Lookup and fill field values based on the content of another table

  • 1
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered
  • (Edited)
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 also has the fields [Driver Name] and [Van ID].

The stop records in Table B are imported via an API call to a platform which records GPS tracking data for each van. The [Van ID] is obtained with the call, but the [Driver Name] cannot be. 

What I want to do is for every record in Table B, where the [Van ID] = "123", for example, I want to lookup in Table A which delivery route had [Van ID] = "123" and find the associated driver name, and show it in the Table B record(s).

Has anyone had any experience with a problem like this? Thanks
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb

Posted 2 months ago

  • 1
  • 1
Photo of Slider

Slider

  • 1,326 Points 1k badge 2x thumb
Are table A and B related?
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
They're in the same app but I'm not sure exactly which way to construct the relationship. 
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
One additional note, is that I would like to make this conditional upon the value in a [Date] field as well, since drivers change vans regularly. Is this something that would require advanced coding or is this possible with native functions?
Photo of Slider

Slider

  • 1,326 Points 1k badge 2x thumb
Without much detail on the app architecture, I cannot offer much help.

It does sound like you have the data available to relate the tables - but that would require a key field change, which may not be a viable option.
table A - Date - Van
table B - Date - Van 

If you could relate Table A records to child Table B records via the date and Van id - you could look up the driver easily. 

If you cannot add to the API that writes the records to relate to Table A, perhaps you can create a Table to Table import that will merge on a custom Date/Van key. This will write the RID of table A to any records that match the key in table B in the reference field of the relationship. You can trigger this import manually, or via an automation. Maybe a daily automation to run the import would be sufficient, rather than a triggered event. 

Let me know if this helps.
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
Thanks a lot Slider, let me give you some more context about the relevant tables/fields.

Table A - "Routes" - a list of all routes run across all dates
Fields: [Date]-date, [Driver Name]-lookup from another table "Employees", [Van ID]-lookup from another table "Van Inventory", [Route ID], and then various metrics related fields
Key field: [Route ID] (renamed from default "Record ID#")

Table B - "Stops" - a list of all stops for all routes across all dates
Fields: [Date], [Time], [Address], [Driver Name], [Van ID], [Record ID#]
Key field: [Record ID#]

I really like your idea here: "perhaps you can create a Table to Table import that will merge on a custom Date/Van key. This will write the RID of table A to any records that match the key in table B in the reference field of the relationship. You can trigger this import manually, or via an automation. Maybe a daily automation to run the import would be sufficient, rather than a triggered event."

I certainly can change the key field in Table A because a van is used only once per day, making the combination of [Date]/[Van ID] unique with every entry. But how would I go about using a combination of fields for a key field?

I found this comment on a related question: "You also mention in your title 'How can I create a table with two "key" fields?' You can't have multiple keys but if there was a need for two keys combinations to be unique you can just do the standard technique of concatenating your "keys" together with some unique delimiter such as a colon:

[key1] & ":" & [key2]"

If you have time, I would like to invite you to my app to take a look if the preceding info is lacking. In any case thank you so far, it definitely helps.

(Edited)
Photo of Slider

Slider

  • 1,322 Points 1k badge 2x thumb
looks like we cannot use the Import method, as the merge key must be unique in table b, and we cannot make it unique for this use case. :(

However, if you are open to changing the Key for Routes, we would have to know a couple things, like how is the data entered? does the data change over time? 

The data in question is the Date and Van ID.
1. Create 2 formula fields that concatenate the Date and Van ID. (Concat Formula, Concat Key)
2. Change 1 formula field to a Text field type from a text formula field, this will set all legacy data to the new value.  (Concat Key)
3. Set Concat Key to be Unique and check all records. If you have any records with blank Date or Van ID, this will create non unique instances. fix those records until you are able to set the concat key to be unique.
4. Change the key field of the table to the Concat Key
5. Depending on how the data in Table A is entered
    a. Record by Record via form - set form rule to set the value of the Concat KEY from the value of the Concat Formula on Save. 
    b. Via import - create concat column and import into the Concat KEY field.
6. Create the same Concat formula in table B.
7. Create a relationship from A to B, and before saving the relationship, set the Reference field in table B to the Concat formula. Lookup the value for the driver in table B. 

This should work, but you need to have the data entry required for date and van id, that data must also be unique for all records to set as the key.  You need a mechanism in step 5 that will set the value of the key moving forward.

That is enough steps to get someone confused, so let me know if you need help with any of the steps. I would think your challenges will be step 3 and 5. :)
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
Slider, I had to make a transfer table in between Table's A and B to accommodate to obstacles in our workflow that I forgot to account for, but it worked like a charm! Brilliant solution. Thank you so much.
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
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.


(Edited)
Photo of Slider

Slider

  • 1,322 Points 1k badge 2x thumb
Thanks for thinking of me!

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.


Let me know if that helps.
Photo of Tyler Parker

Tyler Parker

  • 714 Points 500 badge 2x thumb
I didn't use the summary method, but you helped me think differently about arranging multiple automations to work on the same task. It looks like I solved both cases, after testing.

For anyone else with a similar issue I had to employ 4 automations, 
1. transfer from table A -> B when added under certain conditions,
2. to delete and then re-add if driver name changed (if the concat formula [Date : Van ID] in each table (A & B) matched,
3. to delete and then re-add if Van ID changed (if the concat formula [Date : Driver Name] in each table (A & B) matched,
4. to simply delete records in table B if they are deleted in table A (if the concat formula [Date : Van ID] in each table (A & B) matched.

The delete, then re-add combo is what saved the day

Thanks again for the inspiration