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
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.
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.)
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?
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.
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.
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.
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.
Here is the other screenshot
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.
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.
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.
Why not have ONE table for the main words and ONE table for synonyms? Main words to synonyms is a one to many relationship.
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.