Need to look up fields based on several criteria

[Jul]

Hi Guys,

I am new to file maker and hope to get some answers from you. I've been reading through thte forum but couldn't find anything that would help.

Here is my problem:

I have two related files. The relationship is based on the phone number. One file has item_id, customer_phone_number, phone_type (4 types possible from the value list - home, cell, work, other), salesperson_name. The records in another file are imported from the Excel spreadsheet - it is only phone number. After import, I need to look up information for these imported phones. The problem is that I need information only for home phone and if this one doesn't exist, for cell phone (not for the rest phones) and when i do look-up it shows the first value it encounters. How do I look-up only those ones that phones that are home or cell? Any suggestions? Thx

[Ugo DI LUCA]

Use a calculation from your phone_type field in the related file and use it for the look-up.

May be

c_lookupPhone = Case(not Patterncount(Phone_Type, "HomePhone") and not Patterncount(Phone_Type, "CellPhone"),"",Phone_Number)

[Vaughan]

Phone numbers will not make a good key for a relationship. What happens when the number changes?

Create an auto-entered serial number for the key.

[Jul]

Ugo,

thx, but I need to look up for Cell Phone only if home phone does npt exist. Any help?

Yuliya

[Ugo DI LUCA]

You mean you have 4 fields for each phone types ?

[Jul]

Sorry, I incorrectly described my problem. I figured out deal with phone number types. What I need is the following: I import phone numbers and i need to look them up in the items file.

Each item has ID, cust_name, phone_number and a type. ID is unique, cust_name and phone_number can be the same for several items and type is a value list: value1, valu2, value3, value4. I need to look up cust_name for each imported phone number in the second file and the type has to be of value1. If it does not exist then I am lloking for cust_name with the same phone number abd type of value3, but not value 2 or value4. Hopefully, my explanation is better now. Thx for your help,

Yuliya