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.