Forum Discussion

TylerParker's avatar
TylerParker
Qrew Assistant Captain
7 years ago

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...
APIs and custom code
App builders
Ask about Quickbase functionality
TylerParker's avatar
TylerParker
Qrew Assistant Captain
7 years ago
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.

KevinSlider's avatar
KevinSlider
Qrew Trainee
7 years ago
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. :)