Scott Hall

OK, I have another question. Is there a way to validate the data in a field as unique, but based on a set of related records in a table? For example, in my Contacts database, I have a separate related table for "Addresses", so that I can store multiple addresses for each contact. The records in the Addresses table are related to the Contact record by a "Client ID" number. Now, for each address record in the Addresses table, I also have a "Type" field to label each address (e.g. Home, Business, etc.). What I am trying to do is make sure that the values in that "Type" field are unique to that particular Client ID. So, Client "A" might have a "Home" and a "Business" address and Client "B" could also have a "Home" address. I would like the behavior to be such that if you tried to enter a new address for Client "A", it would not allow you to use a value that you have already used for that client (in this case, Home or Business). If I turn on unique validation for the field, it applies to all records in the table. So, in this example, only one client could have a "Home" address type. Any ideas? Thanks in advance. Scott

Thanks! This worked beautifully. I created a field in the contact database called "DefaultAddress" and another field in the addresses database called "DefaultAddressID" (a calculated field that created a unique ID based on client ID and the get record ID function). Now, when I click on the default address flag, it stores the "DefaultAddressID" in the contact record. Again, thanks for the help! Scott

I have a contacts database that includes a separate table for addresses. This allows me to have multiple addresses per client. The addresses are all related by the contact's unique ID#. I have also included a simple "flag" checkbox to mark which address is the contact's preferred "default" address. However, I want that "default address" flag to be a unique value to all related records for that contact. So, they would only be able to have one address flagged at any one time. The validation options for making the flag "unique" apply to the whole table, not just the related records for that contact. So, if I use unique validation on the default flag, only one record in the database would be able to be flagged. Two questions: 1) How do I set it up so that only one record (among a set of records with the same ID#) can be flagged with the Default Address checkbox? 2) If that is possible, is there also a way to make it behave, such that, if I flag one record for a contact as "default", that if that customer already has another address flagged as his/her "default", that it will automatically unflag that record and apply the flag only to the current record being flagged? Let me know if this brings up further questions. Thanks in advance for any information. Scott

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

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.

I am experienced with writing FM databases in the pre-FP7 world. I have a client that is upgrading their current database set (made up of 9 related dbs) from 5.5 to 8.0. I have assisted them with a conversion and the solution appears to be functional in 8.0. However, they are interested in doing a complete rewrite, as many of the old database features are non-functional and obsolete. I have never helped someone plan a "from-scratch" solution before. Are there any good roadmaps or plans for how to get from concept to final product? I don't even know where to start with this client, outside of asking him exactly what functionality he wants the database to provide. I'm kind of looking for a basic roadmap of how to get through the planning stages in a well-thought out manner. Thanks in advance for any suggestions, Scott