Updating a reference table without going into...

(Emma Nixon) #1

Updating a reference table without going into it.

I have two related tables. Trips and Bookings. A trip can have many bookings, but the booking can only have 1 trip.

Example: Miami trip May 2018

  • Booking 1, Mr Smith (reservation for 5)

  • Booking 2, Ms Jones

(reservation for 2)

  • Booking 3, John doe

(reservation for 1)

On the Trips table, I have an app formula that sums headcount based on a field in the bookings table (in this example, total of 5+2+1 = 8 persons).

From what I understand, the app formula will only recompute the value, if I go into the trip open and save it.

So my workflow would be: 1. Create booking 2. Go into to the trip, open and save.

I want to create a behavior that will trigger step two, when I save the booking. Possible?

Thanks in advance.

(Grant Stead) #2

Yes. You can create a “dummy” trigger update column. And “on save” trigger an action that updates every effected row with NOW(). that will force all app formulas to recalculate.

(Emma Nixon) #3

Thanks Grant I was able to do something, but it will update every row in the referenced table (which is a lot).

Can you help me with the syntax on the referenced rows field to just update the relevant row in the Trip table.

(Grant Stead) #4

@Emma_Nixon sure, so basically on booking save, so you need to update is the parent trip. Which you probably have REF in the bookings table [bookingstablerefcollumn] so that should be all you need in the little highlighted part

(Emma Nixon) #5

Thanks, so I changed it to Bookings[Trip Name], and tested it and it updated every row in the bookings (child) table, which was even longer.

I need to update the referenced row in Trip Table (parent). The column that references the trip table is called [Trip Name].

(Grant Stead) #6

@Emma_Nixon Is Bookings[Trip Name] the column that has the legit REF back to the Trips table? If so, try this: =[_THISROW].[Trip Name]

If that doesn’t work, it might want a list… try this: FILTER(TRIPS, [Trip Name]=[_THISROW].[Trip Name])

(Grant Stead) #7

@Emma_Nixon that filter is wrong it should be something like…

FILTER(TRIPS, [Tripkeycolumn]=[_THISROW].[Trip Name])