4 Replies Latest reply on Feb 3, 2016 8:21 AM by aabrown5

    Joining Multiple Tables in Multiple Ways

    aabrown5

      Hello, thank you in advance for reading through this post!

       

      I have three items I need to connect in multiple ways:

      1. Staff

      2. Program

      3. Office

       

      The relationships:

      Office--->Staff  (1 to many)

      Program <--->Office (many to many)

      Program <--->Staff (many to many)

       

      I want to be able to view:

      1. Related programs & office spaces when viewing staff

      2. Related staff & programs when viewing offices

      3. Related office & staff when viewing programs

       

      I currently have:

      1. Staff TO --- Staff_Program_JOIN TO --- Program TO

      2. Program TO --- Program_Office_JOIN TO --- Office TO

       

      But I can't seem to get Staff --> Office. I've tried numerous ways, and I either end with multiple join records between staff/programs/office (Jane Doe shows up twice in programs, because she's linked to the programs through both the Staff and Office tables) or FM says the tables aren't related.

       

      Attached are two ways I tried to solve the problem. I feel like this should be a pretty easy fix, but I've spent the last couple days attempting to sort it out, and I can't! What am I missing?

        • 1. Re: Joining Multiple Tables in Multiple Ways
          DanielShanahan

          From your diagrams (Scann.pdf) it looks like you are trying to connect Staff to Office via a join table.  Your description says it is a one to many, so you should be able to connect a Staff TO to an Office TO.  No need for an intermediary join table.

          • 2. Re: Joining Multiple Tables in Multiple Ways
            fitch

            Yep, just add Staff to the end of your example 2. above:

            Program >--- Program_Office_JOIN ---< Office --< Staff

             

            What you didn't say explicitly, but your wish 2. sort of implies, is that specific staff are assigned to specific programs. If that's the case you might add another join table between Staff and Program.

             

            In any case you may want to use Anchor/Buoy structure so you have a separate table occurrence group (TOG) for each layout:

             

            I want to be able to view:

            1. Related programs & office spaces when viewing staff

            Staff >-- Office >-- Program_Office_JOIN --< Program

             

            2. Related staff & programs when viewing offices

            Office --< Staff

                    >-- Program_Office_JOIN --< Program

             

            3. Related office & staff when viewing programs

            Program >--- Program_Office_JOIN ---< Office --< Staff

            • 3. Re: Joining Multiple Tables in Multiple Ways
              nicholas.obrien

              I had a similar situation where i had to keep track of people, their vehicles and their parking permits. i was given a lot of dirty information and i was trying to get them all connected appropriately. i had people with permits and cars, too many permits, no permits, permits with no cars.........BIG MESS. I called it a dirty love triangle. ultimately was temporarily solved with a calculation field running a Case calculation.    my relationships diagram was PEOPLE-----PERMITS-----VEHICLES-----PEOPLE2  .

              the case field was setup in the vehicle table and set to get UUID from PEOPLE table from the first occurrence then the second.

               

              a CASE calculation is like an IF calculation but more powerful. It runs the parameters till it finds true then stops.

              hope this might help you like it helped me....

              • 4. Re: Joining Multiple Tables in Multiple Ways
                aabrown5

                Thank you, Tom. I hadn't heard of the Anchor Buoy system, and my research this morning looks promising!