Create a constraint based on values in another feature class field?

[mklatt_cityofventura_ca_gov]

I would like to set up a constraint for a field that validates whether the value entered exists in a specific field within a separate ArcGIS Online feature class.

I am using the latest Survey123 Connect in ArcGIS online. The end users will be using a web form to fill this out.

Use case:
We require users to enter their license number as a mandatory field. We intentionally do not want to use a dropdown menu to avoid the risk of users selecting the wrong license or using someone else's license if they don’t have one.

Instead, once the user manually enters their license number, we want to validate that input against a field in a AGOL feature class to ensure it exists in the dataset.

This validation dataset is accessible to all users who will be filling out the survey. 

let me know if anyone has done something like this or if this is possible.

Thanks!

[Neal_t_k]

pulldata("@layer")  I think will get you what you are after.

https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-pulldata-quot-laye...

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_BB97985...

setup a calculate field to pull their license number from the layer based on the license number in the hosted feature.  Then in your license number question in the constraints field build your constraint to make sure they match.   I haven't tried pulling the same number I am matching however.  Are there any other fields you can match in the hosted layer (customer id) or something incase retrieving the license number based on match that licensing number doesn't work?

Edit: Just checked and pulling the license number should working on matching the license number in the same field.  

here is some code to get you started:

Lic_Number_Question constraint: .=${Hidden_Calculate_question}

WhCl calculate: concat("Lic_Num = ",${Lic_Number_Question})

Hidden_Calculate_question: calculate: pulldata("@layer", "getValue", "attributes.Lic_Num", "<url>", ${WhCl})

[mklatt_cityofventura_ca_gov]

Thank you so much.  

My pull data request is not working.  I am worndering if this should work in survey123 connect before publishing.

This is the WhereContr calculation:
concat("CONTRACTNO='",${ApplicantContractorsLic},"'")

The where concatenation returns this CONTRACTNO='123456'
CONTRACTNO is a string field so I put it in quotes

I put this in the calculation field of another text field to see if it would return the CONTRACTNO:
pulldata("@layer","getValue", "attributes.CONTRACTNO","https://services.arcgis.com/xyzxyzxy/arcgis/rest/services/EXAMPLELicenses/FeatureServer/0", ${WhereContr})

I get values when directly putting this in the browser similar to this:
https://services.arcgis.com/xyzxyzxy/arcgis/rest/services/EXAMPLELicenses/FeatureServer/0/query?wher...

[Neal_t_k]

Try it with just this: concat("CONTRACTNO=",${ApplicantContractorsLic})

[mklatt_cityofventura_ca_gov]

Unfortunately this didnt work either.

[Neal_t_k · Accepted solution]

hmmm,   So I  recreated this with sample data and the workflow does work. And it appears from your test that your feature layer is queryable, so I am guessing that it is just a syntax error. 

From here I would work backward, and start with hard coding your where clause ensuring you get something that works and once you have it working with a static url, move that syntax for the where clause to the helper question with the dynamic field as above. 

pulldata("@layer","getValue", "attributes.CONTRACTNO","https://services.arcgis.com/xyzxyzxy/arcgis/rest/services/EXAMPLELicenses/FeatureServer/0", "CONTRACTNO=xyzxyzx")   

or 'CONTRACTNO="xyzxyzx"'  trying various syntax until you get it to return the correct license number. 

[mklatt_cityofventura_ca_gov]

Got it to work using the above format.  I used single quotes around the text in the where clause
concat("CONTRACTNO='",${ApplicantContractorsLic}, "'")

Thank you for the help!

[Neal_t_k]

Glad it is working,  interesting that it didn't work previously, it appears to be the same as in your previous post.  Sometimes getting the syntax correct is the hardest part.

[mklatt_cityofventura_ca_gov]

Yeah, likely was the syntax somewhere else, I retyped everything out.  Initially, it might have been some of the quotes in other spots.  I lazily used word to type this out originally. The Word document quotes sometimes cause issues.

[MattEdrich]

Never done something exactly like this, but I reckon that if you have the validation dataset available as a feature service (don't know if this is the same as a feature class, I'm still kinda naive...) within your AGOL portal, what you are seeking to do is 100% possible.

I think you could do this with pulldata("@json") - there may be other ways as well but here's how I would do this:

1. Get the user's license number as text/string input
2. You may have to pre-process the input to match your validation layer's values (e.g. strip out dashes/spaces, convert to uppercase, etc)
3. Use pulldata("@json") to query your validation layer with the user's license input
   Something like pulldata("@json", "$", "https://services<#>.arcgis.com/<YOUR ORG ID>/arcgis/rest/services/<YOUR VALIDATION LAYER>/FeatureServer/<#>/query?f=json&where=...."
4. Depending on how the query actions happen, you might be able to return a yes/no or T/F or 1/0 value directly, which could then be used to trigger a user-friendly confirmation or error message about the validity of the license number they entered.

It might be easier to do this using a small .JS script to handle the query to the feature layer ( pulldata("@javascript") in that case) and return a JSON object that you can then use pulldata("@json") on for the validation.

You have a few ways to get there - feel free to DM me if you need a bit of extra help with this and I'll see if I can assist!