9 Replies Latest reply on Apr 26, 2012 12:33 PM by bumper

    Multiway Relationship -- How to Structure AKA's

    alas

      I have a situation where there is a name which will be the "primary" name, but there are also AKAs for that primary name (other related names as the primary name may be sometimes referred). So let's say the primary name is Michael, but there are related akas of Mike, Mikey, Miguel, etc.

       

      I would like to setup a table/relationship where if someone is looking for one name, the related names can be found as well. But where it is confusing me is if I want the related names to be related to each other as well.

       

      So I can figure out how to have a record for Michael with related aka's of Mike, Mikey, Miguel, etc.


      But what about having Mike be related to Mikey, Miguel, etc., and not just Michael.

       

      Do I have to have separate records for all of the permutations and combinations?

       

      Is there a better way to think about/approach this?

       

      So to clarify the usage, I want to assign to a record one of the predefined Primary Names. But if someone is doing a search for a name and they put in an AKA, I want it to find the Pimary name as well as all of the related AKAs.

       

      Thoughts would be greatly appreciated.

        • 1. Re: Multiway Relationship -- How to Structure AKA's
          bumper

          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.

          • 3. Re: Multiway Relationship -- How to Structure AKA's
            alas

            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?

            • 4. Re: Multiway Relationship -- How to Structure AKA's
              bumper

              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

              • 5. Re: Multiway Relationship -- How to Structure AKA's
                alas

                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.

                 

                Thoughts? 

                 

                Thanks.

                 

                A

                • 6. Re: Multiway Relationship -- How to Structure AKA's
                  AppNomad

                  Hope you look for phonetic search.

                   

                  If it is, then Google "phonetic search filemaker".

                  • 7. Re: Multiway Relationship -- How to Structure AKA's
                    IT_User

                    Hello A,

                    The calc field used in the relation won't store, I think.

                    Index Field 10.06.04 AM.pngIndex Field.png

                     

                    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.

                    • 8. Re: Multiway Relationship -- How to Structure AKA's
                      timwhisenant

                      Hi Alas,

                       

                      Are you looking for something similar to this.......

                       

                       

                       

                      HTH,

                      Tim

                      • 9. Re: Multiway Relationship -- How to Structure AKA's
                        bumper

                        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.