1 2 Previous Next 16 Replies Latest reply on Apr 24, 2013 2:45 PM by Christian

    Multiple Databases Needed?



      Multiple Databases Needed?


           Hi Eveyone,

           I work for a college within a university.  Under the college "umbrella" are different departments/schools.  We currently have ONE database of all faculty and staff members within each department/school.

           I am currently restructuring that database to make it more useable and want to begin making it accessible via the web, and viewable/editable only by department heads.  The thing is, each department should have access to only their respective faculty/staff data.

           What is the best way to accomplish this?  I was thinking of creating a separate database within the Peronnel file for each department/school, and restricting access to only their department heads.  Would this be a "best practices" way of doing things?  If so, how do I import my information from the current ONE database into the separate databases of the departments/schools?

           Please let me know if I need to clarify.

           Thank you for any input!


        • 1. Re: Multiple Databases Needed?

               Separating your data into separate files/tables to limit access to specific sets of records is a bit like killing an ant with a hand grenade. It works, but now you have all kinds of new problems to solve. By issuing each department head a unique account name and password, you can set up your database to use that info to limit their access to only those records they should be permitted to see and now you can keep your current table structure.

               See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

          • 2. Re: Multiple Databases Needed?

                 Thanks for the reply (long ago now).  I've since finished designing the database and populated it.  I am now ready to assign access privileges.  I have created users representing each department and assigned their unique university ID as their login ID.

                 The "limiting access on a record-by-record basis" probably isn't a good solution based on what I need it to do because it limits then by records that that user initially creates.  The records are already created.  I now need to limit the records by a field group "department," and assign certain users to be able to access only certain Departments.  They should, of course, also be able to add new records for just their department.

                 For example, Jane should be able to access and create only records that belong to the Department "Theatre," or Bob should be able to access only records that belong to "History."  The field "Department" is a drop-down box which lists all departments so to easily categorize each new personnel into their dept.

                 The help files don't give me a solution specific to what I need the database to do.  Does anyone have any further details on what I could do?


                 Thank you in advance!

            • 3. Re: Multiple Databases Needed?

                   The method described in that help article allows you to do exactly what you need. The "lock expressions" you can use can incorporate any number of different expressions, they aren't limited to checking for who created the record.

                   Here's how I would do it:

                   Assign all members of a group the same privilege set. Use Get ( AccountPrivilegeSetName ) to test to identify the group to which a given user belongs.

                   If you put a field in your table that records the Privilege Set Name of the department that is permitted to see a given record in the table, you can set up a lock expression for that table such as:

                   Get ( AccountPrivilegeSetName ) = Department //this assumes that you use the names of your departments as the names of your privilege sets.

                   This permits two groups of users access to a given record in the table, those of the designated department and users with full access permissions.

                   More sophisticated lock expressions can permit multiple groups to access the same record as well.

              • 4. Re: Multiple Databases Needed?

                     That works great!  Thank you so much.


                     Now, one more questions regarding this topic.  Please see the attachment.  If the other user, who has access to just specific records, logs in, that person can see all other records as <no access>, as well as the records to which access is granted.  Is there a way to show JUST allowed records?

                • 5. Re: Multiple Databases Needed?

                       Any find performed will automatically omit "no access" records from the resulting found set.

                       Thus you need only have your script perform a find to hide the "no access" records from the user.

                       Here's a sample script that finds all records for which access is permitted:

                       Enter Find Mode []
                       Set Field [YourTable::AnyNeverEmptyField ; "*"]
                       Perform Find[]

                       Without the record level access control, this script finds all records, but given the access controls that you have added, this script finds all records for which the user has access permission. You can set up such a script to run when the file first opens if you find that helpful.

                  • 6. Re: Multiple Databases Needed?

                         Fantastic!  Thank you so much for all the help.  You are just a fountain of knowledge!

                    • 7. Re: Multiple Databases Needed?

                           I thought this thread was complete, but it isn't quite yet... unfortunately.

                           The script works great in the FM application.  When accessing the file via IWP through a browser, the same file will show both the <no access> and the found fields.  In other words, the script does not sort to show only records to which the current logged in user has access.

                           Any ideas?

                      • 8. Re: Multiple Databases Needed?

                             In which case, you'll need your script to perform a find based on the user's account name or privilege set name. I'm surprised to hear this.

                        • 9. Re: Multiple Databases Needed?

                               Shouldn't it already be doing this upon user login?  When logging in on the desktop application, the user is automatically shown only records to which he/she has access, just not when accessing it through the web browser.  It seems like the script should still be functioning regardless of where you log in.

                               IWP has been nothing but frustrating, attempting to make things work on both ends (program, and through browser) isn't as simple as advertised.  Can we get an overhaul here soon?  haha... frown

                          • 10. Re: Multiple Databases Needed?

                                 Designing your database to work in IWP can be like tap dancing in phone booth when compared to just setting things up to work only in FileMaker.

                                 The original Find script does that by exploiting the fact that any find performed will automatically exclude records for which the user is not permitted access. Thus a find that, without record level access controls, would find all records finds all records for which the current user is permitted access.

                                 What really surprises me is that this would work differently in IWP.

                            • 11. Re: Multiple Databases Needed?

                                   So, just to help me get through this scripting, in order to set this up, I have to create a new script and under "Found Sets" select "Perform Find"?

                                   I'm guessing I need to then specify find requests?

                                   Will the "Get ( AccountPrivilegeSetName ) = Department" play a role in determining what the script is to find - based on department logon credentials?

                                   I'm not really sure at all how to go about this.  

                              • 12. Re: Multiple Databases Needed?

                                     Please make sure that this script doesn't work in IWP first:

                                     Here's a script that finds all records--if there is no record level acccess control set up or if the DB is accessed with a full access password, but which will find only permissable records if a password with limited access is used to open the file:

                                     Enter FInd Mode [] --->clear the pause check box
                                     Set Field [YourTable::anyField ; "*" ]
                                New Record/Request
                                     Set Field [YOurTable::anyField ; "="] ---> specify the same field in both set field steps

                                     Set Error Capture [on]
                                     Perform Find []

                                     The first criterion entered by the set field step finds all records where there is some data in the specified field. The second finds all records where the same field is empty. New Record/Request sets up a find where the results from both requests are combined in a single set--which should be all records in the table except any for which record level access control blocks access.

                                     Note: If you specify a field, such as an auto-entered serial number field that is never empty in any record, you can omit the steps shown in blue to have a simpler script.

                                • 13. Re: Multiple Databases Needed?

                                       How do I make sure the script doesn't work in IWP?  I went ahead and created that script in the "Scripts" menu.  Do I have to now link it to anything, or does it run automatically?

                                       Currently, it doesn't seem to be doing anything when I log in through the broswer.

                                  • 14. Re: Multiple Databases Needed?

                                         To see if it works in IWP you have to perform the script while accessing the database via a web browser and see if the No Access records disappear.

                                         No script in FileMaker just runs. Something has to happen to perform it. The simplest way and I suggest you try this first as a test is to put a button on a layout and set it up to perform this script. Then log in via IWP and see what happens when you click this button.

                                         You should be able to use a variety of methods to perform this script:

                                         In File Options, you can set this script to run automatically when the file opens. As far as I know, this script should then run automatically when someone uses their browser to access the file.

                                         You can also incorporate these script steps into a layout change script. You might have a main menu layout with buttons to different layouts in your database. Clicking the button can include a go to layout step followed by the script steps I have suggested here.

                                         And please note that the layout that is current when the script is performed has an impact on whether or not this script works. YOu may need to add a go to layout step at the beginning of this script to make sure that it only performs a find on a layout based on the correct table.

                                    1 2 Previous Next