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


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.

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:

1 Like

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.

1 Like

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

1 Like