topic Re: Repeat Duplicate Preventer in ArcGIS Survey123 Questions

Repeat Duplicate Preventer

DustyJordan — Tue, 22 Mar 2022 06:36:15 GMT

First, a little background. I have a workflow that involves going out and servicing a series of assets on a weekly basis. Each asset has a unique barcode/ID that the user scans or manually enters into Survey123, along with a series of other questions. Upon reviewing the data for reporting I noticed a hair pulling amount of errors, one of which was people entering data for a particular asset more than once. Apparently my techs don't like scanning barcodes and prefer to just enter things manually.

At that point I decided I would just design a better form that made it nearly impossible for my users to submit erroneous records.

Enter the duplicate checker. I admittedly do not have the skills to come up with something like this from scratch, so naturally I pilfered it from another Survey123 user after some GoogleFu. From my understanding, I'm basically using a join in the calculation field to populate a list of barcodes/IDs. Then, I have an expression in the constraint field to check and make sure each additional repeat (i.e. Barcode/ID) does not already exist in the join list.

Here is the constraint expression:

not(contains(substr(${Tubes_Serviced}, 0, string-length(${Tubes_Serviced}) - 3), ${Barcode}))

The issue I'm experiencing now is that if the user enters a duplicate and somehow navigates backwards in the repeat section before immediately deleting it, they get locked in place. The duplicate checker kicks in triggers off the currently displayed repeat, which prevents them from navigating forward, unless they delete the current record.

I'm wondering if there is a way to modify this expression to prevent this lockout that happens when navigating to/from duplicate repeat records?

Thanks for reading!

-Dusty

P.S. I've attached my xlsx form if it helps getting the whole picture.

P.S.S. I've also learned that it seems no matter what you do, errors will find a way.

Re: Repeat Duplicate Preventer

Anonymous User — Tue, 22 Mar 2022 09:04:36 GMT

Hi @DustyJordan,

What you are seeing sounds as though it would be expected, as when you navigate backwards you are now re-evaluating those values in the previous repeat, meaning the answers in them may now not be valid as the string contains the value you are trying to stop being entered.

With the 3.14 release we introduced calculationModes, have you tried this new parameter out:

https://doc.arcgis.com/en/survey123/desktop/create-surveys/prepopulateanswers.htm#ESRI_SECTION1_FC295EA0A9354DCFA970B1D0B30D4DF4

https://www.esri.com/arcgis-blog/products/survey123/announcements/whats-new-in-arcgis-survey123-february-2022/

By using one of these modes and combing with the position() function, you should be able to count the repeat record you are on, and the position, compare them, and work out of the calculation should be run again. May need some trial and error, but should be possible to get it working the way you want.

Regards,

Phil.

Re: Repeat Duplicate Preventer

DougBrowning — Tue, 22 Mar 2022 14:31:32 GMT

It looks like you are using my trick. It broke on me also. Took about 4 hours but I got it fixed using once() around the join. Join seems to be a bit funky now and I am working on it with Phil here https://community.esri.com/t5/arcgis-survey123-questions/once-join-work-differently-on-the-first-repeat/m-p/1155993#M41440

I tried to change your form but then I would have to use a barcode. Hopefully you can follow this. The pain was 2 in a row was not caught at first. Test 2 in a row but also a later dup.

On line 56 add a once - once(join(",",${Barcode}))

Under (must be under) the line with the join add 2 temp fields. One a calc of 1 and one that counts this field. Like this

Then change your constraint to Species list = barcode and AllPlants = TubesServiced

if(${TempCount}=0, 1,${SpeciesList}='' or not(contains(${AllPlantsCheck}+",",${SpeciesList}+",")))

Note not sure if you need the commas part but we had issues where ABCD was found as a dup of ABCD3.

I hope that makes sense it was not easy.

Your other option is javascript. Look for my post with the code. Hope this helps.

Re: Repeat Duplicate Preventer

DustyJordan — Wed, 23 Mar 2022 01:00:37 GMT

Hi @Anonymous User and @DougBrowning,

Thanks for responding!

Doug- I have no doubt you're the one I snagged this method from. Your posts/comments have come up in a lot of my searches for Survey123 solutions over the past few years, and they have been incredibly helpful.

I was wondering if some kind of update had broken this constraint expression, as I recall doing a lot of testing when I implemented it and don't recall having this issue. I'm going to try your fix and hopefully I can get it working again. I'll let you know if I run into any problems.

Phil- Thanks for linking that information on calculationModes! I'm going to try Doug's fix of my existing constraint expression first, but if I can't get that to work I'll take a look into calculationModes. I'm always interested in learning a new trick/function in Survey123.

Re: Repeat Duplicate Preventer

DougBrowning — Wed, 23 Mar 2022 14:14:08 GMT

Yes 3.14 broke my trick. It was the 2 in row where it really got tripped up. The fix listed is working for me but it took a lot of hours to get it. I did try all the calc modes and it did not work. I may not have tried every combination but a lot of them - took close to 4 hours to get it! Hope it works for you.

Re: Repeat Duplicate Preventer

DustyJordan — Mon, 28 Mar 2022 03:46:54 GMT

Hey Doug,

I just got done implementing your fix just as you instructed, and it worked like a charm. I've done some testing and it triggers off consecutive and non-consecutive duplicates, and allows paging forward/backward through repeats when there are no duplicates detected.

Thank you very much for sharing this fix!

I said it before, but I'll say it again, you (and this forum in general) have been a great help to me over the past few years as I've learned and continued to build upon my Survey123/XLS skills. The first forms I created are a far cry from the most recent ones I've built.

So thank you for that, it has been greatly appreciated.

-Dusty

Re: Repeat Duplicate Preventer

NatalieSlayden — Wed, 26 Oct 2022 14:05:33 GMT

@DougBrowning Would you mind taking a look at my form? When I select my first grid structure in my repeat, It is notifying that I have already selected it. I know you mentioned you had this problem as well but I am struggling to solve it. The main xls form is the CopyCoral Nursery Monitoring Form. Thanks in advance. You have been solving so many of my issues recently!

Re: Repeat Duplicate Preventer

DougBrowning — Tue, 25 Oct 2022 20:32:23 GMT

All I had to do was change the calc for line 61. It should be a count not a join. once(count(${TempCheck}))

Try that it works for me. Again this is weird due to the new 123 treating the first repeat different than the rest. I reported this but they do not think it is a bug. Sure seems like one to me.

Re: Repeat Duplicate Preventer

NatalieSlayden — Wed, 26 Oct 2022 14:05:06 GMT

Thanks @DougBrowning That solved my issue perfectly! Really appreciate it!

Re: Repeat Duplicate Preventer

NatalieSlayden — Wed, 02 Nov 2022 19:05:10 GMT

@DougBrowning So that did work but upon testing I noticed in my feature service that my repeat is now creating a 1st blank entry before they add the first repeat So it will be blank entry (see image), then Grid B1 (1st repeat), B2 (+ repeat), B3 & send. Any idea what could be goin on here? Thanks again!

Re: Repeat Duplicate Preventer

DougBrowning — Wed, 02 Nov 2022 19:58:04 GMT

I have seen it creating blank records at times. Mostly it was using hidden and I think the appearance of minimal or some combo. Its been so long I am forgetting what we tracked it down to. We never did get rid of them though. I do not think it is the dups code I think it is something else. Mess with minimal so that you see a + sign vs it auto loading the first record and see if that does it. If you are using the esri visible try a change to relevant.

Hope that works

Re: Repeat Duplicate Preventer

NatalieSlayden — Tue, 08 Nov 2022 17:11:33 GMT

I am going to give some things a try. I think you are totally right tho, not necessarily the dup code. Looks like a bug that has been around for awhile after further research. Thanks as always! Your responses are always so helpful!

Re: Repeat Duplicate Preventer

NatalieSlayden — Mon, 21 Nov 2022 20:15:00 GMT

Hi @DougBrowning, Did you ever have issues with someone entering ABCD3 first and then when they went to add ABCD it flagged as a duplicate? The commas are necessary like you said because if they enter it in the correct order of ABCD and then ABCD3, then it does not flag a duplicate but if it is the other way around, it flags. Just wanted to see if you had this occur with you. I have been playing with the code, but have not found a solution yet

Re: Repeat Duplicate Preventer

DougBrowning — Mon, 21 Nov 2022 21:06:12 GMT

You get around this by adding a comma on the search string as well. Maybe the latest one I did not post. The latest version did change it a bit.

Do you have it like this not(contains(${AllPlantsCheck}+",",${Species}+",")))

Re: Repeat Duplicate Preventer

NatalieSlayden — Tue, 22 Nov 2022 13:54:24 GMT

Yes, That is exactly what I have. It works great unless they enter them in out of order. It is throwing me for a loop.

if(${TempCount}=0, 1,${structurecode}='' or not(contains(${AllGridCheck}+",",${structurecode}+",")))

Re: Repeat Duplicate Preventer

DougBrowning — Tue, 22 Nov 2022 15:02:46 GMT

I retested mine and it works before and after. Maybe a different 123 version or something else is up. If you cannot post the form you can dm me it instead.

Re: Repeat Duplicate Preventer

NatalieSlayden — Tue, 22 Nov 2022 17:35:11 GMT

I DMed you! Thanks so much as always!

Re: Repeat Duplicate Preventer

EdmundEkanem1 — Thu, 01 Dec 2022 12:41:01 GMT

first time here and I apologise if this has already been treated somewhere else. My ask is, how do I apply this to non-repeats? just a list with options on my form where I do not want an option to be picked more than once? thank you

Re: Repeat Duplicate Preventer

DougBrowning — Thu, 01 Dec 2022 14:45:40 GMT

I am not sure what you mean. My guess is you have a set of select one questions that all use the same list?

Brute force way would be to check at each question in the constraint

Q1 -> . != Q2 and . != Q3 and . != Q4

Q2 -> . != Q1 and . != Q3 and . != Q4

Q3 -> . != Q1 and . != Q2 and . != Q4

Q4 -> . != Q1 and . != Q2 and . != Q3

You could also try to making a list of all the answers and checking that. But my fear is that it will add the current question value as you pick it and then the check fails on itself.

QList -> Q1 + " " + Q2 + " " + Q3 + " " + Q4

Q1 -> not(contains(QList, Q1))

Hope that helps