Lookup and fill field values based on the content of another table
I have two tables: Table A holds records for daily delivery routes, two of its fields are the [Driver Name] and [Van ID]. Table B holds records of every stop completed on all daily routes, it al...
Hey Slider, if you see this, let me know if you have any ideas for me here. I've got this running well, but there are two cases where I'm losing data:
I now have Tables A,B,C
Table A: Automation sends [Date], [Name], [Van ID], and [Date : Van ID] over to Table B whenever records are added or modified, but only when [Name] or [Van ID] is changed.
Screenshot of example record in Table A:
Automation screenshot:
Table B: I made the key field for this table the [Date : Van ID] field and use it as a holding pen for Table C to reference via a relationship.
Screenshot of example record in Table B:
Table C: A button click pulls all van IDs used (on a date chosen by the user) from our fleet tracking system. A formula combines the [Date] that was selected and the [Van ID] that was returned via API. A relationship to Table B compares this field to the [Date : Van ID] field in Table B to lookup the [Name] of the driver.
Screenshot of example records from Table C:
This always works if the record is created correctly the first time. _________________________________________________________________
However, if someone goes back to modify the record and change the [Name] in a record in Table A, there is no mechanism to update the record that has already been transferred to Table B related to the same driver, because the [Date] or [Van ID] will still combine to equal the same concat key. Thus, this newly edited record is rejected during the automation since the concat key value is not unique.
Attempt 1: Joe, Van 1, 10/23/18 *accepted* ~~Joe calls out sick~~ Attempt 2: Jack, Van 1, 10/23/18 *rejected*
Likewise, if someone changes the [Van ID] in a record in Table A, then the [Date : Van ID] will be different, but the same [Name] will still show for the initially labeled combination of Date/Van.
Attempt 1: Joe, Van 1, 10/23/18 *accepted* ~~Joe changes vans the morning of~~ Attempt 2: Joe, Van 2, 10/23/18 *accepted* But now Joe shows up driving 2 vans that day
Thank you so much in advance for any advice! I'm hitting a mental block. I think having the automation set to run whenever Van ID or employee name change may be a mistake. I'm also thinking that having some mechanism to delete the previously copied [Date : Van ID] value if not unique, instead of rejecting the record copy, could help. But I don't know if that's even possible natively.
I may be asking something you already tried, but perhaps you need 2 automations. 1 for add, and 1 for edit. You can ADD child records, or MODIFY child records as an action in automations. If you are trying to modify child records when they exist, you would need 2 automations, and not necessarily divided by ADD and EDIT on the conditions....
You may need to use ADD actions when table a records are ADDED, or when MODIFIED and CHILD records do not exist (easily identified via summary field).
conditions would be: when record is added or modified, certain fields are edited, and these conditions are true- summary field=0, then add records via automation.
the additional automation conditions would be: when record is modified and summary field>0, then update records via automation.
