AnsweredAssumed Answered

Join Table

Question asked by MarkHarrison on Apr 9, 2014
Latest reply on Apr 15, 2014 by philmodjunk

Title

Join Table & viewing data from other tables

Post

     I am creating a database solution in which I wish to join a table of keyword terms (to describe photos) with more general subjects to which those terms might belong. Any given keyword could relate to multiple subjects, and any given subject will have multiple keywords that are related to it.

     I am having a few problems which may all be related or disconnected. First, The knowledge base article (http://help.filemaker.com/app/answers/detail/a_id/9922/kw/9922) on join tables says that when a record is entered into either of the tables being joined, a new record should be formed in the join table. The way I am reading it, this is supposed to happen automatically, but creating records in either table is not creating a record in the join table.

     Second, unlike in my original keyword table where I can enter a an unique ID number for a related synonym or parent keyword and have the name of the term appear next to it, that isn't happening with the subjects. I enter the keyword control ID and the field I created for the term name to appear remains empty. As far as I can see, I have created the same kind of relationship between tables. For parent keywords, i had use to self-joins, to I have multiple instances of the keywords table, but for the non-preferred synonyms, I just joined directly to the original keyword table. In any case, it works for both parent keywords and for non-preferred synonyms.

     Finally, I noticed that in the subject table layout, the modification date is not appearing, though the original entry date does appear. Again, I can't see how the modification date field is set up any differently than it is in any of my other tables where it works.

     Questions:

     1. Am I seeing evidence of corruption somewhere?

     2. Is there something more I need to do in order to get the "subject-join-keyword" table to populate when I enter records into the keyword or subject tables?

     3. Since I have a join table in between two tables, do I need to have all fields related to a record in either of the other tables, that I wish to show data for, in the join table? In the non-preferred synonyms table, I can just type the keyword control ID (primary key) and the field that I have placed in the layout that shows data from the other table just populates automatically, even though I don't have a field with that name in the keyword table, so I wouldn't think so.

     Please see entries below for screenshots. I'll present the relationships graph here first. You will see a couple of stray tables that will not be related, but just used to store other info. There is also a Keywords 4 instance of the Keywords table. I tried to use this to make it work instead of joining to the original keyword table. That didn't make any difference.

Relationships_Graph.jpg

Outcomes