Update Column in Master Table After Update in Child Table

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

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?appGuidString=62bcb547-8e35-4f69-a194-4494642b4760 It might be a good starting place.

2 Likes

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

1 Like

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]))

2 Likes

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

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.

2 Likes

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).