11 Replies Latest reply on Jan 4, 2017 9:35 AM by erolst

    Lookup Field Multiple Tables

    ZoocMan85

      I have a field that is a lookup field the queries a table for customer names etc. The two tables are linked by customer ID. However I have another table with other customer data in it. These tables cannot be merged. How can I make a field on a layout that is a lookup field be able to query two different tables ? Is that possible ?

        • 1. Re: Lookup Field Multiple Tables
          erolst

          ZoocMan85 wrote:

          [...]  a lookup field be able to query two different tables ? Is that possible ?

          Not with a Lookup, but with its younger sibling: the auto-enter calculation.

           

          ZoocMan85 wrote:

          How can I make a field on a layout […]

          That has nothing to do with the field object on the layout ...

          • 2. Re: Lookup Field Multiple Tables
            ZoocMan85

            So we are working with 3 tables here

             

            Active List

            Customers Approved

            Customers Pending

             

            So on Active List We have fields ID  Name, Address, etc

             

            Name and address are lookup fields. They get there data from customers approved via a relationship by customer ID.

             

            Now we have a new table customers pending.

             

            So my plan was to complete the calc would be if customer = approved look up in Customers Approved , Else If Customer = Pending go to customers pending table. How may I accomplish this via auto enter calc Erolst ?erolst

            • 3. Re: Lookup Field Multiple Tables
              erolst

              ZoocMan85 wrote:

               

              So my plan was to complete the calc would be if customer = approved look up in Customers Approved , Else If Customer = Pending go to customers pending table. How may I accomplish this via auto enter calc Erolst ?erolst

              You've already spelled it out:

               

              If ( "approved" ; Approved::name ; "pending" ; Pending::name )

               

              EDIT:

               

              Case (

                statusField = "approved" ;

                  Approved::name ;

                statusField = "pending" ;

                  Pending::name

              )

               

              for which of course you need a relationship to the new Pending table.

              • 4. Re: Lookup Field Multiple Tables
                philmodjunk

                That if function isn't quite right, but it's the right idea

                 

                If ( status field = "approved" ; Approved::name ; Pending::name )

                 

                But you made another categorical statement that I question:

                 

                These tables cannot be merged.

                 

                I see no reason why you can't merge these tables and thus simplify your solution. A field in this table can flag each record as either "approved" or "pending".

                • 5. Re: Lookup Field Multiple Tables
                  erolst

                  philmodjunk wrote:

                  That if function isn't quite right, but it's the right idea

                   

                  If ( status field = "approved" ; Approved::name ; Pending::name )

                  See what happens when I force myself to use If( ) ...  but what about other stati?

                   

                  For a change I'm gonna be explicit and edit my post ...

                  • 6. Re: Lookup Field Multiple Tables
                    philmodjunk
                    but what about other stati?

                     

                    That's what Case is for. Depends on the specifics of this solution whether you need test specifically for "pending" or can just leave it as the "else" result.

                    • 7. Re: Lookup Field Multiple Tables
                      erolst

                      That was a rhetorical q to point out that the If( ) in your example doesn't account for that.

                      • 8. Re: Lookup Field Multiple Tables
                        philmodjunk

                        And I don't see it as Rhetorical as you made a very good point that needed clarification. It's clear to you and me without my added response, but not necessarily to ZookMan85 or others that might encounter this thread while searching for an answer to their own question.

                        • 9. Re: Lookup Field Multiple Tables
                          ZoocMan85

                          They cant be merged because everyday the master customer list is downloaded from a web portal. Before update the main database is wiped to assure no duplicates , then everything is reimported. So if I merged the two, my pending customers would be wiped and id have no record of them.

                          • 10. Re: Lookup Field Multiple Tables
                            philmodjunk

                            That's not your only option.

                             

                            Down load to a staging table. Then merge the data from it into a single "approved" table but with the needed "pending" values in the flag field that I described.

                            • 11. Re: Lookup Field Multiple Tables
                              erolst

                              philmodjunk wrote:

                              others that might encounter this thread while searching for an answer to their own question.

                              Note to self: "never forget that all you write is for post-erity".