How do i pulldata twice, with the first pulldata functioning as a filter?

[Pieter_GlasgowGIS_Public]

We are upgrading on of our survey123 forms in Survey123Connect where operatives select a site, and then select a piece of equipment at that site, both from the same hosted feature layer. Then we want to pull through some data from that piece of equipment in the background (so hidden in the survey) that we need for a dashboard somewhere else. We have it working that you can only select equipment where it has the site name equal the chosen site name. When we set it up to pull through the manufacturer details of that piece of equipment it fails. We currently have it set up as follows:

type	name	appearance	calculation
select_one SITE_NAME	Select_Play_Area	autocomplete search("NAME?url=https://utility.arcgis.com/...")
text	Play_Area	hidden	${Select_Play_Area}
select_one SITE_EQUIPMENT	Select_Equipment	autocomplete search("EQUIPMENT_SEARCH?url=https://utility.arcgis.com/...",'matches','NAME',${Play_Area})
text	Equipment_Query	hidden	${Select_Equipment}
text	Manufacturer	hidden	pulldata("@layer", "getValue","attributes.MANUFACTURER","https://utility.arcgis.com/...",concat("NAME = '", ${Play_Area},"'") and concat("EQUIPMENT_SEARCH = '",${Equipment},"'"))

We noticed that the equipment autocomplete searches and pulldatas does not reliably work when referring straight to the Select_Play_Area the workaround is the Play_Area and Equipment_Query text rows.

We have a list called SITE_NAME and SITE_EQUIPMENT where the name and label are NAME and ITEM which are the field names we are looking for in the hosted feature layer.

We also pull some data through from just the site like the depot which does work. All rows with the same site name will have the same depot. For reference we do that like this:

text

Depot_Name

hidden

pulldata("@layer", "getValue","attributes.DEPOT","https://utility.arcgis.com/...",concat("NAME = '", ${Play_Area},"'"))

But with the manufacturer each piece of equipment at the site might have a difference manufacturer. So if we do not have a way to also specify the site name it will just just look at the top result of all equipment with the same name and return the manufacturer for that. And, unlike the depot where it is the same for all rows with the same site, the name of a piece of equipment can refer to equipment made by multiple different manufacturers.

So we would want:
Site A --> Equipment 1 --> manufacturer X
Site A --> Equipment 2 --> Manufacturer Z
Site B --> Equipment 2 --> Manufacturer X
Site B --> Equipment 1 --> Manufacturer Y

We are hoping it's just syntax but cannot figure it out. Co-pilot has not been useful either.

[Neal_t_k]

Couple things to try, 

First off pulldata doesn't always like the calculation in the where clause, try pulling that out into a helper field.

whcl = concat("NAME = '", ${Play_Area},...

pulldata("@layer", "getValue","attributes.MANUFACTURER","https://utility.arcgis.com/...",${whcl})

Second you might be getting some syntax errors with the where clause.  Unhide it and if you are it should show the error  text in the answer. I have a feeling it maybe that "and concat(", you might try including the "and" in one concat instead of two, something like:

concat("NAME = '", ${Play_Area},"' and EQUIPMENT_SEARCH = '",${Equipment},"'").

If that doesn't work another option is putting the where clause in the url using request parameters.  If you have to go this route, it is likely you will have to also use some helper fields to get it to format correctly.

Formulas—ArcGIS Survey123 | Documentation

Last thing is sometimes there are issues when the pulldata triggers and retriggers.  You may want to experiment with calculation modes.  And also you could make Manufacturer question relevant on answers present in the other questions, the idea is to delay when the calculation is triggered, with the relevant prerequisites being present prior to the calculation.

Hope that helps.

[Pieter_GlasgowGIS_Public]

Thanks so much. I managed to figure it out and it was mostly just a syntax issue, but also I did not realise that the part before the = was not referring to the earlier query name but to the attribute in the feature layer i am trying to pull from. It just happened that the query for the site name was NAME and the attribute in the feature layer was that as well, but the equipment in the feature layer was under ITEM

It works with this construction:

pulldata("@layer","getValue","attributes.MANUFACTURER","https://utility.arcgis.com/...",concat("NAME = '",${Play_Area},"' AND ITEM = '",${Equipment_Query},"'"))