FileMaker has always been an incredible tool for generating reports and for many years, I used to have a report screen with banks of buttons to generate all of the individual reports. In the last few years however, I’ve added a global field, to the footer of each module data entry screen, with a pop-up menu, listing all of the different report options for the module in question. To that global field, I’ve added an onObjectModify script trigger which sets the name of the report as the parameter that generates the report they want. Simple, very effective and it has the added advantage of giving the user all the reports that are relevant to where they are at the time.
Recently I had a project where some of the reports that were required were restricted to the different privilege sets and I was looking for a quick (and easy) way to accomplish this. I started thinking about whether I could use a related value list to solve the problem and came up with, what I think is, an elegant and very simple solution.
The first thing I did was create a Reports table with just two fields, Group (for the privilege set) and Report (for the name of the report itself).
The next thing was to create the privilege sets themselves (none of these privilege sets have any particular settings).
I then created a global field, GRepOptions…
…(which is going to display all of the different report options) in my module table and a calculated field PrivSet…
…which is used in the relationship to restrict access to certain reports.
(Note that this table is just for demonstration purposes but, in the real world, you would add the GRepOptions field to all of the tables that you want to report on).
We’ll now add our reports layout to list all of the different reports and add access to each of them.
As you can see, the Administrator group has access to all 6 reports, Office Staff has access to 4 reports and Staff only has access to 2.
The Group field…
…uses a value list that has the different privilege sets.
…which is all very well and good, but, as a developer, I need to have access to all of the reports and the relationship doesn’t allow that as I’m logged in as “[Full Access]”. For this reason, I set an auto-enter option on that field for each new record:
(Note that the user doesn’t see this value, is not aware that it exists, and cannot change it).
All that we have to do now is add our GRepOptions field to our screen and format it to display the related reports.
So with all done, let’s look at what our options are for each of the different privilege sets:
(Bear in mind, that on your screen the PrivSet field doesn’t need to be displayed.)
We’re almost done. We now just need to add a script trigger to the GRepOptions field…
…which runs the Report Options script…
…and that’s it. Just as a recap, the only reports that each user can choose from are the ones available for their specific privilege set. We don’t have to set up any complicated privileges or access or write lengthy conditionals, in the script, to restrict access.
If there is a downside, it is that you have to make sure that the report names are accurately typed in the script above but otherwise it is a really easy methodology to implement and use. (If you wanted to, you could add a simple integer number field to each report and then have a number field (in your reporting table) which looks up the value of the currently selected report and then uses that value as the script parameter).
One Last Thing
All of the work that you’ve done here could easily be undone if all users could access the Reports screen. For that reason, that layout is hidden and can only be accessed via a button that runs the following script:
The Else If line is shown in full below:
This means that only developers and Administrators can access the screen to add/delete reports and set privileges.
© Michael Rocharde, January 12, 2013