Update Column in Master Table After Update in Child Table

David_Jones1
Participant III

Hi all.

I’m looking for the best way of achieving this. I have a small fleet database with a master table of vehicles and 2 child/related tables - Vehicle Check Records and Vehicle Service Records

On the master table for each vehicle I have a column for current mileage, current driver and status (Checked in at Garage A, Checked In at Garage B, Checked Out from Garage A, Checked Out from Garage B, Service etc.)

When a vehicle is checked out or in the current driver, mileage and status is known.
When a vehicle is in for service the current mileage is known and by default the status is Service

So each time a Vehicle Check Record or Vehicle Service Record is created or amended, I’d like to automatically update the relevant columns in the master Vehicle table.

Any help on the best way to achieve this gratefully received.

Cheers
David

0 11 669
11 REPLIES 11

tony1
Participant V

Hi @David_Jones. You could use virtual columns in the master table. The virtual columns would need to use a formula that selects from the related records to calculate your values. Here’s an example that shows how to get a reference to the most recent child record https://www.appsheet.com/samples/This-app-shows-how-to-get-the-most-recent-related-row-for-a-table?a... It might be a good starting place.

Hi @tony Many thanks for your response and apologies for not coming back to you until now. I’ll check the sample out, although I think I’m already using that method for another purpose.
If I use virtual columns, is it possible to write those values back in to real columns in the underlying data so that, for example, a chart or a pivot table in the spreadsheet could take advantage of it?

@David_Jones Virtual columns are not written to your sheet. Those values only exist inside your app (they’re computed every time you sync or change data).

You can use an app formula in a non-virtual column, though. And those will be written to your sheet. https://help.appsheet.com/en/articles/961507-app-formulas-and-initial-values

Hi Tony,

So I’ve given this a go and it’s sorted of worked but not!!

I’ve created a virtual column called VC_Current_Checked_In_Mileage in the parent table Vehicles. This is a ref column and it correctly references the child table called VehicleCheck.

This is the app formula I’ve added to it. Reg Number exists in both parent and child table. It is a unique key in the parent table but not unique in VehicleCheck as each vehicle can have many vehicle check records.

maxrow(“VehicleCheck”,“Mileage In”,([Reg Number]=[Reg Number]))

And this shows the following expansion of what that equates to. I don’t know what it means by a randomly chosen value. I thought it was specifically looking for a row in the child table where the Reg Numbers match and then finding the max value of Mileage In

One randomly chosen value from this list ( …

The list of values of column ‘RecordID’

…from rows of table ‘VehicleCheck’

…where this condition is true: (ALL these statements are true:

…1: (The value of column ‘Reg Number’) is equal to (The value of column ‘Reg Number’)

…2: (The value of column ‘Mileage In’) is equal to (MAX( …The list of values of column ‘Mileage In’ …from rows of table ‘VehicleCheck’

…where this condition is true: ((The value of column ‘Reg Number’) is equal to (The value of column ‘Reg Number’))))))

So the bizarre thing is that a value is being returned to VC_Current_Checked_In_Mileage – but, it’s the value of a completely different column in the child table – column name Status which shows whether the vehicle is being checked in or out.

Maybe need to have [_THISROW] like:

maxrow(“VehicleCheck”,“Mileage In”,([_THISROW].[Reg Number]=[Reg Number]))

Hi Heru. Have tried your suggestion and it’s produced the same result

tony1
Participant V

It looks like your formula is calculating a ref value (hence the little arrow to the right of your yellow circle). If you click on that arrow, does it take you to the correct row?

I suspect the reason it’s showing the status column (“Checked In at FP”) because that’s tagged as the row label for that table.

Hi Tony. Yes, you’re correct on both counts. I’m not sure why I had that column set as a label so I’ve removed that and now the value being returned is the Record ID. So the row being returned is correct, but how do I actually get the value held within the Mileage In column rather than the Record ID?

@David_Jones You should be able to use a dereference expression. So [Your Ref Column].[Mileage Column] should do the trick (maybe put it in another virtual column).

dsajones
Participant I

Hi.  I'm reopening this topic as it still doesn't work exactly how I need it to.

The values are all correct now.  So when I edit a vehicle record, the current mileage column is correctly populated with the value of the most recent check-in record.

The problem is that this only happens when the vehicle record is edited.  So if someone is just viewing the vehicle record they see the current mileage record from the last time that record was edited.

The same thing is happening in another app where I store Membership records.  Master table is the member record and I have two child tables - subscriptions and training records.

Here's an example of what a Membership record could look like and the subscriptions and training records tables:

MEMBERSHIP
Member Name - David Jones
Member ID - 209
Date Latest Subscriptions Paid - 24/11/2021
Date Latest Driving Assessment - 15/08/2020

TRAINING RECORDS

RECORD IDMember IDDriving Assessment Date
120915/08/2020
220923/08/2022

SUBSCRIPTIONS RECORDS

RECORD IDMember IDSubs Paid Date
120924/11/2021
220924/11/2022

As you can see from this example, the Membership record, as viewed, has not got the most recent dates for subscriptions paid or driving assessment date.  If I edit the record then everything works and you would then see 24/11/2022 for latest subs paid and 23/08/2022 for latest driving assessment.

The question is - when I add/edit/delete a record in one of the child records, how do I force the relevant column in the master table to be updated?

Consider creating a bot that responds to changes to the child table by updating the corresponding parent entry.

Top Labels in this Space