Fix for data format issue when using numbers in the name column of an external select list?

[AnnaMiera]

Hello everyone,

I'm running into an issue with external selects in Connect. (Not an external choice list, I'm using select_one_external rather than select_one_from_file). 

I'm using cascading selects to narrow down a long (~21k) list of plant IDs, where the choice_filter reduces the choice options based on plant species and location (Garden). 

The issue is that the plant IDs are numbers and when they are brought into the survey from the itemsets.csv a decimal and zero are added to the end of every plant ID (6857 becomes 6857.0). This presents the plant IDs in the survey and stores the data in the results with the added .0. Since this information is stored as text in the data, it seems relatively simple to fix on the back end in excel by exporting as a csv or as an .xlsx file and converting back to number en masse, but it means that the plant IDs are displayed and stored incorrectly in the survey itself and in the analyze and data tabs on the website. Is there a way to fix this?

I've tried a few things (like bind::type and bind::esri::fieldType) but nothing has worked so far. I'm new to creating surveys so I'm hoping there's a solution that I haven't found or seen mentioned on the Community boards. 

Thanks in advance!

Survey123 Connect version 3.16.110, Field app version 3.16.114

P.S. The plant IDs in this survey do unfortunately contain duplicates as certain plant ID tags were reused in some locations. I know there are limitations when using choice lists that contain duplicate names, but I'm not sure how they might impact this survey since the cascading select removes any duplicate options when filling out the survey. I'm mostly concerned with the potential for the choice to revert to the first duplicate in the list when opened from the inbox, drafts, sent, etc. Any more detailed info on this would be greatly appreciated. 

[DougBrowning · Accepted solution]

External choices sheet is the old way to do it.  Now you can create a stand alone csv and use select_one_from_file municipalities.csv to load it.  This eliminates excel and I think this will help with your issue.

If not you can also try the new search appearance style vs a choice filter.  (And also use select from file) See https://community.esri.com/t5/arcgis-survey123-blog/dynamic-choice-lists-using-search-appearance/ba-...

Hopefully one of those 2 does it.

[AnnaMiera]

Thank you for getting back to me! I've been looking into your suggestions and I have a few follow-up questions:

What is the municiplaities.csv you are referencing? I thought it might be from the dynamic choice lists blog post or the appearance doc, but that doesn't seem to be the case.

What do you mean by eliminating excel?

How do I use the search() appearance to filter my choices based on both Garden and Species? I've been working with select_one and select_one_from_file and the given syntax: search(‘tableName’, ‘searchType’, ‘searchColumn’, ‘searchText’, ‘filterColumn’, ‘filterText’) but I feel like I'm missing something. Are all optional parameters required in order to use the filter parameters? Can I specify which optional parameters I'd like to use? I've tried variations of both:

search('plantIDs', 'matches', 'garden' and 'species', ${Garden} and ${Species})

and search('plantIDs', 'matches', 'name', ${plantIDalternative}, 'garden' and 'species', ${Garden} and ${Species})

where my new select_one/slect_one_from_file question is named plantIDalternative but I have not gotten anything to function properly (no options will populate the list).

Thanks!

[DougBrowning · Accepted solution]

municiplaities.csv would be yourffilenamehere.csv  You are pointing to a file in the media file and not using Excel and external choices at all.  Delete the external tab totally and try select form file.  (as always try all this on a new test form!)

I would start there.  On the search I am really not sure if you can string multiple together with an and or not.  I would stick with choice filter for now and see.  What you are doing now is adding choice to excel which 123 then converts to itemsets.csv which I think it where they .0 is getting added.

[AnnaMiera]

UPDATE: I was able to get it to populate the list correctly using "select_one_from_file" and "autocomplete search('plantIDs', 'matches', 'garden', ${Garden}, 'species', ${Species})" 

It wasn't working at first because I didn't have both my name and label columns on the choices worksheet set to 'name'. 

Thank you again!

[AnnaMiera]

So sorry, just saw that my update didn't actually post this morning!

It seems you can string together multiple filter parameters with appearance search()!

The list will populate with just one answer selected though, so if it's important for anyone's data to have both selections first it would be worth looking into a relevant, readonly, or other column to restrict input to the search() question until all filter parameters have answers.

[AnnaMiera]

Follow-up #2: Is it possible for this to work on the web app as well as in the field app?

[DougBrowning]

I am not sure as media files it can get weird.  I never do web forms.  Just post and test and let me know as I may need it here soon.

[AnnaMiera]

Bit of a late update for you, Doug. Never did get it to work in the web app, but perhaps that functionality will come in a future update. Best of luck!