2 Replies Latest reply on Jan 28, 2014 2:11 PM by DeanSuhr

    Show all NOT related data in a portal

    DeanSuhr

      Title

      Show all NOT related data in a portal

      Post

           I want to show NOT related data in a portal, i.e. the inverse of related data (like the result of OMIT all related records, if there was such a choice).

           I have three tables, Developers, Projects and Authorized.  Developers is related to Projects through Authorized, which has one row with the developer key and the project key when a Developer is assigned to a project - creating a Developer to Project relationship.  If there is no row with a developer key and a project key then that developer cannot see that project's data.

           For admin purposes, I'd like to have a "portal-like" view of the developers that are NOT assigned to a given project that I can choose from (portal row script button) to ADD to the project by creating new row in the Authorized table.  On a layout based on Project (i.e. 1 project at a time) I was tinkering with using a portal based on the Developer table related to Authorized by "≠" to show where there is no data (and then to Projects) and then filtering to display rows using isempty(count(project::anyfield)) but I can't get that to work.

           My sense is this will not work ... the list of developers is short as is the list of projects so I may drop back to a dynamic value list and a script.

        • 1. Re: Show all NOT related data in a portal
          philmodjunk

               Projects----<Authorized>-------Developers

               You want to see all developers that are NOT linked by a record in authorized to the current project.

               Make a new occurrence of Developers, Developers|All

               Link it with the cartesian join operator so that all Developers appear in this portal. Then set up a Filter expression on the portal to omit records that have a linking "Authorized" record:

               IsEmpty ( FilterValues ( List ( Authorized::_fkDeveloperID ) ; Developers|All::__pkDeveloperID ) )

               IF using FileMaker 12 or newer, A calculation field using ExecuteSQL could also list all unauthorized developers.

          • 2. Re: Show all NOT related data in a portal
            DeanSuhr

                 Perfect!  The formula stumped me, but now i understand it and it works great.  Thanks...