topic Survey123 Connect - Cascading Sheets in ArcGIS Survey123 Questions

Survey123 Connect - Cascading Sheets

John_RMellor__GISP — Mon, 23 Dec 2019 21:15:15 GMT

I'm working in version 3.7.60;

I have the cascade working as expected, but I want to shortcut the middle select question. In my attachments each Inspector has a single County, only one, so there is no need to select. Each County has more than one city. My desire is that the Inspector is picked up from the metadata (the AGOL login email address) and translate that to the choices of Cities. It is working by making the County a "select_one" choice and picking a County. But since there is only one county per Inspector is there a way to default County based on the AGOL login email address, in turn giving me only the applicable Cities to the logged in Inspector?

I can set it to skip the County and associate the Inspector directly to the City, but then I will have to manually repeat the City list associated to the new Inspector. As I grow, this will be difficult to manually maintain. By keeping the middle question, I will only have to connect the Inspector to a County, then their City will auto associate. Much easier to maintain.

So, my goal is to use the AGOL authentication to tell me what County to associate (and I like this to be hidden) and then show the list of Cities that only relates to the person signed in to AGOL.

I welcome any help, comments, suggestions.

Re: Survey123 Connect - Cascading Sheets

Jim-Moore — Tue, 24 Dec 2019 00:00:26 GMT

Hi John

Here are a couple of ways to do this (there are probably several more!).

Write a nested if statement calculation for the county question (if email equals this, county equals that, etc.). This could be in a hidden/text question (wouldn't need to be a select_one). If you haven't got many email addresses, this would be fairly straightforward, but might get a bit cumbersome if there's a long list of emails/counties.
Put each email address and its county in a CSV file and use pulldata() to lookup the county from the CSV. For more info on using pulldata with CSV please see: https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-data and Formulas—Survey123 for ArcGIS | Documentation. Again, the pulldata calculation could be in a hidden/text question.

Then your city choices can be filtered as normal based on the county. Hope this helps!

Cheers,

Jim

Re: Survey123 Connect - Cascading Sheets

John_RMellor__GISP — Tue, 24 Dec 2019 17:22:55 GMT

Jim, thanks for the response. Option 1, as you say, will be cumbersome because of the long list of Cities, this option would be difficult to maintain.

Option 2, looks viable, but I need a way to say pick the list of cities, related to a county, related to the email address of the person signed in.

If I skip to just say between the city and the person signed in (bypass the county) then that too becomes cumbersome and difficult to maintain. If I include the county, then that middle county section is the only place I have to update when a new inspector comes on board.

How would I write a "pulldata" formula that says;

"select one of a set of cities that are in a county where the county is assigned to the person signed in"

Re: Survey123 Connect - Cascading Sheets

John_RMellor__GISP — Thu, 26 Dec 2019 13:23:59 GMT

I seem to have discovered that the issue was all records with the field name you wish to use must have non empty cells.

I have resolved my issue.