10 Replies Latest reply on Aug 13, 2009 8:59 AM by TSGal

    I'm missing something about relationships...

    OpenOfficer

      Title

      I'm missing something about relationships...

      Post

      ...of the Filemaker kind. A simple example:

       

      So you have a table. Each record has a field, category_id, that expects a number between 1 and 20.

       

      A second table (categories) has 20 records with 2 fields: id_number and category_name. You relate the category_id in the first table to the id_number field in the second table.

       

      You create a layout with a popup that displays the category_names from the second table. All this works beautifully so far.

       

      Now you go to browse mode and you change the category on one of the records. You are expecting Filemaker to take the id_number in the record with the new category_name and put it in the category_id field in the first table. Instead, it changes the category_name for that id_number in the second table. 

       

      Is this how related fields are supposed to work? I was expecting a little more magic than this.

       

      Obviously I could just use the second table as the source for the drop-down list and store the category names in the first table, but then we're not talking about a relational database anymore. This is a simple example, but I want is to understand how you coax the functionality of a relational database out of Filemaker.

       

       

       

       

       

       

        • 1. Re: I'm missing something about relationships...
          mrvodka
             You should be changing the category number of the first table. Once you do, it will reference the category name in your second table if you put the related name field on your layout based on table1.
          • 2. Re: I'm missing something about relationships...
            OpenOfficer
              

            Well, ok, but is there any way to do that short of writing a script that:

             

            captures the category_name entered using the popup

            doing a find on table 2 using that category_name

            getting the category_id from the found record

            updating table 1 with the new category_id

             

            I mean, I can do that, but somehow I expected something more automatic. Keep in mind this is just a simple example. My database has a number of related fields - some related via a link table (table one points to a record in table two that points to that record in table one as well as additional records in tables three, four, and five). I can write all those scripts, but if I'm just not understanding the automatic way to do this it would be huge waste of time.

            • 3. Re: I'm missing something about relationships...
              etripoli
                

              Filemaker is very direct.  Let's say you have a layout based on your first table, with the category_id field from the first table, and the category_name from the second table.  If you change the value of the category_id field, the related category_name will change.  However, if you change the category_name, it will not change the category_id.

               

              To me, it sounds like you want a lookup table (the second table), that has a static list of id/name pairs, to use on the layout based on the first table.  If that is the case, you need the id and name fields in the first table also, with the category_name field setup as a lookup from the category_name field in the second table.

               

               

              Edit: I think what you want is a value list, based on the category_id and category_name fields, applied as a popup list to the category_id field of table 1.

              • 4. Re: I'm missing something about relationships...
                OpenOfficer
                  

                Edit: I think what you want is a value list, based on the category_id and category_name fields, applied as a popup list to the category_id field of table 1.

                 

                Yes, that's it exactly. But I can't figure out a way to do that that displays the names but puts the number in table 1.

                 

                In retrospect, maybe the solution is to just get rid of the category_id in both tables. I could put the actual category_name in table 1, use that field for the popup list, specify the category_name in table 2 as unique, and then relate the two name fields, thus providing access to other fields in table 2. I'll try that next.

                 

                Thanks for helping me with this.

                 

                • 5. Re: I'm missing something about relationships...
                  philmodjunk
                    

                  ID numbers are generally bettter than name fields. Names are subject to change and if you've used the field as a relational key, then the name change breaks the link to any related records while the ID number will remain unchanged by such name changes.

                   

                  Define your value list as a two column value list, first column id number field, second column name field and specify that the first column be hidden. That's what Mr. Vodka is recommending.

                  • 6. Re: I'm missing something about relationships...
                    mrvodka
                      

                    Phil explained it pretty well. See this very similar recent thread where the same contemplation was made.

                     

                    http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=22215

                    • 7. Re: I'm missing something about relationships...
                      OpenOfficer
                        

                      Thank you for your help. I didn't realize that the Value List Setup dialog was where the magic happened. Now I've got the pop-up displaying names but inserting numbers in to the right field.

                       

                      So much of what I want to do is just so easy in Filemaker and so hard with other relational data bases - like turning fields into buttons that go to the related record in a different layout - that when something gets hard I assume I'm missing something.

                       

                      A simple question - if you double-click on a field (or right-click and select Field Control/Setup), on the right there's a list of fields with the title "Display Data From" (as opposed to "Display Values From", which appears in the middle of this dialog when you pick anything but an Edit Box or Calendar). This "Display Data From" control seems to completely change the target field. That is, it not only displays data from the selected field, it also updates that field if you make a change. It even changes the name of the field shown on the layout. In fact, it seems to do exactly the same thing as right-clicking on a field in layout mode and selecting "Specify field...".

                       

                      Do I have this right or is there something subtle I'm missing? If I have it right, that control might be better named "Specify field" than "Display Data From." Which is why I suspect there's something I'm missing.

                       

                      • 8. Re: I'm missing something about relationships...
                        philmodjunk
                           You have things right. When the Filemaker engineers added this dialog, they included the field list on the right so you could specify a field and select a value list format all from the same dialog.
                        • 9. Re: I'm missing something about relationships...
                          mrvodka
                            

                          Yes you are correct. They are the same. It would be nice to have the wording consistent.

                          • 10. Re: I'm missing something about relationships...
                            TSGal

                            All:

                             

                            I have forwarded this wording inconsistency to our Development and Product Management departments.

                             

                            TSGal

                            FileMaker, Inc.