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:
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.
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).
+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).
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.
Best Answer
The first feature returns a
NULLformaximum("id")as there are noidvalues to calculate a maximum on.To get around this, use the
coalesce()function which returns the first non-null value in a series of parameters —coalesce(expression1, expression2, expression3)is a more concise version ofCASE WHEN (expression1) IS NULL THEN (expression2) CASE WHEN (expression2) IS NULL THEN (expression3) ....For the first feature to be 1, use
coalesce(maximum("id"), 0) + 1as your default value.Which means that when you digitise your first feature and
maximum("id")returnsNULL, you go to the next parameter incoalesce(), which is0. Then add1to that.From your second feature onwards,
maximum("id")returns the maximum"id"value (maximum"id"from existing features =1,2,3...)And then adds
1to that (result"id"for current feature = maximum"id"+ 1 =2(1+1),3(2+1),4(3+1)...)See Autogenerate consecutive job numbers per group using QGIS expressions and the comments to this answer to Is there a way of auto_increment for the ID column in QGIS.