7 Replies Latest reply on Feb 13, 2013 3:37 PM by philmodjunk

    Woes of a Common Table and its derivative Value List(s)

    Territan

      Title

      Woes of a Common Table and its derivative Value List(s)

      Post

           Hello folks. Just upgraded to 12, and like any child with a shiny hammer I'm pounding on every nail I can find. And one of them is pounding back.

           The table setup I've been trying to build includes four tables with a special and painful relationship:

             
      •           The Tags table only has two fields: a serialized number for id and a text string.
      •      
      •           The three other tables, GenelineTech, and Interface, each have three fields Tag1Tag2, and Tag3, which need to have a relationship wtih the Tags field.
      •      
      •           The three TagX fields within any given record should all refer to different Tags, but any given Tag can be referenced by any number of records. I specifically want the Tags table to contain no repeats.
      •      
      •           Ideally, I'd like to compile a canonical list of tags within Tags, to use as a dictionary and reference, and to search for all items that reference a specific Tag.

           And this has been frustrating me. For instance, there are a set of fourteen Tags that will be common among a lot of those records, but selecting one of those from the list will either attempt to create a duplicate record in Tags or, if I have "no duplicates" checked in the table's definition, it prevents me from moving focus off of that record.

           Can anyone spare advice for someone who feels reasonably competent with Filemaker, but still apparently has a lot to learn? If I'm not explaining this enough, please ask questions too.

        • 1. Re: Woes of a Common Table and its derivative Value List(s)
          philmodjunk

               Please describe the layout where you are encountering this issue.

               The Tag1, Tag2... fields should be formatted with your value list. What you describe sounds like you have placed the related field from the tags table on your layout and have formatted it to use the value list...

               Also, given three different tag fields in the same record, you may want to set up a related table that matches, for example, a record in GeneLine to a Tag record. This makes for a simpler relationship as well as the option to have more than just three links to Tag should that be needed without having to make design changes to your database.

               Geneline----<Gene_Tag>------Tags
               Geneline::__pkGeneLineID = Gene_Tag::_fkGeneLineID
               Tags::__pkTagID = Gene_Tag::_fkTagID

               See the first post of this thread for an explanation of my notation: Common Forum Relationship and Field Notations Explained

          • 2. Re: Woes of a Common Table and its derivative Value List(s)
            Territan

                 (Common notation? Handy.)

                 First, please forgive the lack of Hungarian naming, as this really is an informal project. This early in building this thing might be a better time to fix it than later.

                 Second, it's a quirk of the system I'm working with that most anything that refers to tags will refer to exactly three tags, and the ordering is kind of important, so that's why I created a FK field for each Tag in a related record. I did this at first...

                 Geneline----Tags
                 Geneline::__fkTag1ID = Tags::__pkTagID
                 Geneline::__fkTag2ID = Tags::__pkTagID
                 Geneline::__fkTag3ID = Tags::__pkTagID

                 ...but you can guess what happened as a result: When I set one of the tags in the Geneline record, they all changed to the exact same thing, which was Not What I Wanted. The next try looked like this, and here's where I go off-road with the notation scheme:

                 Geneline>----Tags[GeneTag1]
                 Geneline>----Tags[GeneTag2]
                 Geneline>----Tags[GeneTag3]
                 Geneline::__fkTag1ID = GeneTag1::__pkTagID
                 Geneline::__fkTag2ID = GeneTag2::__pkTagID
                 Geneline::__fkTag3ID = GeneTag3::__pkTagID

                 ...which got around the problem of every field showing the same thing.

                 I wanted to be able to both select tags from a list and enter new tags on the fly, so I set up the relationships to create records in the Tags table as needed, but no matter how I tried to set up the fields, it ended up creating a new record in Tags for every thing I selected, including anything that was already in that table. (The first fourteen records are very common, and will be used a lot.)

                 Also, to pull that off, I created a Value List containing each tag in the Tags table... which meant to get related tables I needed three copies, for GeneTag1, GeneTag2, and GeneTag3. And I still have to do that for the Interface and Tech tables, which have a very similar structure, which is kind of a Bad Thing.

                 Keeping with the layout of the Geneline table as I'd ultimately like to have it, I'm not quite sure how to implement what you're suggesting without using multiple foreign key tables. There would ultimately be three references to the Gene_Tag table, one for each tag field in the Geneline record, and I'm not sure what the Gene_Tag table would point back to. The original Geneline record?

                 Something like this?

                 Geneline>----<Tag_Gene>----Tags
                 Geneline__pkGenelineID = Tag_Gene::__fkGenelineID
                 Tag_Gene::__pkGeneTagID = Geneline::__fkGeneTag1ID
                 Tag_Gene::__pkGeneTagID = Geneline::__fkGeneTag2ID
                 Tag_Gene::__pkGeneTagID = Geneline::__fkGeneTag3ID
                 Tags::__pkTagID = Tag_Gene::__fkTagID

                 Scary that I'm doing this for fun, isn't it?

            • 3. Re: Woes of a Common Table and its derivative Value List(s)
              philmodjunk
                   

                        most anything that refers to tags will refer to exactly three tags, and the ordering is kind of important

                   None of this precludes using a join table like I suggested.

                   

                        I needed three copies, for GeneTag1, GeneTag2, and GeneTag3. And I still have to do that for the Interface and Tech tables, which have a very similar structure, which is kind of a Bad Thing.

                   If by copies, you mean creating different Tutorial: What are Table Occurrences? that all refer to the tag table, then this is not a bad thing, it's the standard way in filemaker to set up multiple relationships between the same two tables and also as a way to better organize your relationships to make them easier to work with. Please note that this does NOT produce multiple tables of the same data, just multiple references to the same table of data.

                   I would use just:

                   Geneline>----<Tag_Gene>----Tags
                   Geneline__pkGenelineID = Tag_Gene::__fkGenelineID

                   A 3 row portal to Tag_Gene can be used to record tags 1, 2 and 3. An additonal field can auto-enter a value--such as an auto-entered serial number to record the order if that proves necessary.

                   And the same join table (tag_gene) can be used with all of your tables. I'd use separate fk fields in the join table for linking to GenelineTech, and Interface, but use the same fk field for linking all of them to an occurrence of Tags.

              • 4. Re: Woes of a Common Table and its derivative Value List(s)
                Territan

                     I think I see what you're doing, and that join table is going to get big and nasty.

                     I started over from scratch, and built the absolute minimum I needed to have trouble. And it turns out that isn't much.

                     Geneline----<Tag_Join>----Tags
                     Geneline::__pkGenelineID = Tag_Join::__fkGenelineID
                     Tags::__pkTagID = Tag_Join::__fkTagID

                     The relationship between Geneline and Tag_Join allows records to be created or deleted in Tag_Join, but not the other way. (If I'm using three and only three tags, the creator part of the relationship shouldn't be necessary.)

                     Likewise, the relationship between Tag_Join and Tags allows records to be created (only) in Tags, but not the other way. Because it's a poor dictionary that deletes words as soon as everyone in the immediate vicinity stops using them.

                     Tags has been preloaded with the first fourteen records, which should see a lot of use. A value list, called TagList, is based on Tags.

                     The Geneline layout consists only of primary key ID and a portal into Tag_Join, which shows the associated text from Tags.

                     I have created a script button, conveniently accessible on the "Geneline" layout, for creating both a new Geneline record and the three associated Tag_Join records. A deletion button isn't necessary because when I delete the Geneline record, the three associated Tag_Join records are deleted too.

                     But I have the same problem I had before: If I select an existing tag from the pull-down list, it's duplicated in the Tags table rather than associating to the existing Tag.

                     Here's a link to that bare minimum that's giving me fits: http://dl.dropbox.com/u/46631048/Freemarket.fmp12

                     The goal is to, for example, select "Flood/Bleeding" as a keyword and have it link to Tags record #4, instead of creating a new #15, #18, or whatever. What am I doing wrong?

                • 5. Re: Woes of a Common Table and its derivative Value List(s)
                  philmodjunk
                       

                            ...and that join table is going to get big and nasty

                       Big? very possibly. Nasty? I don't think so as it's a very simple table and one easily worked with.

                       

                            Likewise, the relationship between Tag_Join and Tags allows records to be created (only) in Tags, but not the other way. Because it's a poor dictionary that deletes words as soon as everyone in the immediate vicinity stops using them.

                       I don't see the purpose for allow creation on that side of that relationship. I also have no idea what you mean by "deletes words as soon as everyone in the immediate vicinity stops using them".

                       

                            I have created a script button, conveniently accessible on the "Geneline" layout, for creating both a new Geneline record and the three associated Tag_Join records.

                       I see no purpose for that script. The related records in Tag_Join can be created on an 'as needed' basis.

                       

                            If I select an existing tag from the pull-down list, it's duplicated in the Tags table rather than associating to the existing Tag.

                       Sounds like you have the wrong field from the wrong table formatted as a drop down list. You should use the foreign key field for tag ID from the Join table not from tags.

                       What we are discussing here is called a "many to many relationship". I have a demo file that illustrates the basic, portal based approach plus some more sophisticated options: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                        

                  • 6. Re: Woes of a Common Table and its derivative Value List(s)
                    Territan
                         

                              I see no purpose for that script. The related records in Tag_Join can be created on an 'as needed' basis.

                         There is no purpose for the "as-needed" basis. Any records in the major tables that need to reference tags will need to reference three and only three tags, no more, no less.

                         The most efficient way to handle this would be to add a script that automatically creates and associates three join table records to the new geneline (or tech or interface) record, but lacking a way to do that, I put the script on a fairly convenient button.

                    • 7. Re: Woes of a Common Table and its derivative Value List(s)
                      philmodjunk
                           

                                There is no purpose for the "as-needed" basis. Any records in the major tables that need to reference tags will need to reference three and only three tags, no more, no less.

                           Yes but that doesn't mean that you need to create them at the same time that you create the parent record. They will be created automatically when you select a TagID in the portal without needing to add this script. Note that I didn't say that it was wrong to do so, just that you don't need to.