1 2 Previous Next 17 Replies Latest reply on May 28, 2013 7:50 PM by NeoTekCorp

    Need help creating "related" list



      Need help creating "related" list


           Okay, this should be simple enough but I'm just not coming up with a good way to do it. I want to basically import word lists and then easily compare them.

           Basically, I'd like to import a list of words and all their synonyms and then be able to see which one's share common synonyms. I've got all the words and their synonyms already in an excel file.

           Any ideas on the best way ti execute this. Your experience, ideas and wisdom are much appreciated.



        • 1. Re: Need help creating "related" list

               How is the data organized in your Excel file?

               What you have here is a "many to many self join relationship". A given word record can link to many other word records in the same table.

               There are two ways to set this up:

               1) Use a join table:


               Words::__pkWordID = Synonyms::_fkWordID1
               Words2::__pkWordID = Synonyms::_fkWordID2

               2) Use a multiple value field


               Words::SynonymList = Words2::SynonymList

               Both methods allow you to get the relationship that you need, but while 2) is easier to set up in some cases, 1) offers options for working with your related data that can be awkard to very difficult to do with 2).

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Need help creating "related" list

                 Thanks PhilModJunk,

                 Presently each word is on its own sheet with the first line being the primary word and all the others downward are the synonyms of that word.

                 Here is what I'd ultimately like to be able to do. I have a master list of words and phrases and then a number of words and their synonyms my end goal would be to be able to quickly see where they overlap or are similar. I figure some of this could be with conditional formatting of some kind.

                 Any help or ideas in setting this up would be much appreciated.

                 Thanks again.

            • 3. Re: Need help creating "related" list

                   What I have suggested should work for what you have--though importing the data will be laborious given one sheet for each word.

                   Will it work to have all words, phrases, synonyms in the same table? Or is this a case of setting up a kind of Language dictionary where the word/phrase is in one language and the synonyms are in another? (I've assumed that this is all in one language.)

              • 4. Re: Need help creating "related" list

                     I think it would work in a single table but that is what I'm struggling to figure out creating. Attached is a screenshot from the excel file where I have all the words in a single excel sheet. I've been manually moving the common words into a single row but that is extremely time consuming and tedious - especially since the excel file has over 80,000 lines in it. What do you think?

                • 5. Re: Need help creating "related" list

                       Manually moving common words into a single row should not be necessary.

                       Am I correct that the synonyms in your file are the column headers shown in row 1 from columns BT and on to the right?

                       What is the significance of repeating the word/phrase in these additional columns? If you put just the letter "x" to select a particular column, would that indicate the same thing? (That the column header term is a synonym for that word/phase...)

                       If I have your spreadsheet figured out correctly, this is not the optimum design for import into FileMaker, but it would be possible to import this data into a separate table where a script could then move the data into the needed records in the needed tables to establish the setup that I have previously described here.

                  • 6. Re: Need help creating "related" list

                         Hello PhilModJunk,

                         The words in row #1 are the prime words and the ones below them are the synonyms. Having an "x" to indicate a synonym associated with the prime word above would be fine.

                         As for importing something, again I have what you saw in the screen shot but I also have all the prime words (about 150) in individual sheets with all synonyms below the prime word in a single column. It may be tedious but I'm fine with a more laborious import process. The other variable is a word list that I'm cross referencing to those 150 prime words to see how many are also in that word list. (those are shown in the yellow highlighted rows).

                         Again, so far I've been manually combining common synonyms (ex. breather, breathe in above screenshot) and then deleting the unneeded rows. I've reduced a 100,000+ row file down to 80,000 rows but the end result will be around 35,000 rows when all common synonyms are merged into a single row. At the pace I'm going it'd take me months.

                         So my initial thoughts were I'd make one table with all the "master" words/phrases from a source material. Then make a single table or 150 tables each with the one prime word and related synonyms. Then my challenge is creating a relation/joining those tables to where I can quickly cross-reference or list to see where they share common words. I'd like conditional formatting in that list to show which synonyms are common to that master list. In the excel spreadsheet I have a countif function showing how many of those 150 words have common synonyms. So for instance, in that above sample the synonym "breathe" is common to 15 of those 150 unique prime words.

                         Any thoughts, advice, and/or words of wisdom are extremely appreciated.


                    • 7. Re: Need help creating "related" list

                           I would suggest getting someone to write a script to move the data where it needs to go after you have imported it into a separate table. That would save you many many hours of manually editing the data to get what you need here.

                      • 8. Re: Need help creating "related" list

                             Hello PhilModJunk,

                             I've actually been working a bit on it. What I did (or am in the process of doing) is creating a unique table for each word I need to reference. Then that table has an individual record for each synonym. So once I finish this, how would you suggest I join/link/reference all the tables together to cross-reference and find related words. Also how would I do the conditional formatting to show that the words are also found in a master list (also in its own table). 

                             Thanks for the insight and guidance.

                        • 9. Re: Need help creating "related" list

                               Here is the other screenshot

                          • 10. Re: Need help creating "related" list

                                 Looks like you are confusing field names with field content. I would not name any fields after a specific word or phrase. that would all be data recorded in fields/records in my database.

                            • 11. Re: Need help creating "related" list

                                   Hmmm, I was probably a bit too hasty with that last post, apologies.

                                   As a temp table that matches the spreadsheet, you may need such a table for use as a way to get the data into your database before moving the data elsewhere in your database.

                                   The script needed to do that, however, is a bit more than I care to donate to the public forum here. Perhaps another kind soul will be willing to help you set it up.

                              • 12. Re: Need help creating "related" list

                                     Okay. I've entered all the words, prime as the table and the synonyms as individual records with field name being the same as the prime word (which I could easily change to have them all "synonyms" or something else). I've also included a couple master lists (words/phrases I want to cross-reference to those other words). Again it is the list with records in it being the words relevant to that list.

                                     So in a nutshell I've got about 150 tables (WORD1, WORD2, WORD3 ... WORD150) each with individual records for each synonym word,  as well as a few other tables with master lists (MASTER1, MASTER2, MASTER3) each with individual records for each "master" word. The end goal is to quickly be able to see where all the WORD synonyms overlap and have common synonyms (with a total count of occurences, again like the excell file) and then also perhaps apply some conditional formatting to easily show where those WORD synonyms also are common to the master word lists (again, I did this with the yellow highlighting conditional format in excel). 

                                     I completely understand your not wanting to put in the time & effort for free. Two things. Can you give me a little insight on how I can make all these individual tables "relate" or cross reference to the others to make a cohesive listing (similar to how it is in the excel snapshot I showed). Or let me know how much you would feel comfortable with charging to set this up for me.

                                     Thanks again for the input and advice.

                                • 13. Re: Need help creating "related" list

                                       Why not have ONE table for the main words and ONE table for synonyms? Main words to synonyms is a one to many relationship.

                                  • 14. Re: Need help creating "related" list

                                         Hi Rick,


                                         Interesting. So any input on how I could take what I have now and convert it to that way?

                                         Again, my ultimate goal is to be able to quickly reference all the information and see which synonyms are shared by a number of the main words or are also found in the master word lists as well. Further, I'd like to be able to implement conditional formatting so that when looking at it in list format the synonyms that are common to the master word lists are easily seen as such. Before importing everything into FM Pro 12 I'd done this with a bulky, slow and hard to modify excel file. The records common to the master list were conditional formatted to be highlighted in yellow. See attached screenshot in previous reply for reference.

                                         Any input is greatly appreciated.

                                    1 2 Previous Next