Update Table B when Row in Table A changes

[DaveWillett]

Hi

Table A (MobileEstimateCustomers) has a column called [Labour Rate]

Table B (Ticksheet) has a column called [Labour Rate] with a formula of: 

LOOKUP([_THISROW].[JobID],"MobileEstimateCustomers","JobID","Labour Rate")

If I set [Labour Rate] in Table A to 50 , when Table B has a new row added it pulls across the [Labour Rate] 50 from Table A, all good so far.

If I change [Labour Rate] in Table A to 25, [Labour Rate] in Table B stays at 50 (obviously a new row would use 25).

I've looked at actions to try to update table B but can't find a way to update the [Labour Rate]. There could be numerous rows in Table B to update.

Does anyone have a solution which would work?

Kind Regards

[Aurelien]

Hi @DaveWillett 

You may benefit from using the action "action on a set of rows", with an appropriate filter expression that will select the rows you need to update in your table B.

If you need permanent "correct" calculation, I recommend using virtual columns and references.

this is similar to this:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Action-change-status-from-one-table-to-another/...

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Change-values-on-other-table/m-p/266391

[DaveWillett]

Thanks.

So I could change the formula from:

LOOKUP([_THISROW].[JobID],"MobileEstimateCustomers","JobID","Labour Rate")

to

[MobileEstimateCustomers].[Labour Rate]

But would that not change every row visited where I wouldn't want the rate to change?

or how would I construct the formula to only change all rows where JobID = JobID ?

Cheers

[Aurelien]

Hi @DaveWillett 

First thing to do, indeed.

For your need, you should have something like that:

You reference action could be to set the value of your column "yourColumnToUpdate" with:

[MyVirtualColumnWithTheCorrectResult]

But I would go directly with the virtual column.