Setting relationship from non-related field?
Hello I have a database set up with a few tables...
Products | Invoices | Clients
We run a business with a set group or returning clients.
My problem is this...
I want to relate clients in the Clients table to invoices they have ordered... I would like to relate them by their Client_ID field (unique auto-entered number)
to the also unique Invoice_ID field in the Invoices table. The issue is that it's very inconveinent to have to type in a cryptic number to relate the client... I would ideally like to pick the client by selecting their name from a Value List populated by the Clients::Name field, and then have that information find that client's Clients::Client_ID... and enter it into the current Invoices record...
I tried doing this with a lookup field.. but with that method it seems i would need to also have the Clients::Name related to the invoices record. I only want to relate them by ID number incase for some reason we need to change a clients name or something else.