11 Replies Latest reply on Apr 15, 2014 9:05 AM by philmodjunk

    Join Table

    MarkHarrison

      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

        • 1. Re: Join Table & viewing data from other tables
          MarkHarrison

               Now the Subjects Table. Please note where the modification date at the bottom does not appear while the the original entry date does.  Please note also where the keyword term field (taken from the keywords table) does not appear. I tried linking to that Keywords 4 instead, no difference.

          • 2. Re: Join Table & viewing data from other tables
            MarkHarrison

                 Here is the join table:

            • 3. Re: Join Table & viewing data from other tables
              MarkHarrison

                   Here is the layout for the keywords table. As you can see, all fields populate here.

              • 4. Re: Join Table & viewing data from other tables
                MarkHarrison

                     And here is the non-preferred synonyms table layout for comparison. Again, all fields populate.

                • 5. Re: Join Table & viewing data from other tables
                  MarkHarrison

                       Here's the subject window with the inspector showing. I don't think it will help much, though. Also, I just tried to create a new layout for the subject table. The results are the same. I also tried to look up information about what I might be missing about populating the join table but didn't find anything that helped me - so far.

                  • 6. Re: Join Table & viewing data from other tables
                    philmodjunk
                         

                              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.

                         This does not happen automatically. Your system has to create that linking record in the join table.

                         

                              Second...

                         This could be due to a lack of a missing join table record given your first statement, but there are other possible issues, but I'd need to know the context in which this is happening. Into what field are you entering/selecting the ID? From what table and on a layout/in a portal based on which table?

                         1. Probably note

                         2. From your first statement that I quoted, you may not be getting the needed linking records in your join table.

                         3. No. Only the ID fields need be set up as match fields in these relationships. (though a relationship that matches only on the actual keyword instead of the ID may have some added functionality for some layouts as you can then use auto-complete on a value list of keywords... Matching by both ID and SubjectName seems unnecessary in your relationship to the join table.

                    • 7. Re: Join Table & viewing data from other tables
                      MarkHarrison

                           Hello, and thank you for your assistance. I've been out most of the day so I haven't been able to work on this much, but I am attaching here a new screenshot of my subject table layout.  It looks sort of like a step forward, but that may be a mistaken notion on my part. I moved all of the descriptive data to a different tab and created two portals, one for the keyword table and the other for the subject_join_key table.

                            

                           I was thinking last night and this morning that I don't want wish to be able to accidentally modify any data in the keyword table. That is the the primary data in this project, around which everything should revolve. I am concerned about either me or someone else being able to accidentally change a keyword control ID or keyword term. I also think it would be counterproductive to create records in the keyword table from a remote layout unless I am able to enter all data for the keyword term. All I really want to be able to do is to take an existing keyword record and link it to one or more subjects and to link one subject to one or more already-existing keywords.

                           With that, in the relationships table, I allowed for data to be entered back and forth between the subject table and the subject_join key table.While I have a layout for the subject_join_key table, it seems to me that the subject layout would be the most convenient place to do the work since I don't want to alter the keyword records and the there is only one other field in the join table that is not from either the subject or keyword tables: a description of the relationship, which I only envision being used where my reasons for connecting a particular subject term and keyword pair might be unclear. This is different from the definition fields in the subject and keyword tables that serve as authority controls: ways to limit the scope of use of a term.

                           As you can see, I removed the keyword term from the left side of the layout and only have a place to enter the keyword control ID with which the subject term is to be associated. The portal is supposed to show all of the keywords with which that subject term is associated. The other portal is supposed to give the record number in the join table so I could look it up (I have the field in the portal set to act as a button to jump to the record if I want to add a description.

                           I set it up this way because I was unsure of two key points: 1) can a portal be used for general data entry (and is that a good route to go)? and; 2) can a single portal incorporate fields from different tables?

                           See next.

                      • 8. Re: Join Table & viewing data from other tables
                        MarkHarrison

                             Continued from previous:

                             As you can see from the screenshot above, I am now getting the data to populate in the portal when I type in a keyword term to the left. In the smaller portal from the join table. In the screenshot below you can see where a portal in the keyword table layout is showing all the subject terms to which the keyword "activities" has been linked. It appears then, that the basic relationships have now been established. However, I obviously haven't yet allowed for multiple keywords to be linked to a subject from the subject table layout, nor do I have multiple join table records being generated. The join table has a blank entry field below the populated field, showing; that the relationship is set up to allow me to add records to the join table from the subject table. (That blank field only appeared after I chose the option to allow records to be added to the join table from the subject table).

                             Based on everything I've said here and in the previous entry, I am hoping that I have established the relationships now, and might be able to set up a single layout where I could 1) create a new subject record and enter the other subject information; 2) create a join record; and 3) display the keyword control ID and keyword term for being joined to the subject without allowing for alteration of the keyword data.

                             Any suggestions about how to most efficiently accomplish those goals would be great. Thanks again for the help provided so far.

                             Side Note: It appears that the modification date in the subject table is working now, but I am still not able to determine why it quit working. The description author field in the keyword table, which quit displaying my name, even though it was still set to automatically populate in the field setup in manage database, adds my name in new records still, but I had to go back and manually tell it to re-populate that field in existing records. It appears that somehow I de-populated those two fields at one point. I have no idea when or how.

                        • 9. Re: Join Table & viewing data from other tables
                          philmodjunk
                               

                                    I don't want wish to be able to accidentally modify any data in the keyword table. That is the the primary data in this project, around which everything should revolve. I am concerned about either me or someone else being able to accidentally change a keyword control ID or keyword term. I also think it would be counterproductive to create records in the keyword table from a remote layout unless I am able to enter all data for the keyword term

                               With the right layout options, you can keep the fields from the Keyword table from being editable on that layout. You can use a button for adding new keywords via a script that takes the user to a layout specifically for entering all the info about that keyword. If desirable, this script can also automatically generate a join table record and link it to the new keyword record and to the current record in Subject Codes.

                               

                                    All I really want to be able to do is to take an existing keyword record and link it to one or more subjects and to link one subject to one or more already-existing keywords.

                               This is easily done via portals to the join table with a value list used to select the "other table record" that you want to link to the current layout record.

                               So from a layout based on keywords, a join table portal can be used to select subjects via formatting the SubjectControlID field from the join table as a value list. (No subject name field should be defined in the join table.) And from a layout based on Subject Codes, the same join table can be set up to select keywords via a value list setup on the KeyControlID field defined in the join table.

                          • 10. Re: Join Table & viewing data from other tables
                            MarkHarrison

                                 So, are you saying to not use a layout for the join table itself, but rather to do all work in it via portals from the other two tables?  What issues are there, if any, with entering data via a portal?

                                 You mentioned being able to block fields from being modifiable via the portal. One could just not include the fields you don't want to be modifiable, but it would be helpful to see the data in some cases. How do you make a field visible in a portal, but not modifiable? I've seen where you can control adding or deleting records, but not making certain fields read-only.

                                 It sounds like your script idea would be ideal because I definitely would like to be able to have entering a new keyword or subject term automatically generate a new record in the join table. Can you explain more about the script? I have never scripted. I know what scripting is, but not how to accomplish it (the language necessary).

                                 If a button took the user to a layout to enter keyword data, would that be a layout separate from the normal layout for the keyword table?

                                 I'm sorry, but these are the issues where I need more specific guidance - at least for being able to find information in print. As you can see, I've not even known exactly what questions I needed to ask, so I couldn't do effect searches in HELP or in the knowledge base.

                            • 11. Re: Join Table & viewing data from other tables
                              philmodjunk
                                   

                                        So, are you saying...

                                   No, but portals to the join table are easier to use and you see things work in the context of one of the two linked tables so it is easier to confirm that you have everything set up correctly. Thus the portal method is simpler for data entry, but often reports based on the join table can be very useful. There are more potential issues with entering data on a layout based on the join table as you have to becareful to link any new table to records in both of the other tables where this happens pretty much automatically when doing data entry in a portal.

                                   

                                        You mentioned being able to block fields

                                   Enter layout mode. select the field. Then clear the "browse mode" check box in the inspector's behavior section.

                                   I can post a script for creating both a new record in the keyword table and also linking it to the current subject table record via a new record in the join table, but if you have never ever created a script, I suggest that you acquire tutorial or training materials that show you how to create and edit scripts.

                                   #From a layout on your "Subject Codes" layout....
                                   #assuming that your relationship only matches by SubjectControlID, not by this field AND the SubjectName field...
                                   Set Variable [ $subjectID ; value: Subject Codes::SubjectControlID ]
                                   go to layout [ "keywords" (Keywords) ]
                                   New Record/Request
                                   Set variable [ $keywordID ; value: Keywords::keyControlID ]
                                   Go to layout ["Subject join Keyword" ; (Subject join keyword) ]
                                   New Record/Request
                                   Set Field [ Subject join Keyword::keycontrolID ; $keywordID ]
                                   Set Field [Subject join Keyword::SubjectControlID ; $SubjectID ]
                                   Go to layout [ // you have a choice of two possible layouts here, see below ]

                                   With this last go to layout step, if you use it to return to the subject codes layout, you will see a new blank row in the portal to the join table. If you have sufficient editable fields from Keywords in the portal row, you can fill in the needed info directly in the portal row.

                                   But since you have already indicated that you did not want these fields to be editable in the portal, go to layout can return the user to a layout based on Keywords where they can fill in the additional info and then click a button to return to the subject codes layout when they are done.

                                   

                                        If a button took the user to a layout to enter keyword data, would that be a layout separate from the normal layout for the keyword table?

                                   Either option works. So there is no need to create a separate layout unless you feel that doing so makes your system work better in some way. You can create as many layouts as you want all based on the same table occurrence so ultimately, this decision is up to you.