1

I'm building a huge form to be used in QField for wetland surveys. The form will be used by several people on different devices at the same time, on the same project.

The project is composed of a point layer and it's form, with multiple tables fillable through 1:many relations with the point layer.

I'm trying to generate a unique ID that is preventing conflicts when syncing the project (i.e. two devices can't produce the same ID, otherwise one will erase the data from the other).

This unique ID is also meant to be used in the relations so it has to be generated when the point is created and stay as is. Hence the format I came up with:

@user_account_name||'_MH'||incremental number

@user_account_name being unique for each iteration of QField, placing it in the ID makes it impossible for two devices to produce the same unique ID.

'_MH' is short for "Milieu humide", wetldand in french. Don't worry about it.

Now the issue: I'd like the incremental number to go from 1 to infinite for each device. For example:

Device 1:

U0_a584_MH1

U0_a584_MH2

U0_a584_MH3 ...

Device 2:

U0_a456_MH1

U0_a456_MH2

U0_a456_MH3 ...

Here's my code to achieve that (input in default value of a text edit widget, with apply on update unchecked) :

CASE WHEN CASE WHEN maximum(substr(ID,strpos(ID,'_MH')+3),substr(ID,0,strpos(ID,'_MH')-1)) IS NULL

*Groups IDs by left part of string (i.e. @user_account_name) and tests whether or not the max incremental number is null*

THEN @user_account_name||'_MH'||1

*If said value is null, use 1 as incremntal number in ID*

@user_account_name||'_MH'||(maximum(substr(ID,strpos(ID,'_MH')+3),substr(ID,0,strpos(ID,'_MH')-1))+1)

*If max value for this @user_account_name is not null, increment max+1 in ID*

END

In theory, it should work. Result preview in expression builder even gives me what I expect:

enter image description here

But when I digitize a new point, I always get the same ID, with 1 at the end. From what I've deduced, the code works up until I add the group_by:= argument to the maximum() function.

Weirdest part is, it used to work all the time ... like this morning, had no problem with the code. So either I broke it and am too dumb to see my mistake, or QGIS reacts inconsistently to my code in some obscure manner ...

I've got a hunch the answer is a so what am I doing wrong??

0

2 Answers 2

3

I'm not sure why, per your report, it used to work and now it doesn't, but I think the issue here is that you are using group_by to aggregate the maximum substring of a field across all features... grouped by the same field you are trying to populate. It circles upon itself.


Explanation:

Keep in mind that when you first digitise a new feature, the ID field doesn't have a value. That's not a problem for maximum() on its own, though, as it goes through the ID value of all the existing features and returns the maximum value, without any reference to the current feature that you are currently digitising, so it's fine.

But once you add a group_by parameter to maximum(), the function still tries to return the maximum ID value from all the existing features, except this time it's the maximum value from a group - a group where the ID values match the current ID field value... but because the ID field is actually empty before it runs the expression, there is no current ID field value. So the expression fails here.

You do get a sequential value showing up in the expression preview because the preview calculates the result based on an existing feature where the ID value has already been filled in, so it is able to go "okay, based on this feature's ID value, this is device X, so let's find the highest value for IDs collected by device X".

When the ID value hasn't been filled in, however, the expression can't work out what device it's on and therefore how to group the data and extract a maximum value.


I was able to get your code to work with a mock dataset where it correctly extracted the highest value based on usernames, by using filter instead of group_by.

This allows you to make reference to the @user_account_name variable. The maximum() function goes through all the existing features, filtering out only ones where the account name (i.e. substr(ID,0,strpos(ID,'_MH')-1)) matches that variable, then finding the maximum of that. It doesn't need to look at the current (empty) ID value of the feature you are creating.

I have also simplified your expression by using coalesce() which tests whether the first parameter is NULL then returns the next non-NULL parameter (in this case, 0 - so if the max value is NULL it starts from 0, then adds 1, so you always start from 1).

@user_account_name||'_MH'||

(coalesce(maximum(to_int(substr(ID,strpos(ID,'_MH')+3))

,filter:=substr(ID,0,strpos(ID,'_MH')-1)=@user_account_name),0)+1)


Example where I change the @user_account_name variable from Device1 to Device2 on the fly:

enter image description here

1
  • Wow it is so simple yet so elegant. This is exactly what I was trying to achieve. Thank you very much!!! Also, thanks for the great explanation. It all make sense and I'll keep it mind in the future. Commented Jan 29, 2023 at 5:06
1

I would suggest separating the information in 3 fields:

  • a uuid as primary key
  • a text for @user_account_name
  • a drop down for the MH type

this will allow you to have a very simple and very versatile solution since you're not combining information into one single string. In general we recommend using UUID as primary key since it solves plenty of issues connected to collaboration.

cheers Marco

3
  • Yes I considered using UUID but discarded the idea as the primary key is to be used as a label on map during the survey. Being able to have a "palatable" ID is key to assess progress of each surveyor. Also, since the primary key "connects" each points to all features in child tables through relations, I have to be absolutely sure each point has a unique ID. Although the probability of getting the same UUID twice is very low, it ain't zero. If it was to happen, data would get mixed and I wouldn't know. Thanks for the suggestion anyway, I will consider it on simpler projects. Commented Jan 29, 2023 at 3:44
  • I'd honestly separate the two concerns, labelling and unique ID come with very different requirements. As for UUID collision, per Wikipedia, the number of UUIDs generated to have atleast 1 collision is 2.71 quintillion. This is equivalent to generating around 1 billion UUIDs per second for about 85 years... Commented Jan 30, 2023 at 7:28
  • 1
    That sure is low probabilities. So far @she_weeds 's solution seems to be working perfectly but I'll definitely consider UUID if I run into anymore issues. Commented Jan 30, 2023 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.