Determine if entered value exists in feature layer

[BrianShepard]

The closest solution I've found to what I'm trying to achieve requires a separate JavaScript function (Solved: Stop Survey123 Entry if Duplicate Project ID Enter... - Esri Community). I'm hoping to find a solution with a few questions in the XLS Form.

I have a Survey where I don't want duplicate submissions for the same asset. The values in the field "InstallMeterNumber" should be unique. When a user enters a Meter Number, I'd like to check if that value exists in the field in the feature layer.

My survey has an existing text question to enter the meter number (InstallMeterNumber). I thought I could add an additional question with a calculation using pulldata to query the field in the feature layer and return some value that I could use as a constraint.

Something like:

• text question InstallMeterNumber
• note question InstallMeterNumberCheck with calculation pulldata("@layer", "getValue", "layer URL", "InstallMeterNumber = '${InstallMeterNumber}'")
• Then a constraint on one of the questions if InstallMeterNumberCheck is true or determine the count of the value and a constraint if InstallMeterNumberCheck != 0, or something.

Let me know if there's a solution I didn't find by searching. I haven't had luck going through the pulldata documentation to figure out how to determine if a value exists, what value would be returned by that query, and how to incorporate the appropriate questions in my survey.

[Neal_t_k · Accepted solution]

I think your general approach is the way to go.

With the your InstallMeterNumber question, you will want to use input mask or a regex to standardize the format as those two matching is important.

instead of a note question, you can make that hidden, call it ${MeterExists} for this example

In the pulldata, you might need to use a helper field with your where clause, sometime calcs work inside the function and sometimes they don't.

whcl = concat("InstallMeterNumber =",${InstallMeterNumber})

MeterExists = pulldata("@layer", "getValue", "layer URL", ${whcl})

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

The constraint can go on the first question (InstallMeterNumber) and be: 

. != ${MeterExists} 

Question for your consideration:  Are users submitting through the field app, if so constraints won't trigger until submission, so there is the potential a user fills out the entire form prior to realizing they can't submit.

A work around for this could be something like: put everything after your first question in a relevant/invisible group and only have it show up if ${InstallMeterNumber} != ${MeterExists}. That way users know right away that number is used and they can't  move forward.  To prevent submission with an empty form. Use the "body::esri:visble" column instead of relevant, and make at least one required question.  This will prevent submission.

https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-hidden-relevant-an...

Keep in mind that the pulldata @layer  only works online.

[BrianShepard]

@Neal_t_k, Thanks for your reply! I added the helper question to concatenate the where clause and my test survey worked. That's what I was missing.

I do use regex for data validation and appreciate you suggesting it here.

I'm aware of the limitations and appreciate you bringing them up. These Surveys are completed using the mobile app and I'm in the habit of providing a relevant warning message so users don't go through the trouble of entering data and then can't submit, when they're not connected to the RTK GNSS receiver, or when their photos are taken in the wrong orientation. Surveys are filled out in areas of cell coverage, so pulldata should be available.

Good call on the hidden question type. I'm in the habit of using a read-only note with Esri field type as null for testing and troubleshooting so I can see the values. I then change the appearance to hidden when I publish. That way I can toggle visibility using the appearance without changing the question type. Not sure if that's necessary, just an old habit.

I'll post my implementation once I have a chance to work through it all and clean it up.

[Neal_t_k]

@BrianShepard the note and hidden appearance is the preferred way and what I intended. Just wasn't sure from your description.

[BrianShepard]

I was able to implement this with four questions:

1. Text type question for value input: MeterNumber
2. Read-only Note type question (appearance is hidden, Esri Field Type is null) for concatenating pulldata where clause: MeterNumberWhCl - calculation is concat("MeterNumber = '", ${MeterNumber}, "'")
3. Read-only Note type question (appearance is hidden, Esri Field Type is null) for pulldata value query: MeterNumberCheck - calculation is pulldata("@layer", "getValue", "attributes.MeterNumber", layer URL, ${MeterNumberWhCl})
4. Read-only Note type question to provide a user warning before submitting: MeterNumberExists - relevant is ${MeterNumber} != "" and ${MeterNumber} = ${MeterNumberCheck}

The Warning question was appearing initially, when the MeterNumber value was null, so I added a condition to the relevant statement to prevent that - ${MeterNumber} != "". MeterNumber is required, so a value must be present to submit the survey. I added a constraint to MeterNumber - ${MeterNumber} != ${MeterNumberCheck}. 

With that in place, if a user enters a MeterNumber that already exists in the feature layer, a warning displays directly below that question letting them know that's the case. If they ignore that warning, the constraint will prevent them from submitting it.

[Neal_t_k]

@BrianShepard 

${MeterNumber} != "" or ${MeterNumber} = ${MeterNumberCheck}  

can't be both null and = to  ${MeterNumberCheck}  at the same time.

scratch that, I just re-read what you wrote.