I have a table with species sightings and I would like to add a field with the sighting number in that day (every day should start again from number 1). Ideally this should be done automatically as I add the new record (Qfield form entering). The result should be like this:


This formula works at the time I'm entering the values, but if I need to correct them later the field updates and gets wrong values.


WHEN "Sighting_num" is NULL THEN '1'

ELSE sum(1,day( "HORA_DETE" ) || month( "HORA_DETE" ))


1 Answer 1


If I understsand you correctly, you want the sighting numbered in time order for each day. And this needs to change if you make amendments. This calls for an ordered list, or array.

But datetime fields can be tricky. If you use an ISO datetime format, complex string expressions for sorting and grouping dates are simplified. So the following assumes you have an ISO datetime field, as in widget configurator below. If its not like this already, you may need to update existing dates.

enter image description here

Once your field is like this it is alot easier. Create a Calculated Field "Sigthing_num", with following expression. I have asumed you have an ID field called "id". Change this as required.

    array_agg("id", to_date("HORA_DETE"), order_by:="HORA_DETE"),
+ 1

In the array_agg() expression, to_date("HORA_DETE") is a group_by field. the to_date() converts the datetime value of HORA_DETE to a date value (no time), so only records for that day are returned. The order_by argument does not need to be converted, it is sorted from first sigthing to last sighting. array_find() then returns the index number of the matching ID. It's a zero-based index, so we have to add a 1.

In QGIS, to see the update you have to save the record and then update the attribute table. I haven't tried but in QField it should update after the record is saved.

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.