Entities - get the most recent record from the database

Support
1

1. What is the issue? Please be detailed.
Hi.

Thank you so much for the work that has already gone into developing the entity based submissions. It is really helpful - a game changer.

I have been playing around with the Tree forms that Kathleen demonstrates in this video to try to understand how it works. Using these as the example I have created a tree entity using the tree_registration_form and then will enter multiple records of the tree measurements for that same tree using the follow-up form. In the follow-up form is pulls a measurement from when the entity was created as the reference. What I would like to do is to pull in the most recent measurements from the tree follow-up form and use that for reference and validation.

So I have set the tree_follow_up form to save to a separate csv called tree_measurements.csv.

What I can't work out is how to interrogate tree_measurements.csv to get the most recent submission for that tree. The two ways I can see is to either use the submission date/time, or to create a serial number for each tree's submission and pull the biggest serial number in as the last measurement. However, I can't quite make ir work. I think that I lack understanding of how to use this syntax instance('tree_measurements')/root/item[tree=${tree}] and instance('tree_measurements')/root/item[tree=${tree}]/circumference

I attach the file of how far I reached
trees_follow_up_Previous.xlsx (80.3 KB)

This Tree Registration Form form is unchanged from Kahleen's demo
trees_registration_entities.xlsx (61.4 KB)

2 Likes
2

Thanks for trying out this functionality and sharing what you're thinking about and experiencing! I see you're finding out why we are calling this a preview for now -- there's not quite enough functionality yet for it to be usable in most contexts.

Eventually it will be possible to define a form that updates entities. That will make it possible to keep track of latest values at the entity level. You'll then be able to use entities to drive workflows while using raw form submissions for more complex analysis. For example, you might have a current_circumference property on trees which gets updated each time the circumference is measured, possibly by a variety of different forms representing different encounter types with an individual tree.

Updating entities is trickier than creating new ones: it's important to select the correct entity to update, to deal with multiple updates that may come in from different sources, etc. So we want to make sure we get this right.

In the mean time, I think there are two options to consider. If you have well-known steps in your workflow, you can create a separate dataset for each step of the process. Below are three forms that implement a basic environmental monitoring scenario and illustrate this concept. The first form lets you register a new site to monitor. The second form lets you register a sample for a given site. And then the third form lets you process samples and get their results. Using separate datasets for samples and lab results lets us track the latest status of a real-world sample: either it's only in the samples dataset in which case it hasn't been processed yet or it's in both the samples and lab results datasets meaning it has been processed.

In the trees example, there might be separate trees_a_visit and trees_summer_visit datasets. Then you could explicitly read the circumference for a tree with a given id from both datasets and only use the fall one if there was no summer measurement.

If the workflow steps are not known ahead of time (e.g. people record data about trees they encounter whenever they happen to encounter them), then what you propose by finding a maximum date or serial number sounds good. You can get the maximum identifier using a calculate with a calculation something like:

max(instance('tree_measurements')/root/item[treeid=${treeid}]/visitid)

Let's call that latest_visit. You can generate the next visitid by adding 1 to that.

Then you can get information about that latest visit using instance('tree_measurements')/root/item[treeid=${treeid} and visitid=${latest_visit}]/circumference. circumference is the entity property you want to access.

We definitely want to make this less clunky. If you do try it out and would prefer to see a worked example, do let us know.

2 Likes
3

Thank you Hélène, this was really helpful. As I am exploring this functionality wiht a view to using it for irregular workflows, I followed on the suggestion assigning a serial number to each tree->measurement and it works fine now.

This is rough and there are more elegant ways of doing this, but I share the excel in case it is helpful to anybody else exploring this functionality. Essentially once the tree entity is created the follow-up form checks whether there are already measurement entities that exist for that tree and how many. based on this is assigns a visitid 1,2,3,4...etc which is specific to that tree and pulls out the circumference from the measurement entity for the last visitid

This preview of the entity based functionality is already looking incredibly helpful and I am really excited that there are more developments to follow

The edited from from the demo - it probably isn't necessary to have the fields ${exists} and ${counter}, but I like them both. The registartion form remains unchanged
trees_follow_up_Previous.xlsx (80.4 KB)

2 Likes
4

Thanks for sharing what you came up with! Great to see you were able to put it together with my brief ideas.

A big thing to note is that currently there's no way to ensure the uniqueness of visitid. If you visit a tree and then start another visit before having sent and approved the original submission AND updated the dataset on the device, you'll end up with two visits with the same id. I think this will make the form crash on the next follow-up (maybe this counts as a way to ensure uniqueness? :grimacing:). This isn't a problem for every workflow but would be a dealbreaker for some.

Another thing to note is that client-side performance for thousands of visits will not be great.