5 Replies Latest reply on Dec 11, 2009 5:22 PM by comment_1

    Lookup that will look in 3 different related databases

    jlenberg

      Title

      Lookup that will look in 3 different related databases

      Post

      I have a database of thousands of phone records that are recorded each month. Monthly, I need to identify which of my clients received the call based on their telephone number (key) that is in the record. The results will populate the clients account number and clients name in the proper fields for the call records.

       

      The problem is, I have and must maintain 3 different client databases. Presently, I do a lookup in client_database_1, then, go to the "Define Fields" and change the lookup parameters for the Account Number and Company name fields so the next lookup will be for client_database_2, then I do the same thing for client_database_3.

       

      Is there a way to write a script to do lookups for the same fields in multiple databases? 

        • 1. Re: Lookup that will look in 3 different related databases
          comment_1
             Have a look at the Lookup() function: it can be nested.
          • 2. Re: Lookup that will look in 3 different related databases
            MikeyG79
               So you're actually going into the phone records and adding the client account number and name to each of the phone records?
            • 3. Re: Lookup that will look in 3 different related databases
              philmodjunk
                

              Note also that phone numbers are not as unique as you might expect. I've found that out with a contact manager that I created here where I work. Basically, people change their phone number, another person is assigned the phone number and the first person rarely if ever tells you that they've got a new phone number and that the old one should be changed.

               

              Result:  Two different contact records in your DB with the same phone number.

              • 4. Re: Lookup that will look in 3 different related databases
                jlenberg
                  

                That is correct. I provide 800 numbers to my clients that spell words, such as 1-800-Insurance, 1-800-Cold Air, 1-800-Hotel Res. Each client only receives calls from his designated serving area. For instance, if my client sells insurance in Dallas, he doesn't need to use the Number in Miami, so I program the telephone company switches to direct the call to the assigned client. 

                 

                Each month I receive the call data from the telecom switch which contains 200-250 thousand records, and the only way for me to identify which of my clients received the call is to match the telephone where the call rang, to my client database. I do that with a simple lookup. By basing the lookup on the telephone number, it populates each telephone call with my clients account number and my clients name. I can then create individual reports for my clients that contains all the calls he received.

                 

                My problem is, I have several very large clients that have between 750-1,000 locations that receive calls and my report must contain an identifying Sub-Account number, so all the call detail for each location is shown. I cannot have these large accounts mixed with the other clients, that is why I do a look-up in 3 databases, one for my regular clients and two databases for these many locations.

                 

                I want to be able to have a script that will pull the account number and client name from all 3 database without the need to change the field definitions to make them look up one at a time.

                 

                I hope this is clear.

                 

                For the following response by MikeyG79, I never worry about duplicate phone numbers as I control all the phone numbers.

                 

                I did look at the Lookup function, but to be honest, I could not figure it out. 

                • 5. Re: Lookup that will look in 3 different related databases
                  comment_1
                    

                  The formula =

                   

                  Lookup ( Table 1::Field ; Lookup ( Table 2::Field ; Lookup ( Table 3::Field ) ) )

                  will look for a match in each table in turn, and return the Field value from the first match it finds.

                   

                   


                  Otters2 wrote:

                  I have several very large clients that have between 750-1,000 locations that receive calls and my report must contain an identifying Sub-Account number, so all the call detail for each location is shown. I cannot have these large accounts mixed with the other clients


                   

                   I am not sure that's a necessary conclusion.