Field expression bug

[olegzhelezn]

Greetings to the QField community!

Writing here to report a potential bug and ask for advice.

I have a vector layer with some of the fields regulated by default expressions. Specifically, there is a set of points which need to be regularly updated, and i am trying to do it in such a way that the original point preserves the information about all updates.
For this, I have created two types of fields (=> columns in the attribute table):

• an original field with the current value
• a field that accumulates the values of the original field throughout the time.

For example:

I have a field called ch_date (checkup date), which is being transformed into a string and summarised in the ch_dates field, which has a default value of:
"ch_dates" +';' + to_string( "ch_date")

This works perfectly in QGIS, with ch_dates containing all checkup dates in the form of delimited string.
However, in QField the cumulative data field gets updated twice, once when writing the attribute, and then second time when saving the updated feature. If the attribute is rewritten (edited) before saving the feature, than the date is added again, and again, and again...

This is not very comfy, but it works in some way, though.
But when it comes to the same expression with string data, e.g.:
"ch_values" +';' + "ch_value"
-- then it does not work in QField whatsoever...

Could this be addressed in the future releases?
Or maybe, someone might recommend a way to solve this in an alternative way? E.g. creating nested tables somehow?...

[lucienicolier]

Hi @olegzhelezn,

We are going to have a look on this issue, but we do not know exactly when.
If it is urgent, please contact [email protected].

Best regards

[olegzhelezn]

Hi @lucienicolier!
Actually, I found out that the same thing sometimes happens in QGIS, too.
Specifically, updating the field that references and takes data from a different field happens with each edit.
For example, I edited ch_value writing 'apple', and ch_values resorded 'a';'ap';'app';'appl';'apple'.

I suppose, it is not something one commonly does, so no surprise this bug is there.
However, I also understand it is not exactly a question to you, but rather to QGIS development team.

So, I would like to share the nice (and probably 'default') workaround I found to bypass this issue. Maybe someone will find it helpful.
I created a new non-spatial table within the same GeoPackage file, which contains the columns for the date of update and the new value, as well as the id if the feature that is updated.

Then I set up the relations between IDs in the two layers, according to the manual here: https://docs.qgis.org/3.22/en/docs/user_manual/working_with_vector/attribute_table.html#introducing-1-n-relations

The result is a nested form, in which multiple entries related to the same feature can be made.

[nicpitsch]

Or maybe, someone might recommend a way to solve this in an alternative way? E.g. creating nested tables somehow?...

Hello @olegzhelezn, one possible solution would be creating an SQL Trigger on your layer for that, which populates your ch_dates history field with the date versions. It's a standard known by several database systems like Sqlite/GeoPackage and PostgreSQL.

It could be like this (Sqlite flavor):

drop trigger if exists set_ch_date_history;
create trigger if not exists set_ch_date_history after update of ch_date, ch_dates on mylayer
when new.ch_date is not null and (new.ch_dates is null or old.ch_dates not like '%;' || new.ch_date || ';%')
begin
update mylayer
set ch_dates = coalesce(t.ch_dates,'') || new.ch_date || ';'
from (select * from mylayer where fid=old.fid) as t
where mylayer.fid=old.fid;
end

your mylayer would look like this:

[olegzhelezn]

Thank you, I will try that!