Linking data on multiple tables

[Elisa_James]

I want to lookup a record in my contracts table based on 2 fields in my invoices table.

In our example we have a contract with the client, so I have a table with the client name and contract start date and end date. A client can have more than one contract but the dates do not overlap.

When I receive an invoice related to delivering the contract I need to determine which contract the invoice belongs to. The invoice has a client name and a invoice date.

I want to automatically show the correct contract in the invoices table.

How can I use the client name and invoice date to determine the correct contract from the contracts table?

[Andrew_Johnson1]

I am assuming the client names are unique.
If not you would want to do ensure this happens.
Airtable provides you with a Autonumber field which would be helpful in creating the unique client names

After that I would suggest that you break down your table containing the client name and contract date into 2 different tables.

1.Client Table
2.Contract Table

Use the linked record feature of Airtable to create a relation between the Client and Contract table.

Now add in another table called Invoices.
Associate the Invoices table with the Contract table now.

After that you can use a lookup field to show the correct contract on the Invoices or Client Table.