Incrementing Site_id

[DanHaynes123456]

Hi,

Creating a survey123 for to replace a legacy .mdb system.

I need to be able to create a site_id that consists of the project_code, users initials and an incrementing 4 digit integer so for example STAT + DJH + 0001 next site would be STATDJH0002. I need to be able to do this on and offline as crews are often working in remote locations.

I also need to create sample_id for sites where if multiple samples are collected the sample_id increments by 0.1 so for the above example first sample would be STATDJH0001.1 then STATDJH0001.2 etc.

As the site_id is tied to the users initials I am not worried about duplicate records, I am thinking if I create a hosted feature layer in our enterprise portal and populate it with all the possible combinations of site_id for each user (and have our SQL server update it via FME/rest if new users/projects start) then I can maybe somehow write an expression in survey 123 to mark when an id is used and grab the next lowest null valued id for the new site?

Its not pretty and not sure it will or can work, help me please!

My other issue is creating repeats for another form that is based on depth to and from. I need to be able to have a new repeat auto fill the from field using the previous to field (with the first starting at 0m) so first interval might be from 0m to 15m then from 15m to 17m from 17m to 28m etc. I have not found a way to do this either...

[MobiusSnake]

Assigning these out in the field while you're offline is going to be tricky.

I needed something similar to this a while back (multiple users offline in the field) and the best solution I found was to create the Site ID field in the layer but ignore it in Survey123.  Create a nightly-scheduled notebook that does something like this:

• Find existing Project Code + Initial combinations, determine the next number in the sequence for each
• Identify records with null Site ID values, assign Site IDs to them
• Apply the IDs to each of your samples

This means that on the day these are submitted, they'll have null Site IDs, but they'll be populated the next day.  This wasn't an issue for my workflow, but might be for you.

Another approach could be to have near-empty records waiting in your users' inboxes with pre-populated Site IDs, I'd tried something like this but the users didn't like it as much.

[DanHaynes123456]

Hey thanks, yeah unfortunately the site id is required at the point of capture as it is used to build the sample id, the crews can't label the sample bags in the field without the id. Looking through the community posts this looks like it's been an issue for a decade. Crazy there is no workable solution yet. Pretty sure my users won't go for 1000's of forms in their inboxes but thank you for your help.

[MobiusSnake]

Hmm...  You could filter the inbox by username so they only see their own records in there, and generate new records weekly (schedule it for Sunday night, for example), so there'd be a manageable amount in there.

Can you label the bags in advance with a QR code?  If you have a QR code on the label, S123 can read the Site ID and Sample ID from that.

[DougBrowning]

When we do this we have a crew number for each crew then number based off of that.  Does not need to be unique across the entire DB really.  Or since we assign plots to crews we usually make an id based off the date vs worrying about a number increment. Like PlotID_MM-DD-YYYY.

[Neal_t_k]

@DanHaynes123456, Yep you are going to have issues trying to auto increment the 0001, 0002 etc.   Pretty low tech but you might just consider having users enter the start number for the site,  Once the start number is entered you could increment through for multiple samples.  

Any check against a database, even for multiple samples on the same day would require a user online.

Not sure this would work for your flow, but since you are not concerned with users starting with the same number, have you considered and ongoing survey and using nested repeats? Meaning users would open the same survey every time and just add additional repeats.

Main form - User information.

Child -  Site info - Setup initial site id 

Grand Child -  Sample info incremented on .1 after the initial sample for a site

See attached, may explain it better or at least give you some ideas.

How was this accomplished in your last system with a .mdb and being offline.

[DanHaynes123456]

Hi Neal, the old system was built in 2009 (hence updating now) and for that system we have a website (data warehouse), the user requests the project they are working on, fme then pulls the data from the sql and downloads it as a .mdb. Fully set up with all of the sql filtering,calculations etc. The user opens the mdb selects the project and their user code. Once opened it reads the last site for that user and project and increments by 1 for new sites. when finished the user re uploads the data to the warehouse and it is synced back into the sql. Its clunky but for when it was built it worked and kept multiple crews data unique as each person had their own user code.

[Neal_t_k]

@DanHaynes123456   That makes sense.  What I proposed is actually kind of similar then.  Each user would essentially have their own version and continually update their entry.  The sentbox could be useful in this situation.

[TylerGraham2]

For your interval problem I attached an xlsform with some options.  You can use an indexed repeat to pull the end value from the last repeat and use it to populate the start value for your current repeat.  That will work best if you don't have regular intervals.  You still need to manually put the end depth in though.  

If you are doing something like geotech work where samples are certain lengths at certain intervals you can do some math. In the form you enter start depth, sample length, and interval between samples (assuming the interval is from start depth to sample start depths).  It grabs the position index of the repeat of the form and uses some math to calculate regular intervals start and stop depths.