2 Replies Latest reply on Apr 23, 2010 11:04 AM by ratio3

    Setting relationship from non-related field?



      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.


      Any idea???