Unique Entries In Multiple Fields

[gemini9189]

Hello!

I have three phone number fields for each contact entry, but I need the phone numbers as they get entered to search all three fields for a same number. I understand how to search in the same field, but I cannot figure out how to search the other fields.

Thanks.

[Kent Searight]

Have you considered making a separate, related file just to hold phone numbers? This would solve your dilemna quite easily since you'd only be dealing with 1 field.

[gemini9189]

I actually tried that, but my superiors need all phone numbers for one person all together. They are very picky about this so I have to get them to search on all. I actually only need the phone1 field to search the phone1, phone2 and phone3. But it would be better for all of the field to search everywhere.

[Kent Searight]

Hmmm, just to make sure we're both on the same page here...my suggestion for the separate phone file would work as follows:

The Phone file would relate to Contact file via the Contact record's unique ID. The phone records for any given contact could be viewed in a portal on a layout based on the Contact file. This is a fairly common way of handling multiple phone numbers for a given contact. As an added bonus, it greatly facilitates searching over your current method and allows for the concise search that you say would be better in your last sentence.

[gemini9189]

I have been trying all weekend to learn about portals, but I am having no luck - Could you please explain step by step how I can set this up so that my phone1 field will search phone1, phone2, and phone3 fields upon entry?

Thanks

[Vaughan]

The point with the relational design (in this case, for phone) is that you don't need 3 fields: you have one field, but instead have multiple related records.

Usually a phone file would have two fields, one for the phone "type" (home, fax, mobile etc) and the other for the phone "number".

(Bbehind the secenes is a third field to hold the key of the parent record.)

[gemini9189]

Actually I do need 3 fields. Most of the contacts that I am entering have at least 1 number, usually 2 or 3 and this database will contain about 50,000 plus entries. So, I am trying to find a way so that I will know without a doubt wheather or not I have duplicate entries within any of the 3 phone number fields.

[LaRetta]

Hi gemini,

No. You don't need 3 fields ... you need records. As sure as the sun rises, someone will need a FOURTH number. And a fifth.

Your existing structure will cause you heartache yet untold. Your problem with searching multiple fields (and for validating from multiple fields) is just the beginning if you continue down this flat-file path. Take the time to understand why 'related' is so darned powerful and change it NOW. This a perfect situation and is easy to do as well.

Here is a very basic example. I didn't embellish because I wanted you to see how simple it can be. There are many great people on this forum here to help you if you get stuck.

Now ... the demo is basic. There are many avenues of design and I rarely allow creation of records directly in the portal. I have here. And I rarely use field-level validation. I have here. But, as you put this together, these tweaks can be presented to the Forums as individual questions; or the forum archives can be searched for various methods you may wish to consider. This is just a starting point.

customer.zip

[gemini9189]

Sorry for the delay in reply - I have been away. Thank you for the demo, I think that is what I looking for. I am going to study up on related fields and portals and try this again.

Thanks a Million.

Gemini9189

[bruceR]

Laretta, your demo is in Filemaker 7/8, gemini's sig says he's using FileMaker 6.

[gemini9189]

Its okay - I have actually upgraded to version 7. But thanks for the concern. It is much appreciated.