ODK2 Updating master table from linked table

Hi all

I’m wondering if there is a way to update the master table, with entries from a linked table.

In my case, we are following admitted children at a hospital with daily visits. We want to keep track of which section and bed they are admitted to. But we also want to keep the history of which section and bed a child has been admitted to. To do this we are using a linked table where we each day collect the information.

Each child has one entry in our master table, but can have many entries in the linked table. We are using the _id to keep track of each child in the linked table.

We would however like to automatically update the current section and bed in our master table if we registrer that the child has been moved to another section or bed. Can this be done?

Best regards

Hi @Andreas!

It is definitely possible to update a table – you can write to the same table with an update from a different point in the survey. However, I am not sure if you want to update the same table or a different table. It sounds like you have a child table (observation is a child) and a bed table (observation is a bed) and so you want to update across tables, not update the same table? So are you trying to take something from child table and have it write to bed table? You can update things across tables somewhat, possibly using async_assign, but updating the same table is easier… if you could describe a little more about the data structure, in terms of tables, that would help. Or upload examples!

Best,
Caroline

Hi @elmps2018

As always your help is much appreciated!
I will try to explain a bit more and attach an example of the forms.

We have several surveys which all write to the same table (master table). We are collecting a lot of information on each child in the hospital, but on different times, hence multiple surveys. A child is registered at consultation and the information is obtained at certain times: Consultation, daily rounds, and discharge.

However a child can move to different section (hospital wards might be a better word in english) at the hospital during its stay, and we would like to register all the sections the child has been to.
If we for example register a child with measles we will be able to check what room(s) the child has been in and further check which other children that might be infected.

For now the section (hospital ward) and number of the bed is registered once a day at each hospital rounds. This is done in a linked table.
And example:
Master table:

_id admission date name birthday bed section
ID1 adate1 name1 dob1
ID2 adate1 name2 dob2

Linked table:

_id rounds date name bed section
ID1 rdate1 name1 2 B
ID2 rdate1 name2 5 I
ID1 rdate2 name1 2 B
ID2 rdate2 name2 3 A
ID1 rdate3 name1 2 B

We would like to tranfer the most recent bed and section to the master table, since we are using that table to keep track of admitted children. And also populating the linked table with the currently known information.

Attached are small parts of the involved surveys.
initrounds.xlsx (36.8 KB): Assigns the initial section and bed to master table
laterounds.xlsx (36.9 KB): Survey for rounds which calls the linked table
rounds.xlsx (30.9 KB): Survey for the linked table

Any help is welcome also suggestions to change how we are handeling the history of a child is welcome.

Best regards.

Dear @Andreas,

Thank you for the additional info! I think I see much more clearly now! Okay, so your goal is to take the most recent information from the “rounds” table I shall call it (the linked table) and transfer it back up to the “master” table where an observation is a child – basically take bed and section from the most recent date in the linked table and update it in the master table.

I certainly can see how to do that when you are in a child observation, that it would update and show the most recent bed and section when you get to that screen. So let’s say you open name1 as the instance from the master table, and then go to the rounds screen for name 1, add a round instance (in the linked table) with a new bed and section. When you save that subform and end up back out at the child level, you could have either the same or next screen with variables for bed and section. You would need to add these to the model sheet of the master (child) table with the same structure and then assign them with an async_assign. I think you should be able to find the observation in the rounds table with the same child _id and the most recent (maximum) date and then assign the bed and section from that observation to the child table. async_assign is not in the docs, but there are some past posts on this in the forum and I am also attaching an example where we ask people if the person sending a remittance is one of the household members they listed previously (we show the name and then record the line number) in the queries. I think this is something like what you would do. You may need to calculate the maximum date in the subform in one query and then filter on that in the next query.

quest3_14_2_update.xlsx (36.0 KB)

Best,
Caroline

Hi @elmps2018

Thank you for your suggestions! Your ideas and example together with this post: Getting data in a linked_table entry for parent form - #2 by Mitch_S solved the problem.

Best!