Auto increment field in QField form per parcela based on max value by uuid

Ask the Community
1

I am using QGIS + QField with a GeoPackage.

I have a parent-child relation:

Parent layer: Parcela
Field: uuid_parcela

Child table: Arboles
Fields:
uuid (foreign key to parcela)
ID_Arbol (integer)

Trees are created from the relation widget inside the parcela form.

What I want is this:

When creating a new tree in the form, ID_Arbol should automatically be the maximum value of ID_Arbol for that same uuid plus 1.

This increment must be per parcela, not globally.

Example:

For parcela with uuid = A, if the existing tree IDs are:
1
2
3

the next tree should automatically be:
4

But if I create trees in another parcela with uuid = B, numbering should start again from:
1

Another important point:
sometimes the suggested value may be changed manually before saving.
If that happens, the next new tree should still consider the maximum saved value for that parcela.

Example:
for parcela A, if the saved values are:
1
2
3
3

then the next new tree should be:
4

Important requirement:
the number must appear in the form before saving, because the field crew needs to see the tree number immediately.

I tried using aggregate(), relation_aggregate(), and SQLite triggers.

Triggers work correctly after saving, but the value does not appear in the form before saving.

My question is:

Is there a recommended way in QField forms to calculate the next ID_Arbol as MAX(ID_Arbol) + 1 for the current parcela (same uuid), so that numbering restarts at 1 for each parcela and still works correctly even if some values are manually edited?

Thanks!

2

This should be possible with an aggregate expression which also contains a filter.

set a default value in your attribute form for ID_Arbol:

CASE WHEN
aggregate(‘Arboles’,‘maximum’,“ID_Arbol”,“uuid”=attribute(@parent,‘uuid’)) > 0
THEN aggregate(‘Arboles’,‘maximum’,“ID_Arbol”,“uuid”=attribute(@parent,‘uuid’)) +1
ELSE 1
END

This expression is looking for the maximum value in the field ID_Arbol with an additional filter “uuid”=attribute(@parent,‘uuid’) to limit the features used for calculating the aggregate.
@parent does not refer to the relationship but is needed in this expression. Often @feature is used in the attribute sub-expression, but for some reason the aggregate needs @parent.

The CASE WHEN is necessary because the +1 does not add 1 to NULL, so the expression first checks if the max value is greater then 0.