Set Value in one column based on changes in value in another column

[Peter_S1]

Hi,

I’m trying to set a behavior to update Company ID when Company Name is changed.
I have two tables:
Company: lists Company ID and Company Name Drivers: lists driver names, Company Name they work for, and Company ID which references back to the Company table.
What I have done so far:

I have set up an action
a) Data: set the values of some column in this row
b) Set these columns: Company ID (having trouble with the expression here to look up Company Name and return the Company ID from the Company table.

I’ve set up a worklow
a) Target Data: Driver table
b) Update event: updates_only
c) Condition: [_THISROW_BEFORE].[Company Name]<>[_THISROW_AFTER].[Company Name]
d) Set task category to Chang Data and linked the Date Change Action name back to action in step one.

[Marc_Dillon]

I believe you are going about this all wrong.

Presumably your [Company Name] column in Driver table, is a dropdown selection of all existing company names?

You should be having the app user select an option from the [Company ID] column instead. Make this a Ref type column, pointing to Company table, where ID is the Key column, and Name is the Label column. With this setup, the dropdown will look identical, displaying the Name, but storing the ID value.

Then, if you still need to, you can pull the Name value through to the Driver record with a de-reference [Company ID].[Company Name]

Here are some resources for you to learn more:

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

help.appsheet.com

Row Labels

How to determine which columns are prominently displayed in a view.

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

https://www.appsheet.com/samples/This-app-shows-the-difference-between-keys-and-row-labels?appGuidSt...

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

[Marc_Dillon · Accepted solution]

Then also, to solve your issue of forcing values to update (which you shouldn’t even need with the above), when other values in the same record get changed, I would use a combo of Initial Value and a reset_on_edit expression. No Actions or Workflows needed.

[Peter_S1]

Perfect! reset_on_edit works great. Thank you for the tip, very easy.

[JohannesS]

Hi!

Sorry to revive this thread, but I have a similar problem, and the solution doesnt work in my case:

I have made a small app to track my meals. It has two tables: "Meals" and "Food".
The latter table contains the different dishes, including standard measure (e.g. 1 portion, 100 g, 100 ml (number and value stored in different columns) and so on).

I try to compute the consumed kalories (and other values) based on the amount of the respective dish with the following formula in "Initial value":
[_THISROW].[Amount]*
ANY(SELECT(Food[spez.kcal], ([Food] = [_THISROW].[Dish])))/
ANY(SELECT(Food[Standardmeasure], ([Food] = [_THISROW].[Dish])))

When I enter the amount before I the dish, the kalories will be calculated (for this value). If I later change the value of Amount, the kalories are not updated (until I change the dish, then they are re-computed).