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

Are table A and B related?

They're in the same app but I'm not sure exactly which way to construct the relationship. 

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?

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.

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.

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.

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.