Survey123 Tricks of the Trade: Choice Filters

62019
50
10-16-2020 01:16 PM
IsmaelChivite
Esri Notable Contributor
20 50 62K

The best way to tackle a big problem is to break it down into smaller ones. Lists are no different. Choice filters let you leverage user-entered values in your form to dynamically control which elements of a list are shown or hidden. The most common use for a choice filter is to construct what is known in XLSForm jargon as a cascading select, where a selection in a list (for example countries), controls the contents of a second list (for example, States, or regions within that country).  However, as we will see in this article, choice filters can be used well beyond cascading selects.

This blog introduces choice filters in Survey123 and some common techniques to work with them effectively. If you are already familiar with choice filters and cascading selects, you may still want to read this article as it describes a few new interesting things added in version 3.11 (October 2020).

If you are not familiar with choice filters, XLSForms or Survey123 Connect, you may find this content a bit steep. I suggest you watch this 2 minute video with an introduction to XLSForm select questions to warm up. This is not rocket science, but basic familiarity with Survey123 Connect and XLSForms is assumed.

The basics (Cascading selects)

In XLSForm jargon, choice filters are often associated with the notion of 'cascading selects'. The idea is that a selection in one list, determines which values will be presented in the following list. Say we have a list of countries, and a second list of regions. As you select a country in the first list, you want the second list to just show regions for that country.

Below is what the countries and regions cascading select example could look like:

Survey123 Choice Filters

Building your own cascading select is pretty easy. Here is a brief step by step guide using a hyper-simplified countries and regions example:

  • Open Survey123 Connect and create a new survey using the Advanced template
  • Switch to the choices worksheet and add the countries and regions lists as shown below:

  • Now switch to the survey worksheet and add two select_one questions.

  • Save your spreadsheet and preview your form in Survey123 Connect. All the regions will be shown, which is not good. We need a choice filter for that!
  • Switch back to the choices worksheet and add a new country column to the worksheet. This new column can be called whatever you like, but in our case we will call it country.
  • For each of the rows in the regions list, populate the country column accordingly. The values in this column must match the name values from the countries list.

  • Now that the choices worksheet specifies the country for each of the regions, we can define a choice filter in the survey worksheet.
  • Switch to the survey worksheet and look for the choice_filter column. Apply the following expression to the region question: selected(${country},country)

  • Save your spreadsheet and preview your changes in the Survey123 Connect preview. Now your cascading select should work as expected: As you select a country, the list of regions changes accordingly.

As you can see the concept is simple: The expression in the choice_filter column of a select question determines which elements of the list will be displayed. In the choice_filter expression you can reference questions from your survey and  custom columns from the choices worksheet. In our scenario, the expression was selected(${country},country). The choice_filter expression is evaluated against every choice in the region list.  If the expression evaluates to true, then the choice is kept. If the expression evaluates to false, the choice is dropped.

If you have been using choice filters for a while with Survey123, I bet your choice_filter expression would have looked like this: country=${country} instead of selected(${country},country).  Both expressions are correct. We will talk more about the differences between these two expressions, and when to best use one or the other, later in this blog in the 'Working with very large lists'.

Chaining cascading selects

In the previous example we constructed what is known as a cascading select. You can cascade, or chain, as many selects as you like. For example, you can add a cities list next:

In the choices worksheet, note that I added a new region column for the cities list.

In the survey worksheet, I added a new select_one question for the cities and its corresponding choice_filter.

Choice filters on checklists (select_multiple)

You can apply choice filters to select_one and select_multiple questions. In the example below, note that I converted the regions and cities questions into checklists. Say for example, you visited Spain. Then I want users to select all the regions they visited in Spain, and also all the cities visited within these regions.

To do the above, I simply changed the type of questions from select_one to select_multiple.  Since the selected() function works for all select questions (select_one and select_multiple), you do not need to change the choice_filter.

Support for cascading selects on select_multiple questions was added in version 3.11 (October 2020)

More on choice_filter expressions

Given that the choice_filter column can evaluate XLSForm expressions, you can get creative in the way you apply filters to lists.  The key concept to understand is the following:

A choice_filter expression is evaluated against every choice in the list. If the choice_filter expression evaluates to true, then the record is kept in the list. If it evaluates to false, the record is hidden from the list.

With the above in mind, here are some ideas:

  • Filters on numeric values: If the choice list custom column contains numeric values instead of text, the > (greater than), < (less than), = (equal), etc. comparison operators will be useful. Below is an example using price<=${dollars} as a choice filter.  The range question at the beginning sets a numeric value (the dollar amount), which is compared against the price custom column added in the items choice list.

  • Functions on strings: Other than the selected() function and = operator, which we already described, there are other functions you can use to work with strings. Let's pretend we need some work done by a contractor. We want to use a first list to define the work that needs to be done. The list of contractors needs to be filtered to show only the contractors with the necessary skills.

Unlike the countries and regions example we explored earlier, in this case elements from the filtered list can appear multiple times. For example, Patricia is an electrician, gardener and welder. As shown in the screenshot below I added a skills column to the contractors list. This column includes a comma separated list of the skills of each contractor.

To find if a particular skill is present within the skills column, we can use a regular expression such as regex(skills, ${skill}). Alternatively, we can also use contains(skills, ${skill}).  Other string functions such as starts-with() or ends-with() can be handy as well.

  • Working with custom JavaScript functions: In some cases, to model complex logic, you may want to resort to the use of custom JavaScript functions with the pulldata() function.  The key to use your own custom JavaScript functions is simple: if your function returns true, the choice will be added. Otherwise it will be hidden. This option is powerful but it can, depending on the complexity of your function and the length of the list, add an overhead to your app.

As usual, using the survey samples that come with Connect is often the best. In Survey123 Connect, create a new survey and select the Cascading Select sample. You will find in there a few additional great examples illustrating the different patterns you can follow to create filter expressions.

Working with very large lists

Very long lists can slow down your survey.  The definition of a 'long list' varies. It varies depending on the compute power of your device as well as the size of your survey. Generally speaking, you should not worry too much about performance when working with lists under 500 records, but as you get to that threshold, you may want to consider a few tips to optimize lists in your survey.

There are different aspects that require your consideration when working with long lists:

  • How you display lists: Appearance
  • How you store the lists: External choice lists
  • How you filter lists: Choice Filter expressions

Appearance

Survey123 supports multiple ways to display lists. By default, choices in a list are directly presented in the form. In this way, end users can see right away all the choices and tap on them quickly.  This default appearance, is great for very small lists (under 10 elements). For lists that grow larger, this default appearance is not adequate because it forces users to scroll the entire form in order to see elements at the bottom.  If the list grows even larger, the initial loading time of your form will be negatively affected because the default appearance forces Survey123 to render every choice in the list within the form.

Using the XLSForm minimal appearance , you collapse list choices into a dropdown control.  Everyone wins: end users do not need to scroll up and down the entire form, and Survey123 does not need to render the entire list until the user chooses to open the dropdown.   When working with long lists, the minimal appearance reduces the initial survey loading time.

You can also use the autocomplete the appearance. With autocomplete, the dropdown list will not show any choices until the user starts typing. This also saves time because only elements in the list that match the user input need to be rendered. For very long lists, autocomplete is often the best option for both the end user as well as to speed up your survey behavior.

Ultimately, you will need to choose what appearance works best for you. The performance differences of these different options will vary depending on your device too. As I wrote this article, I measured how much time a list with 1,500 elements took to load using the different appearances. Autocomplete was the winner. It reduced loading time 400% when compared to the default appearance and it was also slightly faster than the minimal appearance.

External choice lists

The most common way to define a list in XLSForms is through the choices worksheet. Using the choices worksheet is convenient because you can easily visualize and change your lists when authoring your XLSForm. All it takes is to switch from the survey to the choices worksheet.  Having said this, it is best practice to keep very long lists as separate CSV files.  External CSV files reduce the initial loading time of your survey and accelerate choice filters on your lists. 

Starting with version 3.11, Survey123 supports variations of select_one and select_multiple question types that allow you to reference lists in external files: select_one_from_file and select_multiple_from_file

The Survey123 Connect XLSForm templates do not include the select_one_from_file and select_multiple_from_file question types in the drop-down, but you can add them manually to your XLSForm.

The XLSForm below shows how these new types could be used. The number of municipalities in the Philippines is roughly 1,500. Already a big list, so we are using a select_one_from_file. Note that the list name referenced in this question is referencing a csv file called municipalities.csv. Municipalities are broken down into barangays. As of 2020, there are more than 40,000 barangays in the Philippines. So again, we use a _from_file select to speed up loading time and filtering.  Note that in this case we choose a select_multiple_from_file and also applied a choice filter.

typenamelabelchoice_filter
select_one_from_file municipalities.csvmunicipalityMunicipalities
select_multiple_from_file barangays.csvbarangaysBarangaysmunicipality=${municipality}

The contents of the csv files are not much different from what is included in the standard XLSForm choices worksheet. The list_name column is not needed, as the name of the list is defined by the csv file name (no spaces allowed in the name). The name and label columns for choices in your list are mandatory and any other column (language columns, media, image, etc) are optional.

The csv file names do not accept spaces and the file must be stored within the media folder of your survey.

I want to emphasize here that using external csv files for your lists is adequate for very long lists (over 500 choices), but it is not going to necessarily add much, other than overhead to you, for smaller lists. Keeping lists in the choices worksheet of your XLSForm is a very valid and convenient approach.  For most of your forms, you will want to keep your list definitions in there.  It is only when you work with monster lists that _from_file selects will help you.

The use of _from_file selects supports both select_one questions (select_one_from_file) and select_multiple (select_multiple_from_file).  You can also apply choice filters, calculations, constraints and any other XLSForm tricks you already.  Other than where the list is stored (external file versus the choices worksheet in your XLSForm), your XLSForm authoring experience should remain the same.

The XLSForm specification supports a variation of external choice lists called external selects. I will not be covering in this blog how to work with external selects, but if you are interested you can have a look at them in the Cascading and external selects—ArcGIS Survey123 | Documentation help topic.  I personally like to use external choice lists instead of external selects.

Choice filter expressions

How you write your choice filter expressions can impact performance as well. The fastest choice filters are those using simple comparison operators. For example:

  • country=${country}
  • dollars>${price}
  • code!=${code}
  • priority>=${priority_reported}

Remember that comparison operators like the above work only against select_one questions. For select_multiple questions you need to go with functions like selected(), regex(), contains() and others, which can't be optimized by Survey123 as efficiently as simple comparison operators.

You should not be afraid of using selected(), regex() and other functions as performance will generally be pretty good, but if you want to squeeze the best of your device, simple operators will help.

Be cautious when using custom JavaScript functions with the pulldata() function in choice filters. Since your JavaScript function needs to be evaluated once for every record in the list, performance can be greatly affected. 

Learning more

As you can see, there is a lot around choice filters in Survey123. If you would like to see more examples of expressions you can apply, check the Cascading Selects sample included in Connect. 

50 Comments
DavidColey
Frequent Contributor

Hi - I've just updated to 3.11.  Unfortunately, the question types

select_one_from_file and select_multiple_from_file 

are not appearing as valid question types when beginning a new survey with the Advanced Template in Survey123 Connect.

Do I need to add these types to the XLSForm 'Question types' worksheet so that these types will appear as valid selections in the 'type' column?

Suggestions?

Thanks-

MichaelBruening
Occasional Contributor

You may need to actually type in the "select_one_from_file InsertFileName.csv" within the "Question Type" column of the XLSX form in order for it to work. I was just using these two features yesterday without any issues. 

IsmaelChivite
Esri Notable Contributor

To add to Michael's response: Michael is correct. The select_one_from_file and select_muliple_from_file question types are not included in the list of questions types in the templates we provided with Survey123 Connect, but you can type them manually.  We decided not to add these question types because their setup is a bit more convoluted than the traditional select questions.  For the vast majority of use cases, the traditional select questions do an excellent job so we thought we would keep the list of questions lean.

DavidColey
Frequent Contributor

Ok thanks to you both.  I had tried that but must have my csv headers wrong.  I am using csv files with pulldata functions, but can either of you tell me:

does(do) the csv file header names for select_from_file need to be 'name,label' as in the xlsForm?

IsmaelChivite
Esri Notable Contributor

Yes. Your CSV files must have the name and label columns. Add them in order and use lowercase, just like you would if working directly in the choices worksheet.

GüntherFüllerer1
New Contributor

I am having trouble with the multiple language support and .csv files:
My default language is German and there is also English. If I switch to English the dropdowns from select_one_from_file do not translate  - the header of my CSV is as follows:

"name","label","label::English (en)"

Is the capital "E" a problem? I stick to the following manual: Manage multiple languages

GüntherFüllerer1
New Contributor

I additionally tried lower case "e" and also replaces the blank between "h" and "(" with an underscore -> label:englisch_(en), unfortunately no success - any ideas?

NickMoore
New Contributor III

Ismael Chivite

We have a survey with a repeat section. The repeat table has hyphens in its naming convention. Similar to PRJ-12345-DD-inspections.

We have a series of cascading selects within this repeat table. one select_one question and another with with a choice filter for example utilising this country=${country} convention. When the table name has hyphens in it, we are experiencing an issue that the choice_filter does not work, it does seem to work if we rename the repeat table. This seems to not be documented anywhere? Is this something you have come across before? can you confirm if this is a bug?

by Anonymous User
Not applicable

Ismael Chivite

When I add the "selected(${country},country)" the regions disappear and when I select the country, no choices are available.

I see a geopoint in the Schema Preview that is not in the type column in the survey worksheet.

Anneka_France
Occasional Contributor III

@IsmaelChivite this is a really useful blog, thanks for sharing. Is it possible to set up a choice filter using a hidden calculation field, rather than a select_one or select_many field?

For example, we have a hidden field (${organisation}) with the calculation:

if(${total_score}>=10, 'organisation1','organisation2')

I then want to apply a filter to a choice list based on the value of this field. I've followed the steps above, but it doesn't seem to work. Am I missing a step? Or is it because it's not the correct field type?

palgura
New Contributor

Hello @IsmaelChivite 

Just wondering... with the improvement on external choices, is select_one_external or select_multiple_external still works with the current version of Survey123? If yes, would you still recommend using it or should we update our forms to use select_one_from_file and select_multiple_from_file question types instead?

Thanks,

Patricia

PaulMillhouser
New Contributor II

Hello @IsmaelChivite and  @MichaelBruening ,

I'm having problems implementing a select_one_from_file in a survey. Despite having manually created a new row on the question type tab of my advanced template, I get this message when trying to create the survey:  

PaulMillhouser_0-1608325699835.png

The entry on my question types tab looks like this:

PaulMillhouser_1-1608325802418.png

Can anyone tell me where I have gone astray? I am using Connect version 3.11.123. Thanks in advance for your help!

Jim-Moore
Esri Regular Contributor

Hi @PaulMillhouser

From your screenshot of the conversion error, it looks like there might be an erroneous space in the question type?

Jim-Moore_0-1608508616282.png

Best,

Jim

PaulMillhouser
New Contributor II

Thanks, @Jim-Moore, but I think that is just an artifact of Excel formatting/screenshot quality. There's no space there. If there had been, I would have felt a little silly, but I also would have been relieved to find such an easy solution!

Jim-Moore
Esri Regular Contributor

Thanks @PaulMillhouser, it was a long shot! If you're able to share your XLSForm that'd be great so I can attempt to troubleshoot. Feel free to send to me by direct message.

MichaelBruening
Occasional Contributor

@PaulMillhousersorry as I am just now seeing this and I am wondering if -

Within your csv file are you using the two following columns "name" and "label" in that order?

Do you happen to have any values within these two columns that exceed 32 characters?

Is the csv file within your media folder?

Which version of the Survey123 Connect are you using to create the survey?

Here is an example that I have for one of the "question type" -

select_one_from_file EDGE_Employee_List.csv

Todd
by
New Contributor III

@MichaelBruening  I noticed a similar issue with records being excluded from a  select_one_from_file choice filter. I have name lengths >32 chars (as I added a state name suffix, 2 chars) but the length is necessary to avoid duplicate names. I could add another filter, but the current .csv causes about a 4 sec delay for each filter control. Curious to hear your thoughts, thanks!

MichaelBruening
Occasional Contributor

@ToddI went back and tested a few choices that were longer than 32 characters and the choices behaved properly within both of the latest BETA versions of the field and connect apps. I tried up to 65 characters and the form worked correctly. So, maybe there was something with the first set of choices that I was using to cause an issue with the apps?

In terms of the slowness, there could be a variety of items that could cause the delay. Have you tried looking at other blogs that report slowness? I noticed that there are many discussing they have thousands or choices and many are using external selects.

PaulMillhouser
New Contributor II

@MichaelBruening Thanks for the helpful thoughts. My problem turned out to be the result of their in being an extraneous space, which I did not initially see due to their being two versions of my Excel file that had gotten out of sync on my network.

by Anonymous User
Not applicable

Hi @IsmaelChivite,

We've noticed that choice lists with choice filters applied no longer create domains in the Hosted Feature Layer. Is this expected behaviour? Is there any way we can keep the domain creation outside of applying the filters in a re-publish?

Cheers, Chris

Todd
by
New Contributor III

@MichaelBruening yes, I believe it's due to the amount of records I'm using. The original post I was using locations (115K) throughout the country. For now, I've limited my selections to only 5 states and the performance is much better. Obviously it's the field app that suffers the delay. Thanks! 

DougBrowning
MVP Esteemed Contributor

I would like to calc the name of the csv but it will not take it.

Like select_one_from_file ${calcfield}

That way I can dynamically pick which file to use.  In this case based on state.

So if they pick CO I want COSpeciesList.csv and UT I want UTSpeciesList.csv.   But it will not take a field name in the type column.

I am guessing I am going to have to use the new search() instead?

Thanks

MichaelBruening
Occasional Contributor

@IsmaelChivite I have a question about last half of this statement - " The name and label columns for choices in your list are mandatory and any other column (language columns, media, image, etc) are optional." I have been testing this functionality out as there will be many uses within the survey forms that I am creating. I want to have a great understanding of the ins and outs of using this within my Surveys. It seems that we can have more than the "name" and "label" columns within our csv file when looking at the second half of your statement (bold blue text). I will also add that I will be using the csv file via the "Linked Content" option for many of the surveys.

So, my question pertains to the order of the columns within the csv file. It appears as though we must have the "name" and "label" columns as "name" = column A and "label" = column B, then column C through X can be whatever additional fields within the csv file we would like to have. Would this be the correct means of setting up the csv file?

I believe it is and this would be useful with the csv file used in conjunction with the "choice_filter" column. @DougBrowning this could be how you get around your question above; just have a linked CSV file that has all of your surveyors within it and then a column named "State" and follow the directions above from the "External Choices Lists". If you have a surveyor in more than one state, then just have 2 entries with their name and then State 1 and State 2. So, far this is working for myself and I hope this is the way it should work.

Thank you,

Mike

DougBrowning
MVP Esteemed Contributor

I have no idea what the names of my crews are.  But that was not my goal.  I wanted the list to flip based on a State field that gets passed from Collector.

I changed to using the new search() way and it is working so going that route.

Just do search(${calcfieldcsvname}) and it works.  You do not even have to give it anything else like the query or the filter.  Seemless now to pick the correct csv list for them.  And its fast in 3.12!  I can load 3 states in less time than one previously.  Looks like now I can make a nationwide project vs 1 per state which is awesome.  (Other speed tests also good - see early adopter board for my post).

I do not think name and label have to be first in the csv.  Mine were not and worked fine.  In fact you do not even have to call them name and label it seems.  Just tell choices what columns anmes to use and it works.

Hope that helps.

MichaelBruening
Occasional Contributor

I see, so you have a data connection where as I do not, so everything has to be local on the device in my instance and this can be a challenging thing at times. I would love to be able to use the search() within my form on other services; huge advantage.

Are you using the "Linked Content" CSV file for anything? When testing the Linked Content CSV file in conjunction with the question type "select_one/multiple_from_file filename.csv" would not function correctly unless the "name" and "label" was the first two columns. There could be no other columns before these two columns. Once I placed the "name" and "label" columns within the first two columns (in that order), then everything worked accordingly.

I hope to hear from the Survey123 team if this is the expected behavior/setup. If so, then I have a workflow that will allow for the crews to submit surveys that contain potential choices, an automated service that will use those submittals to update the "CSV" file within our ArcGIS Online organization. This means they will be able to run at will on any project, anywhere, and I will have minimal interactions with them. This app continues to grow our capabilities.

DougBrowning
MVP Esteemed Contributor

No, no data connection.  I am totally offline.  You can also use search with CSV files in the media dir.  search(csvfilename)

DougBrowning
MVP Esteemed Contributor

Note the new search() allows me to flip lists now on the fly.  Super cool and allowed me to go from 6 state projects to just 1 with no speed hit

autocomplete search(${SpeciesListName}, "matches", "Plants", "Y")

DanaLRobinson
New Contributor III
DanaLRobinson_0-1617400113198.png

 Ismael Chivite

When I add the "selected(${country},country)" the regions disappear and when I select the country, no choices are available.

I see a geopoint in the Schema Preview that is not in the type column in the survey worksheet.

 

Self resolved on Country example, still struggling with my own fields

z_cameron
New Contributor II

Hi,

Thanks for the great post!

Is it possible to do other way around with choice-filter? For example when Paris is selected in City field, Country field is populated with France?

Thank you

JulioFerneyAriasRodriguez
New Contributor

Hola

Gracias por tu aporte. Estaba buscando como filtrar listados que incluyen multiples unidades de negocio sin tener que generar columnas nuevas.

Gracias.

SSMGroup
Occasional Contributor

It appears I can't use this functionality when also using the "or_other" option. Is that correct, or am I doing some thing wrong? I continued to get errors trying to use the choice filter, until I removed "or_other" from the question type.

@IsmaelChivite 

MaryWasson1
New Contributor II

Hi,

Is it possible to use a choice filter and/or a cascading select with ranking? I would like for the respondents to the survey to pick their top 5, then only those top 5 show up in the ranking list. I do not know how to do that or if it is even possible. 

Thank you!

@IsmaelChivite 

ClaudiaGIS
New Contributor III

Hi, not sure if this is the right place to post this question. I have a survey that includes several choice filters. I need to generate a list of the choices that haven't been selected. I am visualizing the results of the survey in a dashboard so I would like to also be able to see the list of choices not selected. Is this possible? 

Thank you.

CandiceBardsley1
New Contributor III

@IsmaelChivite @MichaelBruening 

Hey there - after some help regarding choice filters. I've set a simple one up in my survey 123 connect xlxs and it is working perfectly in the preview and also in the survey123 phone app once I've published. Brilliant. The issue I have is that I want the survey to be used within an experience builder on a laptop - the only way to embed that survey is to grab the link from Survey123 manager (via our portal). Again, I can embed the survey fine but in this setting the choice filter no longer works. I pick the initial geothermal field I want and instead of the wells for that field getting filtered by the choice filter for the next selection, the entire field is just blank. 

Is this a known bug or issue with choice filters?

Thanks in advance!

MichaelBruening
Occasional Contributor

@CandiceBardsley1 there should be no limitation on using the choice filters within the webform or at least I believe this to be the case. I do not use the webforms very much, but I completed a few web searches on the topic and most of the post lead me to believe this is possible.

Within this post there was an issue with the version of Survey123 Connect that was being used to publish the Survey123 webform. I just wonder if this could be the potential hiccup. 

https://community.esri.com/t5/arcgis-survey123-questions/choice-filter-not-working-in-web-form/td-p/...

 

MichaelBruening
Occasional Contributor

@CandiceBardsley1 I guess I should have added that the "cascading selects" is not currently supported within the webform.

CandiceBardsley1
New Contributor III

@MichaelBruening thanks for the responses. The issue I have is identical to the question you linked in the above post - and it appears that worked. I might follow up on that post to see what Richard has to say. Thanks~!

MaholyRavaloharimanitra1
New Contributor II

Hello,

 

Thank you very much for this tutorial. Very useful and helpful. However, I have one question because I have hard time trying one choice filter to work in my survey. Strangely, all the other choice filter worked, except this one. This is the situation:

I have rangers that are doing monitoring regularly, they come as a team of two to four.

They come from different associations (VOI) (cf. screenshot called "choices")

What I want to do is to only show the name of rangers related to the VOI when one VOI is selected. (cf. screenshot called "survey")

Unfortunately, I have tried all the possible formula but it didn't work (as you can see on the "preview") and you can see one of my try on the screenshot called "survey". I have tried clb=${association}, selcted(${association}, clb), selcted(${association}, "clb"), selcted(${association}, 'clb'). I am running out of idea so I would be glad to have your help

 

Thank youchoiceschoicespreviewpreviewsurveysurvey

DougBrowning
MVP Esteemed Contributor

I guess I am confused that the VOI name column and the clb column do not match at all?  

MaholyRavaloharimanitra1
New Contributor II

The VOI name and the clb column are matching. It was just that it was not possible to show all the line in one screenshot 😊

DougBrowning
MVP Esteemed Contributor

Hmm looks like have dups in your list also?  Special chars can get you a lot.  Can you post the form.

MaholyRavaloharimanitra1
New Contributor II

Do you mean the excel file? 😊

leahmaps
Occasional Contributor II

Is there anyway to do a similar functionality in web builder?

VirginieLe_Gal
New Contributor III

Hi @IsmaelChivite,
Thanks for the wonderful article !


I have a question. In my survey, I have an external file (.csv) with some choices for a question.
example : list of police offenses (with name and label)
But I want to filter this list with keywords.
I know that I can do this in XLSform in "choices" sheet with a third colomn: in this colomn I separated the keywords with commas.
But my list is very long. So I use an external file (.csv) with a third colomn containing my filtered keywords.
I have several keywords for this colomn. What is the syntax to use in this field ? I can't do the same thing as in the "choices" sheet where I use the comma because it is the delimiter in the external csv.
Thanks for your help

Virginie

DougBrowning
MVP Esteemed Contributor

Try the newer search() appearance and it may work better for you.

https://community.esri.com/t5/arcgis-survey123-blog/dynamic-choice-lists-using-search-appearance/ba-... 

VirginieLe_Gal
New Contributor III

I'm sorry @DougBrowning  but I don't understand how the search() appearance can help me.

 

RobertAnderson3
MVP Regular Contributor

@VirginieLe_Gal 

search() appearance can be used to query hosted items in ArcGIS Online and can use it to filter based on a field as well. Generally, the syntax for it is: search(‘tableName’, ‘searchType’, ‘searchColumn’, ‘searchText’, ‘filterColumn’, ‘filterText’) 

You can also use the choice_filter column to point to the third field in the CSV. For example I have a regex expression in my choice_filter column for a select_one that references a hosted CSV (that I created in Excel, which means I do just use commas in the column that my filter is in). But if you're using a regex the delimiter in your choice_filter column won't matter.

regex(WorkingGroup,"REGIONAL") or regex(WorkingGroup,"OTHER")

WorkingGroup is the header of the column I am using as a choice_filter, and "REGIONAL" or "OTHER" are the keywords to filter by. You could also replace the keyword with a reference to a previous question the user has answered.

VirginieLe_Gal
New Contributor III

@RobertAnderson3thanks for the explanation.

In my case I'm using a reference to a previous question and "theme" is the column header. In Choice_filter I put this: regex(theme,${theme_inf}) However, if I have multiple keywords in my Excel field theme, this doesn't work. I can give you an example.

.csv, offenses list:

name,label,theme
50,50_Refus,nodata;DFCI;DOM
98,98_Abandon_dechet,nodata;DFCI
213,213_conduite_excessive,nodata;DOM
323,323_Chasse,nodata
 
Questions in survey :
In my xlsFORM, I have a first question where people choose the theme (DFCI or DOM or nodata). Then a second question to select the offense. the proposed list is that of .csv. If the DFCI theme was chosen, I would like only offenses related to the DFCI theme to appear.
My problem is : this is not the case. However, I tried to separate the keywords with commas or semicolons in the csv colomn.

 

DougBrowning
MVP Esteemed Contributor

Typically for this case you need a list item for each category so it has an exact match instead of a contains.

So more like this.  

name,label,theme
50,50_Refus,nodata
50,50_Refus,DFCI
50,50_Refus,DOM
98,98_Abandon_dechet,nodata
98,98_Abandon_dechetDFCI
213,213_conduite_excessive,nodata
213,213_conduite_excessive,DOM
323,323_Chasse,nodata

Then you can use  simple choice filter that matches that last column with the theme.

VirginieLe_Gal
New Contributor III

Yes, I was thinking about that but was hoping there was another solution. Thank you very much for your answer. I will be able to move forward on another subject.