Survey123 if then statement with an 'and' and a pulldata

[SSeymour]

I am trying to use an if then statement with an 'and' included and a pulldata. The data I am using is GSA's per diem rates. I have a ‘select one’ for location (county), and a begin and end date in Survey123 form. I have also concatenated the two date columns but not sure this is necessary.

What I am trying to do is pull the lodging and MI&E rates into the form, so once a location is selected (county) it then gives the options for the ‘SEASON BEGIN’ and SEASON END’ columns. In the case of Kootenai, there would be three options, once that is chosen it fills in the Lodging and MI&E fields.

If ‘Kootenai’ and ‘begin’ and ‘end date’ are this, then lodging and MI&E rates are this.

I have seen examples with if then statements and nesting but nothing that adds 'and' or multiple columns the way I need it to. I would really like to keep this as a pulldata and not enter it into the form because it will be updated every year and I would rather update the spreadsheet then the Survey123 form.

Any ideas, suggestions, help would be greatly appreciated. Thanks in advance. 

[IsmaelChivite · Accepted solution]

Are you looking for something like this?

If so, check the attached XLSForm and associated media files.

In short:

• A cascading select (choice filter) to display the seasons according to the county
• A pulldata to retrieve the rates

[SSeymour]

That is exactly what I'm looking for. Thank you so much!

[adamscottorsanco]

Hi Ismael,

I am trying to autopopulate a field via pulldata() similar to this but I am running into issues. 

It's a biological datasheet for fish surveys. I have 'select_one fish' for Fish species, and I was hoping that once you clicked on the appropriate species name that it could autopopulate the 'Species Number' field. Every time I try, I get an error that says:

"There has been a problem trying to replace ${fish} with the XPath to the survey element named 'fish.'" There are multiple survey elements with this name.

I have tried everything I've found online but nothing has worked. I attached my documents below. 

Thanks!

Bridget

[adamscottorsanco]

and here is my CSV

[BobbyHorne]

Hi Adam,

I think there are a few issues with your pulldata statement in the attached xlsx. 

pulldata('OhioRiverFishSpeciescsv', 'Spnum', 'Common_Name', '${fish})

The title of your csv for part one is title only, no .csv suffix should be included.  Also, in part four you have a single quote in front of your $ sign.  Can you try this statement and see if it works for you?

pulldata('OhioRiverFishSpecies','Spnum','Common_Name', ${fish})

Let us know if this works!  The exact formatting needed can be found here: https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#:~:text=selected(%24%...

Thanks,

Bobby

[adamscottorsanco]

Hi Bobby,

When I tried that statement it returns with: 

"There has been a problem trying to replace ${fish} with the XPath to the survey element named 'fish'. There are multiple survey elements with this name. "

[ESRI-BobbyHorne · Accepted solution]

Ah, I see what the issue is! 

Under name column, all names should be unique.  You have fish as the name for around 30 rows.  This is attempting to place "fish" for all field names and in turn.

Your pulldata statement is attempting to pull the value for the field 'fish".  Once you change each to a unique name, you will be able to use your pulldata statement for each group with the corresponding name used to replace subsequent "fish".  

[adamscottorsanco]

Hi Bobby,

I apologize for my lack of understanding here. It seems like in the example posted above ${season} is used in the same way that I am trying to use ${fish}. I am unsure of how I am supposed to replace it.

I made some adjustments to my survey and the CSV file to mimic the exact format from the example, and added the associations (on the choices tab) for it to draw from that I may have been missing before, and it still won't work. Would you please take a look at my updated files?

Thank you!

Bridget