Have a look at the Lookup() function: it can be nested.
So you're actually going into the phone records and adding the client account number and name to each of the phone records?
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.
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.
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.
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.