Set Values of Columns in Another Table Based on Values in the Current Row - Efficiency?

[mykrobinson]

I am looking at ways to make an app more efficient, and one current heavy hitter is the massive amount of expensive expressions that use nested lookup(maxrow()) style queries.

Let's say for example, i am creating admission records for a patient. A patient will have many admission records, depending on their health. There is an admissions table and a patients table. The patients table currently has a virtual column that will extract the admission date from their most recent admission record. Since this is a virtual column and there are many others, this makes the initial load of the table a bit heavy because there are thousands of patients, and i am learning that VCs are not necessarily calculated on demand, it seems they are all parsed at load/sync time.

So we've kicked around the idea of writing an automation that triggers when a new admission record is written, adding a physical column to patients to store the latest admission date, and writing to that column based on the bot trigger.

But herein lies the problem. Is there not an option to set the values of columns in another table? I am currently doing it by way of ref actions, but for the ref action to work, i find that i still have to use a lookup(maxrow()) nested expression, which is the root of the issue as it stands. It may be marginally more efficient doing it this way because it is on demand and only looking up for one patient at a time, as opposed to calculating all of them at once at load time. 

I see the option to create a new row on another table using values from this one, but not quite the other way, write values to a row on another table using values from [_thisrow]

I may have just thought myself into a corner and could be missing something slightly obvious, so feel free to help me out 😂

Thanks

[scott192]

So, we have a table called Patients and one of the fields on that table is called [LastAdmissionDate].  You intend for that field to ALWAYS hold the date of the very last admission record that are getting added over time.  We are going to set that value at the time that an Admissions record for that patient is saved and we will continue to update that value at every save of a new Admissions record.

To do this we are going to use an Invisible Action on the Admissions table.  This Action is going to be fired off at the time the Form is Saved for the new Admission...commonly referred to as a Form Save Event.

Create an Action on Patients.  We can call it UpdateLastAdmissionDate.  The Action is of type 'Data: set the value of some columns in this row'.  Choose the column that is going to hold the last admission date and put a formula MAX(SELECT([Related Admissions][AdmissionDate],TRUE)).  You can set this action to Do Not Display.

Create an Action on Admissions.  We can call it UpdatePatientRecord.  The Action is of type 'Data: Execute an Action on a set of rows'.  The Referenced Table will be Patients, the Referenced Rows will be LIST([PatientID])  <- This is the KEY field from your Patient Table.  You can set this action to Do Not Display.

Finally, go to your View that is the Form for your new Admissions record.  Scroll down to the Behaviour section and open it up.  You can see that there is a Form Save Event which is probably set to Auto Assign (go back).  Change that to the new Action you created (UpdatePatientRecord) and save.

Hopefully, you will now get 2 records to sync for every new admission.  The first sync is to save the Admission Record and the second sync is the Action that runs on the Patient record which should lift out the latest date from all related admissions records for that patient.

[mykrobinson]

okay, looks like i have already done this for some things but i suppose forgot about it 🙂 I suppose at the end of the day, there is simply no way to completely avoid lookups, selects, max, etc , but doing it this way makes it more modular in its approach and performs the tasks on demand and for one record at a time, which will, in theory, improve load time instead of having all those virtual columns calculating on the front end.

Thank you

[dbaum]

---

@mykrobinson wrote:

nested lookup(maxrow()) style queries

---

Consider whether you could use a more efficient expression in the current virtual column. For example, based on your description, something like the following might be compatible with your data structure.

MAX([Related Admissions Records][Admission Date])