4 Replies Latest reply on Oct 29, 2010 7:30 AM by RDWirr

    Filter records on a form layout

    RDWirr

      Title

      Filter records on a form layout

      Post

      I have a single table employee data file that contains all the usual kind of data including the department each person works in. I have a form layout that displays each employee’s detailed data and also a list view layout that shows the list of employees. I need to restrict access to the records of employees depending on the department they are in and also whether they are currently employed or not. I have set up layouts (the form and the list) that show all employee records and the alternate layouts that show only the filtered records I think I can determine access to the unrestricted layouts and restricted ones via security privileges. My problem is filtering the records. I have a global field in the ‘Employee’ table for entering the filter criteria (a pilcrowed list of which departments are unrestricted). I have set up two TOs off the same ‘Employee’ table, let’s call them EmpMain and EmpFilter with a relationship that keys the global criteria field in the EmpMain to the ‘Department’ field in the EmpFilter TO. I have the unrestricted layout with the EmpMain as the underlying TO and the restricted layout with the EmpFilter TO. But I see all the same records in both layouts regardless of what I have in the criteria field. I know this is something very elementary but I have been playing with it for hours without getting it. My eventual goal is to use the same restricted layouts to show different data sets depending on which privilege group logs in but first things first. I also haven’t figured out the active employee one yet either but I think this will follow the same logic using a checkbox on each record and matching it to a global field and including this in the same relationship as the department filter. Can anyone point me in the right direction? Thanks in advance, RDW

        • 1. Re: Filter records on a form layout
          philmodjunk

          The relationship you've set up isn't really going to do what you need with the layouts you've designed. The relationships control how you access records in other table occurrences from the table occurrence of the current layout. Once you navigate to a layout based on the related table occurrence, you can see all records in the data source table just like you could from the original layout.

          If you placed a portal to the restricted TO on a layout based on the unrestricted TO, the filtering relationship would work.

          There are better ways to set this up, however.

          If you use Manage | Security to set up access priviliges correctly, records that the current user's privilege set will show up with the data blocked and text reading "Access Denied" or some such is displayed. If you perform a find, the restricted records are automatically omitted from the found set. Thus, you can use a single layout for all your users but set up different privilege sets and record level access to control which records they can see.

          To see a fairly detailed description on how to set this up so that different users are blocked from different records, look up "Editing Record Access Privileges" and pay close attention to the sub section titled "Entering a formula for limiting access on a record-by-record basis".

          • 2. Re: Filter records on a form layout
            RDWirr

            Hi PhilModJunk,

            Thanks for your response. I plan to use privilege set to control access to the restricted and unrestricted forms but I was hoping to find a way to filter out completely the records displayed instead of showing "no-access" for the restricted ones. The thing is that the restricted forms will be used by people every day for data entry and there are quite a lot of records in the database that are restricted so it means they have to deal with a lot of empty records to get to the records they have access to. It would be a lot cleaner from the user perspective to just keep those records out of the accessible set completely. I know I can use a portal to effectively restrict the displayed set but I really need the complete form view for the details of each record in addition to a list of the records.

            Likewise I have another database I have been working on that contains all the assets of the company but I need to give access to a small subset of those assets to the person in the tool crib to check in and check out tools and supplies. If he had to deal with all the desks and computers with no access records, it would slow him down a lot.

            I was hoping to do this with a self join relationship but I haven’t found the way to do it yet.

            Thanks for your help,

            RDW

            • 3. Re: Filter records on a form layout
              philmodjunk

              If you perform a find, the restricted records are automatically omitted from the found set. Thus, you can use a single layout for all your users but set up different privilege sets and record level access to control which records they can see.

              You can set up a script that is peformed each time a person opens the file or each time they access a particular layout. This script can check their account name and/or privilege set so as to perform a find for just the records they are authorized to view. This way, the only way they should be presented with "no access" records is if they choose something like Show All Records from the Records menu. If you have FileMaker Advanced, you can even set up a custom menu where you write your own script for Show All Records so that choosing this option becomes "Show All Records your are permitted to see.

              • 4. Re: Filter records on a form layout
                RDWirr
                Great PhilModJunk, I'll try that. Thanks for the good advise. I much appreciate your help on this. RDW