I have a contact database that has a separate table for phone numbers. This allows us to have multiple phone numbers for each contact. Each record in the phone table has a phone number field, a type (home, business, fax, etc.) field, and an ID key field. The question I have is in displaying the phone numbers in the Contact database. I can easily create a portal to the phone table and show all the related phone numbers for each contact.

However, I would like a little different functionality, but I'm not sure if it is possible. I am envisioning a portal with only one row, showing one phone number. Next to it, I envision a pop-up menu field displaying the type of phone number currently showing in the portal (home, business, fax, etc.). As the user selects the pop-up menu, they could change the type and the portal displaying the phone number would update with that record.

For instance, let's say one contact has 3 phone numbers in the phone table (home, business, fax). The phone number shown in the portal on the Contact page is the home number and the pop-up menu displays "home". If the user selects "business" from the pop-up menu, the number in the portal would change to the business number.

I'm envisioning that the pop-up menu would only contain the values related to that contact. So, if a contact had only 2 numbers, say Home and Cell, that those would be the only selectable values in the pop-up menu. Whereas another contact might show Home, Business, and Business2, if those were the records related to him/her.

Does this make sense? Is it possible? Any ideas are appreciated.

You can show only selected phone just by creating new relationhsip between contacts and phone number tables. The relation contains ID and phone type field (home, business ...)

You dont have to use portal if you want to show only one related record. Placing just a related field is enough to display first row's phone number.

You can see how it is work by downloading example file.

contact.zip

Edited July 15, 200619 yr by Guest
a simple example file is added

Thanks for the reply and the example file. I was able to reproduce the structure and it works great!

However, it has now led me to a follow-up question. I want the user to have full fuctionality to add custom "types" of phone numbers. In other words, I don't want to create a static value list of only certain number of phone types. So, a contact might have Business1, Business2, Home1, etc. as types. The problem comes when the user enters in two phone numbers with the same "type" name, such as "Business". I want to restrict the user from entering a value in the type field that is already in use for that particular contact. So, if there is already a value for "Business" in a contacts related phone numbers, then I want there to be some way to force the user to enter a unique value in that field.

I was considering some way of doing unique validation, but I don't know how to force that to only related records, only to the whole table. Again, maybe this is a simple solution, I just can't figure it out.

Thanks in advance for any help,

Scott