4 Replies Latest reply on Mar 8, 2011 11:21 AM by littledrew_1

    complex relationships and portals



      complex relationships and portals


      I have a page with several portals, but all the portals pull from the same child table projects::  I'm using portal filters to show projects in different statuses (baed off of projects::status). I just found out i can't really use portal filters when it comes to CWP. So i'm going to try using complex relationships to pull of the same things. I've looked into the different operators, but i'm not sure how to about doing this. Any suggestions? i've thought about creating a bunch of tables with global fields to match the 10 different status, but then it gets kinda hazy bringing all those together in one layout...


        • 1. Re: complex relationships and portals

          You can definitely use multiple global fields, though in some cases, you can use the same field, but with different values assigned to the global field. You can also use a calculation set to return a constant value.

          Say you want to see all project records for a given client that have a status of "complete" in a status field in Projects.

          Define a calculation field, cCompleteKey,  in Clients to return the constant text "complete" (without the quotes).

          Define this relationship:

          Clients::clientID = Projects::clientID AND
          Clients::cCompleteKey = Projects::Status

          To get relationships for other values of status, just add calculation fields with other values instead of "complete".

          You can also use this relationship:

          Clients::clientID = Projects::clientID AND
          Clients::GlobalStatusField = Projects::Status

          If you assigne "complete" to the global status field, you get a portal filtered for Complete projects for the current client. If you asign a different value to the global field, you get a different "filter" for your relationship. That doesn't work on a regular filemaker layout if you want both portals on the same layout, but you can use it if you put the global status field on the layout formatted with a value list so that you can select the status you want for your filter value.

          • 2. Re: complex relationships and portals

            Thanks, I applied that concept and it works. But now, dealing with multiple portals, I had to create multiple instances of both tables involved, each with their own unique relationship. How do i combine all these portals on to one layout? This will all be eventually used for a webpage that will allow project managers to see what's going on in their respective groups at a glance, in a very nice and colorful way on large tv's (if you're wondering why i'm not using a summary report).  

            Thanks again!

            • 3. Re: complex relationships and portals

              Is there any reason you can't just create multiple instances of the child table and relate them all to the parent table? (Obviously each using a different relationship)

              You will need to create a set of fields in the parent table, either global or constant calcs (I prefer constant calcs) - one for each project status: awaiting approval, in progress and complete.

              The something like:

              Clients::clientID = Projects_Awaiting::clientID AND

              Clients::awaiting = Projects_Awaiting::Status


              Clients::clientID = Projects_In_Progress::clientID AND

              Clients::inProgress = Projects_In_Progress::Status


              Clients::clientID = Projects_Complete::clientID AND

              Clients::complete = Projects_Complete::Status


              My example would give you one parent table with 3 child tables related to it, allowing you to have 3 portals each displaying the relevant records on the same layout.

              • 4. Re: complex relationships and portals

                thanks! that worked perfectly. A little tedious with 15 statuses, but it'll pay off in the end (and then thn some) when I implement this on the web version of our database.