Issue: Strange upload transect survey from the app, missing data of one section

[CrisSevilleja]

Describe the issue

Indicia Warehouse User ID: 246153 reported his transect BIAR, EBMS:España:72, Id 308776 with three sections has strange behaviour when collecting data on the app and the upload of data is not correct.

On the app for this user, the transect appears with four sections, but the app jumps to section 4 directly from section 2. When uploading records to the eBMS website, section 3 doesn´t have data, when indeed it was recorded on the app but as section 4. Therefore, all records from section 4 are instead from section 3.

The user can enter data on section 3 on the app, but because the app jumps to the section 4, he continues entering data on the wrong section.

Expected behaviour

The transect BIAR on the app shows only three sections and the consecutive sections are correct, from section 2 pressing next should bring you to section 3. This will allow to submit data correctly.

To Reproduce

As reported by the user: The way to submit the data: I select the transect first (I have 2 transects). I enter the meteorological data. I select the first transect and start counting. I finish this section 1 and press next. I continue with section 2, finish entering data and press next, in this case, I get to the 4th section (which doesn't exist) and I put the data of the third section in this one.

I can enter on section 3 but by default after section 2, the app brings me to section 4. So data from the 3rd section is actually in the 4th section since the beginning.

App Version

V1.20.0 (241)

Device

Galaxy A12

When did this happen

since March 2023, new transect of 2023

Screenshots

Screenshots from the transect visit of the 27th of March 2023. This visit is submitted, on the website no data appears for section 3.

Additional context

the user continues using the app for registering transects and continues with this problem in this specific transect BIAR. No problem reported to another transect done on the app.

The data from section 3 is missing, so it should be recover from the submission. Thanks in advance.

[JimBacon]

The transect was created with 4 sections and then one was deleted. id	name	deleted
308777	BIAR - S1	false
308778	BIAR - S2	false
308779	BIAR - S4	true
308780	BIAR - S3	false

I guess the app needs to update the location information. How is that done @kazlauskis?

When that is fixed I will need to update all the samples saved against S4 to refer to S3.

[kazlauskis]

@CrisSevilleja, does refreshing the transects not solve the issues?

[CrisSevilleja]

thank you @JimBacon and @kazlauskis for checking this. The user confirms that section 4 doesn´t appear anymore on his phone :) great.

Now if Jim you can update the data from S4 to S3 this would be done. good teamwork. Have a nice weekend!

[kazlauskis]

Sorry, reopening for Jim to fix the records.

[JimBacon]

There are 8 walks where edits are required. In each case, 4 sub-samples have been created. Where any occurrences have been attached to a sub-sample for the deleted section (location_id = 308779), these need attaching to the sub-sample for section 3 (location_id = 308780)

The sub-samples for the deleted location then need deleting. The changes must be applied to both the core tables and the cache tables.

The following query was run to accomplish this:

with walks_to_edit as (
    select s.id, count(*)
    from cache_samples_functional s
    join cache_samples_functional ss
        on ss.parent_sample_id = s.id
    where
        s.location_id = 308776 -- walk location_id
    group by s.id
    having count(*) = 4
),
sections_to_edit as (
    select w.id, section_keep.id keep_id, section_delete.id delete_id
    from walks_to_edit w
    join cache_samples_functional section_keep
        on section_keep.parent_sample_id = w.id
        and section_keep.location_id = 308780
    join cache_samples_functional section_delete
        on section_delete.parent_sample_id = w.id
        and section_delete.location_id = 308779
),
update_occurrences as (
    update occurrences o
    set sample_id = s2e.keep_id, updated_on = now(), updated_by_id = 2
    from sections_to_edit s2e
    where o.sample_id = s2e.delete_id
    returning o.id
),
update_cache_occurrences as (
    insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
        select 'task_cache_builder_update', 'occurrence', id, null, 100, 2, now()
        from update_occurrences
),
delete_samples as (
    update samples s
    set deleted = true, updated_on = now(), updated_by_id = 2
    from sections_to_edit s2e
    where s.id = s2e.delete_id
),
delete_cache_samples_functional as (
        delete from cache_samples_functional s
        using sections_to_edit s2e
        where s.id = s2e.delete_id
)
delete from cache_samples_nonfunctional s
using sections_to_edit s2e
where s.id = s2e.delete_id

insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
select 'task_cache_builder_update', 'sample', id, null, 100, 2, now()
from delete_samples

EDIT: I forgot that any sample attributes attached to the deleted section needed to be attached to the section S3. On checking, though, I found there were no such attributes.

[CrisSevilleja]

Hi Jim,

thanks for going further with this. I don´t really get about the subsamples, but I hope you can solve it.

I received a new message from the user telling me the visualization of the transect visit for 8 May 2023 is wrong, only showing one species Thymelicus when there were more butterflies registered. When I entered the visit through My samples, I can see there were more butterflies (44 individuals and 14 species)

but that is not appearing on the details of the visit on My visit page:

I mentioned you were correcting the data missing from section 3 and this can be a result of that work or because some arrangements were done. Anyway, I thought it was important to report this to you

[JimBacon]

With this recording method you do a walk divided in to sections in which you record the butterflies you see.

In the language of the database, the walk is a sample and the sections are subsamples. The species records join to the sections and the sections join to the walk.

What I can see here is that the species records, all except the one of Thymelicus, have somehow become attached to the walk rather than the sections so they do not show up in the recording form.

It is only this one record that is wrong. I don't know how it has happened. I don't see that my corrections can have caused this.

Since there is no information in the database about which species records should belong to which section, I cannot fix this on my own. If the recorder can, by any chance, indicate which sections the following records belong to then I can fix it. I have listed them in order of id which might possibly relate to the order they were recorded.

id	taxon	count
30245863	Pieris rapae	3
30245864	Pieris	3
30245865	Pontia daplidice	5
30245866	Thymelicus acteon	3
30245867	Pyronia bathseba	4
30245868	Maniola jurtina	10
30245869	Pararge aegeria	1
30245870	Hipparchia semele	2
30245871	Polyommatinae	2
30245872	Aricia cramera	1
30245873	Thymelicus sylvestris	1
30245874	Pieris brassicae	1
30245875	Iphiclides feisthamelii	1
30245876	Pontia daplidice	1
30245877	Hipparchia semele	2
30245878	Aricia cramera	1
30245880	Pyronia bathseba	2

[CrisSevilleja]

I am not saying you produced it, just that you were checking those data it could happen.

I ask the user if he can give me more information on the species recorder per section. Thanks

[CrisSevilleja]

H @JimBacon here are the screenshots from the app with the data of the transect visit 8th of May 2023 from the Biar transect. I checked the numbers and it is correct. The species appearing in section 4 belongs to section 3. Thank you in advance.

[JimBacon]

That's fantastic! How fortunate to still have the details. I have updated the record. We have one anomaly remaining.

• The database record for section 2 has an occurrence of Hipparchia semele with a count of 2
• The screenshot above has an occurrence of Hipparchia semele with a count of 1 and an occurrence of Hipparchia hermione with a count of 1.

Are you able to confirm which is correct?

For reference, I ran the following queries.

-- Section 1
update occurrences 
set sample_id = 21905967, updated_on = now(), updated_by_id = 2
where id in (30245863,30245864,30245865,30245866,30245867,30245868,30245869,
             30245870,30245871,30245872,30245873,30245874,30245875);
-- Section 2
update occurrences 
set sample_id = 21905968 , updated_on = now(), updated_by_id = 2
where id in (30245876,30245877,30245878);
-- Section 3
update occurrences 
set sample_id = 22647465, updated_on = now(), updated_by_id = 2
where id in (30245880);

insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
select 'task_cache_builder_update', 'occurrence', id, null, 100, 2, now()
from (values (30245863),(30245864),(30245865),(30245866),(30245867),(30245868),
      (30245869),(30245870),(30245871),(30245872),(30245873),(30245874),(30245875),
      (30245876),(30245877),(30245878),(30245880)) as t(id);

[CrisSevilleja]

hi Jim, thanks.

I would consider the correct one is from the screenshots which is the data the user recorded.

[JimBacon]

Thanks, @CrisSevilleja. I have followed your advice. Close this if complete.

The website now shows the recording form as follows:

I note that the occurrence of Hipparchia hermione was created at the time of the survey and deleted by the recorder a week later, on the 15th May, based on the database contents. Only the recorder could say if that was intentional.

For reference, I used the following queries:

-- Undelete the occurrence of Hipparchia hermione.
update occurrences
set deleted = false, updated_on = now(), updated_by_id = 2
where id = 30245879;
-- Undelete the attributes of the H. hermione occurrence.
update occurrence_attribute_values
set deleted = false, updated_on = now(), updated_by_id = 2
where occurrence_id = 30245879;
-- Change the abundance of Hipparchia semele from 2 to 1.
update occurrence_attribute_values
set int_value = 1, updated_on = now(), updated_by_id = 2
where id = 67655354;
-- Trigger the creation of a cache record of Hipparchia hermione.
insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
(values ('task_cache_builder_update', 'occurrence', 30245879, null, 100, 2, now()));
-- Trigger an update to the cached attributes of Hipparchia hermione.
insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
(values ('task_cache_builder_attrs_occurrence', 'occurrence', 30245879, null, 100, 2, now()));
-- Trigger an update to the cached attributes of Hipparchia semele.
insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
(values ('task_cache_builder_attrs_occurrence', 'occurrence', 30245877, null, 100, 2, now()));

[CrisSevilleja]

thanks, everything good! (so far)