Associating Records using different fields

[bwakefield01]

I am building a base of vendor records. Each record has a first name, last name, and vendor ID. Ideally, the unique ID would be the primary field. However, when using the "linked records" field in other tables, I have to enter the Vendor ID, which I don't know off the top of my head -- I have to flip over to the other record, look up the ID, flip back and input the ID to get the correct association. Is there a way to use the Last Name in a different table to associate with a record that has a primary field of the Vendor ID?

My current work around is to use =concatenate(first name, last name), which is perfect for the above scenario. However, when I import bills from my accounting software, it imports using the Vendor ID number -- so it's the same problem the other way around. If using the concatenated primary field, I need to know how to associate the vendor ID with the correct record when it imports from my financial software.

I hope this makes sense and thank you in advance for the assist!

[TheTimeSavingCo]

Hmm what if you created a new table that just had the vendor IDs and acted as a junction?  Then you could rollup / lookup the data as needed? 

[bwakefield01]

This sounds interesting, could you elaborate what you mean?

[TheTimeSavingCo]

You'd have a table called "Vendor IDs" where the primary field is the vendor ID.  It'd have linked fields to the "Bills" table and the "Vendors" table

In "Vendors", you'd just need to convert your current "Vendor ID" field into a linked field to the "Vendor IDs" table

When you import bills, you'd import the bill's vendor ID into the linked field to the "Vendor IDs" table

In "Vendor IDs", you'd then create rollup / lookup fields as needed, and then use lookup fields in "Vendors" to pull over whatever you needed to display
---
If you're open to using automations, you could do this without the extra table too actually, just have an automation that'll trigger whenever a record in "Bills" has a vendor ID and is not linked to a "Vendor" record, and it would have a "Find Record" action to find the right "Vendor" record based on the vendor ID, and an "Update Record" action to link it together