4 Replies Latest reply on Dec 28, 2014 9:50 AM by philmodjunk

    Show non-related records



      Show non-related records


      I'm having a tough time trying to figure this one out and I sure hope I can accurately describe what I'm trying to do:

      I have a number of employees and we have a number of internal websites, each user has access to some of the websites depending on their seniority. For example the employee we just hired in the mailroom can access a couple of basic sites, each manager can access the basic sites and those related to their department and the owner has access to everything.

      I have three tables in a database, Employees holds employee information (a record for each employee) and Sites contains website information (a record for each website). To connect the two together I've created a third table Conector, it contain only two fields, an employee record Id (EMPrecid) and a site record Id (SITErecid). To assign a site to an employee I simply create a new record in the Conector table, enter the employee's Erecid in the EMPrecid field and the STrecid in the SITErecid field. Using relationships to link the three together I simply have to use a portal in the Employees table to list all the sites the employee has access to. I can do the same thing in the Sites table to see who has access to each site. This all seems to work great, it's very easy to maintain the information in both the Employees and Sites tables.


      Now I'm I'd like to take it a step further, I'd like to list the sites the employee doesn't currently have access to. This would make it much easier to reassign and remove site for employees as they move between departments. I can't figure out how to set up the relationships to achieve this.

      Any help or suggestions would be greatly appreciated.



        • 1. Re: Show non-related records

          Hai Todd,

          I think you could create a new table view, based on the same one you have now. But, change te relation in "not equal" instead of "equal". Then your portal shows the items that are not equal. The only thing is, if you use this solution, you have to built another form to show a portal with the records "not equal".

          If you want to use the same form, you could base the relation on a global field, instead of a table, and change the value of the global field to show the other view. (Equal or not equal).

          Hans Lijnbach.

          • 2. Re: Show non-related records

            Sorry Todd,

            Where I used "table view", I ment "Table occurrence". (Sometimes confusing if your native language is not English). 

            Hans Lijnbach

            • 3. Re: Show non-related records

              Hai Todd,

              Thinking this over, you can make a copy of your website table (second table occurrence) and connect this other table occurrence also to your Connector table, with the option "not equal". In that case you can create 2 portals on one form, one with the websites with access and one portal with websites without access. You could place the second portal in another Tab if you like.

              Hans Lijnbach

              • 4. Re: Show non-related records

                one way to get such a list is to use Go to Related Records to pull up a found set of all permitted web sites, then use Show Omitted Only to show all the ones that are not permitted. This can be popped up in a small modal window.

                Here's another option:

                Define the following unstored calculation field in Employees with a text result specified:

                List ( Conector::SITErecid ) & ¶ & -1 )   // the -1 assures that this field always has at least one value that does not match to any record in Sites.

                If you name it cSiteIDList, you can relate it to a new occurrence of the Sites table like this:

                Employees::cSiteIDList ≠ Sites|Excluded::STrecid

                Sites|Excluded would be a new Tutorial: What are Table Occurrences? of Sites and a portal to Sites|Excluded would list all sites that a given employee is not permitted to access. The same relationship could also be used as a drop down list of sites for selecting sites in a portal to your existing Conector table occurrence. This is, in fact, a modification of the typical dwindling value list as demonstrated in:

                "Adventures in FileMaking #1 - Conditional Value Lists"