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
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
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
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.
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).
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
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.