Best practice for form submission + updating a field on another table (Covid app question)

Firstly, @appsheet. Thank you for supporting those of us who are building covid-related applications.
I’ve read through so many different responses on how to update a table if another table is changed but I can’t seem to find a solution that works 100% of the time.
My question is pretty simple I think:
We have 2 main tables - visits and cases.

  1. A visit includes conversations that we have with patients where they complain of symptoms. The 5 visit statuses are: Non-Covid, Suspected, Active, Recovered, Death
  2. The cases only have 2 statuses: Open or Closed
    The visit table can have multiple visits referencing a single case.
    The case table only references the latest visit.

Basically, when we log a visit as “Suspected” or “Active”, we want the case status to be “Open”. Otherwise we want the case status to be “Closed”. (We typically follow up for 14 days to make sure these people are ok)

I’ve tried both of these methods:

  1. On the visit form submission, I changed the Behavior: Event Actions to be Execute an action on a set of rows. So for a record on the visit table, I update the case status on the case table.
  2. I have an automation for every added and updated visit, the case status updates.

However, when I look at my data, both of these implementations of this action only updates 80% of case statuses. There are 20% of the case statuses that do not update. Therefore, I can’t tell if this is a bug or if I am not using best practices to accomplish this task. Is there a more reliable way to do this?

Thanks in advance for your help.

Both of these methods are acceptable ways to perform your desired update. Personally, I would recommend method 1 because the user making the changes will see the update immediately.

I have never had an issue with this option not updating consistently unless it was something I implemented incorrectly. I suspect that there is something off about your implementation as well.

Can you show us your “Execute an action on a set of rows” action as well as the action it calls? WE can help form these sort out the problem.


Thank you for the response. This is what I did:
On the case table, I have a behavior that executes a sequences of 2 actions.

First action opens a “Visit Form” UX:
LINKTOFORM("visit_form - Adding from case", "case_id_ref", [Case_ID], "patient_id", [patient_id_ref])
In the “behavior” I have the “event action” opens a behavior titled “Update Case After Visit Submission”

“Update Case After Visit Submission” does the following:

  • Executes an action on a set of rows on the visit table
  • With the reference table being the cases table
  • With the reference rows: SELECT(cases[case_id],[case_id]=visit[_ThisRow].[case_id_ref])
  • With the “reference action” being updating the case status (from “Open” to “Closed” status or vice versa)

The 2nd action is on the “cases” table with "set the values on some columns in this row: "
For status column: IF(OR(([latest_visit_status]="Recovered"),([latest_visit_status]="Death")),"Closed","Open")

Maybe the issue is that I have a reference action as well as a second action? Seems repetitive.
It’s just very strange that it only works like 80% of the time.