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?