QGIS Expressions – Autogenerate Consecutive Job Numbers Per Group

attribute-tableexpressionqfieldqgis

I am looking to autogenerate consecutive numbers per group by an attribute form default expression whilst completing a survey using QField for QGIS.

Requirements:

  • A job number will only be created when a separate column, "SpanClear" = No.
  • I also require the job numbers to reset to 1 for each unique group in the column named "CircuitID".
  • As this is a survey to be completed in QField, the attributes start off empty/null and will be populated on a mobile device
  • Each time a job is identified (by "SpanClear" = 'No'), the jobs should be consecutively numbered, 1, 2, 3… etc.
  • I require an expression to add in the default value of the attributes form (if there is a better way, please let me know)

I have searched through many previous questions and although there are workarounds using plugins, virtual layers and tools in QGIS, I require an expression so it will work in QField.

I want to automate the numbering to reduce chances of errors during surveying.

The expression I have created so far is:

CASE 
    WHEN "SpanClear" = 'No'
    AND 
    "JobNo" is NULL
    THEN $id=array_first(array_agg($id, "CircuitID"))
    
    WHEN "JobNo" is not NULL
    THEN  array_agg((maximum("JobNo")+1),"CircuitID")
    ELSE
    NULL
END

The above expression names the first job per group "1", but any following jobs are set to "0".

Is what I am asking for even possible through an expression? I'm not a developer so I may have missed something obvious.

How it will look in QField

Table to autogenerate consecutive job numbers per group BY EXPRESSION for surveying in QField for QGIS

How the current expression is autopopulating

Table when autopopulated using current expression

How the expression looks in Attributes Form

Expression in dialog box

Best Answer

I am not sure what you meant to achieve with the $id=array_first(array_agg($id, "CircuitID")) part of your expression, as that's what is returning the 1 and 0 values.

It's checking whether the current feature's $id value matches the first $id value in the same CircuitID group (according to no particular order...). If it does - it returns a 1 (true), if not, a 0 (false). Only one feature can result in a match, hence why you start with one 1 and all the rest in that CircuitID group have a value of 0.

I presume you meant to use that as a condition for another expression that would generate the incremental value, but because you preceded it with THEN - it's actually returning the check itself.

This default value expression in the JobNo widget will get you started:

CASE WHEN "SpanClear" = 'Yes' OR "SpanClear" IS NULL 

THEN NULL 

ELSE 

coalesce("JobNo",coalesce(maximum("JobNo","CircuitID"),0)+1)

END

The first 3 lines should be self-explanatory, but here is what is happening with line 4 working inside out:

  1. maximum() function. You don't need to use the array_agg() function here as maximum() can already aggregate/group by within the function itself. This finds the maximum value for JobNo within the group that has the same CircuitID as your current feature.

  2. The second coalesce() function. Checks if the result of step 1 is NULL, and if it is, returns 0 instead. This is for situations where there is no maximum() because you are entering a new CircuitID (so no existing features to group), or the existing CircuitID group has not been updated (so all JobNo values in that group are NULL and a maximum cannot be calculated).

  3. +1 - this increments the result of step 2 (which is either the maximum JobNo for your CircuitID group, so you go one up, or it's 0, so you start your sequence at 1).

  4. The first coalesce() function. Checks if JobNo already has a value in it - if it does, then it leaves it alone. Otherwise, it applies the above sequence. This is so that if you check 'Apply default value on update' in the widget, and you go back to a feature that already has a JobNo applied (say job number 2 out of a current max of 4 in that CircuitID group), if you update a completely unrelated field like comments, it doesn't recalculate the JobNo (overwrite the existing value of 2 to 5, the new maximum + 1).

Make sure your form is properly designed so you make sure CircuitID and SpanClear cannot be null for a form to be saved.

You may need to tweak the expression depending on how you want your form to behave when you go back and edit records.

One option may be to disable 'Apply default value on update' so that once the JobNo is calculated the first time, it is locked in place.

But what if you initially said SpanClear = No for a record and now you want to revise that to SpanClear = Yes (or vice-versa?)

If it already had a JobNo do you want to retain it overwrite it with NULL? If the latter, do you want all the other JobNo values in that CircuitID group to readjust (in which case you'll need a virtual field)? or are you ok with there being a gap in the sequence? etc.

Related Question