PullData not working

[ModernElectric]

Working on revising an existing Survey123 form to uses Pulldata to auto-fill a field based on the user's selection on a previous field. However, on this modification, it is not working and not sure what I am not seeing.

The goal is for the user to start typing the Inventory Description and find what they are looking for, and once selected the Inventory Number and UOM is auto-filled. 

My previous survey, the user would select the Inventory Number and the Description and UOM would auto-fill from the Inventory Number. However, it has been requested to switch it around.

Appreciate any help.

[ModernElectric]

@ZacharySutherby @Jim-Moore 

Wondering if you have any advice on this for me?

Thank You

[Jim-Moore]

Hi @ModernElectric 

I'm guessing the quotes (") in the DESCRIPTION field values are causing an issue with the WHERE clause.

Try enclosing the value for the WHERE clause in single quotes, e.g. 

concat("DESCRIPTION='", ${Description_1}, "'")

Hope this helps, Jim

cc: @ZacharySutherby 

[ModernElectric]

@Jim-Moore @ZacharySutherby 

Thank you for the response and the advice. However, that did not solve the the issue. I've attached updated screenshots and the excel file to see if you can help and determine what part of the coding is incorrect.  

In my previous survey I built, I was able to use the Autocomplete search for the Inventory Number and have the item description and UOM auto-populate. Have been trying to reverse that by the user doing the autocomplete search on the Inventory Description since we have 100s of inventory numbers and easier to find a match on the description compared to trying to remember a specific number.

Appreciate any additional help and guidance you can provide.

[Jim-Moore]

@ModernElectric the syntax in your XLSForm looks good to me. It's a bit tricky to diagnose without the feature layer, but everything seems to be set up correctly.

Is the autocomplete search() working for the inventory descriptions?

If you drop the WHERE clause from the pulldata("@layer") expressions, do you get a result?

[ModernElectric]

@Jim-Moore 

Without the WHERE clause, the field doesn't know what to populate into the Inventory Number field based on the original search getting a match from the feature table. Long story short, nothing populates.

Now, I can do the Inventory Number (1025) and the Description will autopopulate based on a match from the feature table. The goal is to reverse that action and have the foreman start typing in the description, find the exact one they want, and the inventory description auto populates from there. Much each for the foreman to start typing in cedar pole to find what they want versus memorizing 1000s of inventory numbers. 

I thought just reversing the code in the spreadsheet would do the trick, but apparently not. 

Any other suggestions?

[Jim-Moore]

Hi @ModernElectric 

Sorry for any confusion. If there's no WHERE clause in the expression, the pulldata("@layer") query defaults to a WHERE of "1=1" (i.e. return all records without any filtering) and will return the first record from the query results (see the Query a feature layer documentation). So removing the WHERE clause should at least confirm if Survey123 can successfully query the feature layer and get a result. 

Perhaps give that a try first and if you get no result, check: that the URL is correct; that the feature layer is shared appropriately; and (in the case of a getValue request) that the attribute you're trying to retrieve matches the field name in the feature layer.

Once you confirm that the feature layer can be queried successfully, you can work out why there's no match happening with the WHERE clause.