8 Replies Latest reply on Jul 15, 2014 4:24 PM by EvanKline

    Getting data from related table twice removed



      Getting data from related table twice removed


           I've been working through the training series and Lynda.com tutorials in my spare time over the past few weeks, but fear I won't know if FileMaker will serve my company's purposes before the 30 day trial expires. I've having trouble getting data from one table to appear. I have 4 tables:

           1. Cases - each Case can have only 1 Adjuster, 1 Company (tied to the adjuster), and 1 Attorney

           2. Adjusters - each Adjuster can have (works for) only 1 Company, but handles many Cases

           3. Companies - each Company has many Adjusters (and as a result, many cases)

           4. Attorneys - each Attorney (our company's attorneys) has many Cases

           Here is how I set up my Relationships:


           I've created a "Case Details" layout that will list a case, along with our company's attorney associated with that case, plus the adjuster and adjuster's company associated with the case. I have no problem getting the attorney's info to appear, along with the adjuster's name (using a calculation that pulls the full name and last name).

           I can't for the life of me, however, get the adjuster's company to appear. I've tried various permutations of a value list tied to fields, and either get a blank box on the Case Details layout, or the Adjuster's ID. Here's an example after one attempt (yes, I know it's ugly. I'm working on seeing if I can even get it working before I work more on the layout). The "17" is a particular adjuster's ID - where it appears first, my drop down actually does show the adjuster's full name. The bottom box is the problematic one - I can't get it to show anything but the number. I'm trying to get it to show Company name:


           I'm assuming that I'm screwing up my relationships somehow, but any insight that anyone can offer would be much appreciated. After trying to cram this stuff in the last few weeks, my brain is about shut off at this point.


        • 1. Re: Getting data from related table twice removed

               I am assuming that your Case details layout is based on Cases.

               It is not clear to me why you want a value list for companies on this layout.

               If you want to see the name of the company for the adjuster that you've selected for this case, simply do the following:

          1.           Enter layout mode
          3.           Drag and drop from the field tool to add a field object to your layout.
          5.           The specify field dialog opens, select Companies from the drop down at the top of this dialog.
          7.           Click the field from Companies that you want to provide data to this field object.
          9.           Return to Browse mode, saving your changes and as long as you have an adjuster selected for your case, the company record linked to that adjuster will supply data to that field.

               If that doesn't work, then either my assumption that your layout was based on Cases was incorrect or there is an issue with the match field values that link a companies record to your selected adjuster.

               It is also possible that you want to select a company first, then select an adjuster from the list of adjusters that work for the selected company. If that is what you need here, let me know and I'll provide you with the info needed in order to do that.

          • 2. Re: Getting data from related table twice removed

                 Thank you for the reply. I tried that initially (simply adding it via layout mode), without luck, which is why I went through my various other contortions. I tried that way again, and I still have the same problem.

                 Here is my Manage Layout screen, confirming that the Case Details layout is based on cases:

                 And here is my layout, with that field in place:

                 And here is what I get in Browse mode:

                 I'm assuming, then that my problem is your second suggestion - that there is an issue with the match field values that link a company's record to my selected adjuster. Are there common problems with this that users make, so I'd know where to look? Here are my match fields, if I understand the term correctly:

                 I'm sure there's a stupid mistake on my part somewhere. I just don't know where to look. Thank again for your help.

            • 3. Re: Getting data from related table twice removed

                   What I am suggesting that you check is not the relationship, but the values of the fields that link Cases to adjuster to companies.

                   What you show could actually be the expected result if for some reason the adjuster whose ID of 12 has been selected is in turn not correctly linked to a record in Companies. This could be due to not selecting a company ID in the __fkCompanyID field for this adjuster record.

              • 4. Re: Getting data from related table twice removed

                     Thank you! Now I feel like an idiot - I must have removed that field from Adjuster Details during my many changes. However, I have another problem (probably a basic oversight again on my part):

                     Now that I've added it back in, my __fkCompanyID field is giving me a blank dropdown on the Adjuster Details page:

                     Here is what it looks like in layout mode:


                     And here is the value list that a Drop Down menu is calling to:

                     I'm obviously doing something wrong here.


                • 5. Re: Getting data from related table twice removed

                       The ID's that make up the first field, should be the _pkCompanyID field from Companies, not the __fkCompanyID field from Adjusters.

                  • 6. Re: Getting data from related table twice removed

                         That fixed it. Thanks for your patience in walking me through some basic stuff, and some boneheaded mistakes on my part. If the 30 day trial had a "pause trial" button, I probably would have pressed it a few days ago to clear my head. I'm not a programmer, so I'm trying to learn all this from the ground up, and then sell my partners on it.

                    • 7. Re: Getting data from related table twice removed

                           I just wanted to say that I also began my work with filemaker by viewing the Lynda tutorials.  Lynda.com has great value, and I'm glad I watched their filemaker series.  However once I finished with ~30 hours of viewing  and sat down at the computer with notes in hand, I was just like- 'Wait, what?'  The most important realization I had in that first week of struggling was that if  I was 'fighting' to get something to work- I was most likely doing it the wrong way.  Fortunately there are some pretty active communities  that will really help you along.  

                           The lynda stuff in retrospect just served to familiarize me with the filemaker developer/designer paradigm.   Most of the methods/techniques I actually rely on I learned through diligent googling and reading:

                           forums (filemaker/technet/fmforums/filemaker daily/etc....)

                           blogs (weetbicks, six fried rice, etc...)

                           specialty sites (filemaker standards.org, filemaker magazine, etc...)

                           aggregators (scoop.it has some nice filemaker accounts)


                           third party plug in sites like seecode which occassionaly give hand out videos/files gratis

                           Above all, and across all these resources, you will find the contributions of a dedicated set of professionals like Phil, Matt Petrowsky, Jeremy Bante, etc... to be invaluable.  In fact here is a nice series of interviews with some of these people:


                      • 8. Re: Getting data from related table twice removed

                             Thanks for the info/links, but also the reassurance, Nihm. I'll be sure to check all of that out. It's nice to see what a vibrant community there is around the product.

                             You hit the nail on the head with me fighting to get something done because I was doing it the wrong way. As I was working through my problem, and before I posted here, all I could think was, "there is no way they made it this hard to do something so simple. I've got to be doing it wrong/missing something."