My thoughts would be to have the primary name on the main record in the Contact table, then have a second table just for the AKAs, linked by the ID from the Contact table. One record in the AKA table for each "other" name.
Create a calculation field, AllNames, in the Contact table as follows:
Contact:Name & "¶" & List (AKA::Name)
When you do a find in AllNames you will get all of the names in any record that has that name, be they the primary or AKAs. No need to try and relate all the other names to each other.
Question for you.
I have done what you suggested. So I have a new field on my "primary" table which is the primary name and then all of the related aka's.
However, I cannot index that field. I get an error that says that I am trying to store a field that is not indexed or unrelated.
This is an issue because I would like to use this combined field to create a value list.
Basically, I would like to create a value list that has all of the primary and aka names (all of them for everyone). Is there a way to do this?
My suggestion was to put the given name in a field in the main table, then create another table just for the AKAs related by the primary key of the main table. Then using a calc field in the main table, using the calculation provided create a list of all the names. This field can be used to do a find for any name from the list of all the names.
If you have to have a value list for another reason, then just include the given name with the AKAs in the second table and pull your value list from there.
Sent from my iPhone
I did do what you suggested in the first paragraph. And that works. But I have a second need which is to have a value list with all of hte names (given and Akas) across all of the records in the main table. I thought I could use the new calculated field you suggested. But it won't let me b/c it is not indexed or stored.
is there a way to do both? I want the field that you suggested above (which I now have) but I also want the Value List for another purpose.
The only way I can think of is to put the given names into the aka table with a reference to themselves. And then change your idea above to not combine the given name and the list of akas since it will just be the list of akas (since the given name will now also be an aka). But that seems like an odd way to do it.
Hope you look for phonetic search.
If it is, then Google "phonetic search filemaker".
The calc field used in the relation won't store, I think.
You might have to make the value list off the field in the AKA table, but I don't know how to include all the name unless included in the AKA table.
Please see attached.
Name.fp7.zip 7.3 K
You can also get the same results, a value list of all name across all records, without the extra relationships by creating a text field, populating it with the data from the original calcuations field (tested) via a script trigger (not tested) after any changes, and basing the value list on that field. The new text field does not have to be visable, but a commit record will be necessary to add any new names or changes to the value list.