3 Replies Latest reply on Oct 31, 2016 4:33 PM by rmapromotions

    2 lookups on same layout


      I am having a little issue and I am sure it's simple.


      Ok, the database is for creating invoices and contact management.


      Table 1 - Contact information, name, address, map, membership information, last contact date, blah, blah,

      Table 2 - Buyers Information

      Table 3 - Sellers Information

      Table 4 - Invoices

      Table 5 - Invoice Data

      Table 5 - Products

      Table 6 - Purchases


      Everybody is a "table 1" Contact

      Everybody could be a Buyer

      Everybody could be a Seller


      Every Invoice needs a Table 2 "buyer" and Table 3 "seller"

      invoice has a portal from Invoice data where a table 5 "product" is a pull down and fills the description and price and does the calculation for final price, totals, tax, shipping, adjustments, perfect writing to the table 5 "invoice data" (for the ability to search any invoice line item and not have to enter the products on a certain line.. ) THANKS RCR.. 


      The problem I am having is... when you create a new record "invoice layout" it has the buyer and the sellers info. at the top.. when i pick the dropdown and choose a buyer it fills in both the buyers and sellers information in both sets of fields. SO.. I created a buyers table and a sellers table. The dropdown works fine now and I can do a lookup on either buyer or seller.. Works GREAT!!  


      I have the lookup pull to the invoice perfectly so an old invoice will never get modified if they update email, phone, product pricing, whatever it is unless a new lookup is performed.


      But how do I add the "contact" to the "buyer" and "seller" tables without having to go to the contacts table, select the "customer ID" and paste to perform the lookup into new records on both tables each time?


      Hope this is enough info.


      I have attached the different layouts and the relationships


      I really want to say THANK YOU!!!! to everyone who helps out here..




        • 1. Re: 2 lookups on same layout

          What contact information are you wanting to copy? And why? Typically, there's no reason to copy information from table to table unless you (1) are preserving a historical record (like, for example, what the person's address was at the time the record was created), or (2) are engaging in denormalization for performance purposes.


          (BTW: Why do we have separate tables for buyers and sellers? I thought they were all contacts?)

          • 2. Re: 2 lookups on same layout

            Unless you are recording info in your buyers table that is only recorded for a buyer and info in the sellers table that is only recorded for a seller, you should eliminate those tables from your solution.


            What you need are two table occurrences of your contacts table, one that links to invoices as a buyer and one that links to invoices as a seller.


            A table occurrence is the "box" found on the relationships tab. You can create two such that both refer to contact as their data source table but one can link to your invoice table's Buyer ID match field and one can match to your invoice table's Seller ID match field.

            1 of 1 people found this helpful
            • 3. Re: 2 lookups on same layout

              You are absolutely correct Sir..  I so appreciate it.


              This piece of the puzzle has been solved.