1 Reply Latest reply on May 9, 2012 6:41 AM by philmodjunk

    Multiple Lookups in a database


      Multiple Lookups in a database


      I have 2 databases - one customer database - each customer having a unique id, another one, which lists Jobs, however some jobs have 2 customers.  I have field in this database Customer ID (which is the lookup unique ID), and Name 1, Address 1, Phone 1.  Then I have another Customer ID #2 which lookups the same unique ID from the other database), and the fields Name2, Address 2, and so on.  It seems to be  working however when I type in the customer ID it fills ALL fields including the 2nd lookup fields.  Is there a way I can get this working so I can lookup 2 individual customers, (without having to create a 2nd database for 2nd customers.

      I hope people get what I mean, it is hard to explain. 

        • 1. Re: Multiple Lookups in a database

          By "database" do you mean "table"? Are these two tables in the same file?

          A quick fix is to define two relationships between projects and customers so that each set of fields uses a different relationship to look up the data.

          In Manage | Database | Relationships, select the customers table occurrence (the box with this name) and click the duplicate button (Two green plus signs.)

          That enables you to set up these relationships

          Projects::CustomerID1 = Customers::CustomerID

          Projects::CustomerID2 = Customers 2:::CustomerID

          and now your second set of fields can look up from customers 2 instead of customers.

          The long term fix may be to ask yourself if two customers will always be the most that can link to a project. What if you get a project where there are three or more customers? You wouldn't want a design that has to be modified in cases where the number of customers linked to a project increases.

          If this is even a remote possibility, using a "join" table to link customers and projects so that you have a true many to many relationship here will be the better option as it enables you to link any number of customers to any number of projects.