Greetings all.

I've been poking around the forums for a while, and haven't seen anything like this. I'm new to Filemaker, hope this isn't a dumb question. Let me know if this has been talked about elsewhere here.

I'm building a simple call tracking database, to keep track of the phone calls our customer service folks receive. So far it's set up with a database of customer info (name, address, phone, etc) a database of call activity (name, phone#, product used, problem, resolution, etc.) and a product database.

The relationship between activity and customer is done through phone#. If the entered phone number is in the customer database, the relationship fills in the customer name and email address. A portal is also then filled with past activity that matches the phone#.

If not it creates a new record in the customer database.

I'd like to have the relationship work for any one of the three (phone, email, name). That is, if the email is entered, it fills in the phone and name, or if name is entered, the email and phone are completed.

Thanks much,

Mark

Welcome Mark!

Each of those three (Phone, Email, and Name) are not very good key fields. Names, phone numbers, and email addresses all change, and there could be duplicate names or phone numbers. If there are duplicates, a lookup will just pull in data from the first match.

You could use a global Phone, Email, and Name field to get the right customer by defining a relationship between each (Call::gPhone=Customer::Phone, Call::gEmail=Customer::Email, etc.) showing a different portal for each relationship, then having the user click a button on the portal row, which would set the Customer ID. The Phone, Email, and Name fields would all be looked up by a different relationship based on Customer ID.

Thanks Mike.

That should work.

I'm feeling pretty dense on the concept of globals though. I understand what a global field is (always the same value regardless of record), but can't figure out how I can make it work for finding matching records.

Thanks again for your help.

Mark

Globals can be used as part of a key in a relationship, but only from the file you are relating from, not on the match side of the relationship. Globals are good to use in your case because you only need the relationship temporarily, and they don't take space like text or number fields. The other nice thing about globals, is each user in a multi-user system has their own instance of the global fields.

The match side of these relationships must be regular fields, like text, number, calculation, date, or time.