Lookups question (I think)

[Julia]

I used to work with Filemaker all the time, but haven't in quite a few years, so I'm a little (okay, a LOT) rusty. If there is an answer to this question that can be found by doing a "search," please let me know (but I did try that first, and was quickly overwhelmed....)

Anyway...

I have a fairly simple database project set up for a small retail business. They have three files: inventory, sales receipts, and customers. We want to use the customer's telephone number as the look-up for the inventory and sales receipts databases (using the customers database as the value list). For an existing customer, when you type in the telephone number, it will populate the corresponding fields (name, address, customer no.) in the particular form.

However, there are some customers in the customers file with a home telephone number but no work number, some with a work number but no home phone, some with both, some with none. Is there a way to have the field look up first, the home telephone number, then, if doesn't find it, the work number?

Let me know if I haven't explained this properly (my mind is a little fried...).

Thanks so much in advance.

Julia

[-Queue-]

What happens when one of the phone numbers changes? You'll want to use a unique, unchanging number to identify your customers and use that to pull their related information into your inventory and sales receipts files.

Create a customer id field and populate your existing records with unique numbers. (Have a look around here for articles relating to unique record ids.) Then when a new customer is created, have some sort of auto-entry or scripting process to give that customer the next unique id number. Have another field set in the process that is as simple as a number starting from 1, that will become the Client ID that the user sees. In the related files allow the user to select a number from the existing client ids (or enter it themselves) and use a relationship based on it to enter the remaining client information.

Using something modifiable and non-unique like a phone number or address is never a good idea. When one of your customers moves or changes numbers, you update the information in your customers database and voila, you lose all existing connections to that customer in your other files.

[Julia]

Thanks for your response. Our customers will not necessarily know their customer number, but they will always know their phone number (we've been through this). If we can't find them by telephone number, we'll ask their last telephone number. If we still can't find them, we'll look up under last name.

Original question still exists.

Thanks.

[-Queue-]

The customers need not know their number. Have your users do a find by customer name or telephone number, get the correct customer id, and enter that in the related file. You can 'look up' the information by whatever you want, but you'll only want your related files to do 'lookups' based on their unique customer id.

[Julia]

The users will be out in the field (at shows and conferences and the like) and need to do these lookups quickly -- does your method require writing down or remembering a customer id? 'cause that's not going to be possible. I'm trying to make this as easy as I can for them.

In your scenario, do I have to have them do a find three times? (e.g., if it's not listed under home tel no., then do a find under work tel no., if not there, then do a find under last name)

I'm probably not looking at this distinctly, but it seems like I'm *adding* time to the process, instead of streamlining it, no?

Thanks.

[-Queue-]

You don't have to do three finds when one find with three requests will suffice. One way is to attach a script to your find button that checks If Status(CurrentMode) = 1, then add another request. Then every time your users click Find, it adds a new request onto the original one.

Another option is to create a value list with the customer name and id fields (or a concatenated field of the two) in the customer file, then create value lists in related files that use the value list from the customer file. Format the field in the related files as a list that uses the value list. Attach a script to the field that goes to the field, pauses for one second, and then sets the customer ID field with the selected customer. (This pause continues indefinitely for 'lists' until the user clicks out of the record or presses Enter.) The easiest way to do this would be using the RightWords function.

Another option is to use the multiple find requests, and then use a button that captures the current customer id and creates a new record in the desired related file and auto-fills the customer id in the new record, which causes the lookup for the remaining fields.

And another way would be to use a portal. The user could enter the first few letters of a customer's name, for example, and using a relationship

to a calculation in the customer file (which indexes the first three letters of the client name, for example), having the portal display relevant customers and then have the user click on one of the rows to select that customer and auto-fill the information.

There are several ways to accomplish this. I'd suggest looking around here and searching for more ideas that might fit your needs better.

[BobWeaver]

To find a home or work phone number with a single find request, make a calculated field with the formula:

HomePhone & " " & WorkPhone

Then just enter either number into this field when you do a find.

[borzhd]

Julia

I know what you are trying to do. A random number is the way to go. Use it as an identifier only. For the phone number it's simple. Make the customer Id that you find on with a calc. If is empty HomePhone WorkPhone, HomePhone. Syntax is not correct but it's that simple.

john