13 Replies Latest reply on Feb 22, 2012 8:43 AM by ShaikhAtta1238

    Display User Related Records

    ShaikhAtta1238

      Title

      Display User Related Records

      Post

      I am working on a database and it includes about 15 users. Now i want every user to view and add their own records and not to view other users records. How am I going to achieve this ?

        • 1. Re: Display User Related Records
          philmodjunk

          Assuming that each user has their own account and password, you can use setting in Manage | Security to control access to records on a record by record basis.

          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.

          If you still have questions after reading that section, feel free to ask follow up questions.

          • 2. Re: Display User Related Records
            ShaikhAtta1238

            Thanks alot Phil but as I am new I will need abit more guidance in this. For example I have 2 users john and mac now how will i setup my security settings so john will add/edit/view only his own records.

            • 3. Re: Display User Related Records
              philmodjunk

              First, give both John and Mac their own accounts and passwords. Are you able to do that? They cannot and should not be full access accounts so you'll need to select or create privilege set that provides them the permissions they need.

              2nd, you need a field in the table that stores the account name of the person who has access to that record. You can set this up as a text field that auto-enters the creator's account name. This will automatically assign new records to the user that created it, but you can still edit the field to assign it to a different user at a later point if necessary. If you do not have such a field in place, existing records will need to be updated with account names to assign them to specific users.

              If more than one person needs to have access to the same records, let me know as we'll need a more sophisticated method of identifying who has access to a given record.

              Let me know if you can get to this point and then we can re-open manage-security and edit the user's privilege set options to set up record level access control.

              • 4. Re: Display User Related Records
                ShaikhAtta1238

                Thanks a Million Phil I have given them the username and password, and they are in the Data entry Only privilege set and a field is setup which records the created by username automatically. Now what further do we have to do so they can not see each others record.

                • 5. Re: Display User Related Records
                  philmodjunk

                  What I am describing limits access to a single table. You'll need to repeat this process for other tables if you need to limit access to them on a record by record basis.

                  Make a copy of your table. This way if you make a mistake and have trouble opening your file with full access, you can discard the current copy and try again with your back up file.

                  Open Manage | Security, select an account and click edit to edit it. If you selected one of the existing privilege sets for this account, select New Privilege Set and specify the basic security settings needed for this user. You may want to refer back to the settings of the original privilege set to match the options.

                  Then select "custom privileges" from the Records drop down.

                  This opens a dialog listing all the tables in your database file.

                  Select the table you want by clicking it.

                  Select "Limited..." from the view menu at the bottom.

                  This opens a specify calculation dialog. You want to enter an expression here that evaluates as "True" (1 or other nonblank, nonzero number) to permit access for a given record and that evaluates as "false" (0 or blank) for every record they are not permitted to view.

                  For this table, we can use:

                  AccountNameField = Get ( accountName )

                  assuming "AccountNameField" is the name of the field that auto-enters the account name. Note that you can use this same expression, but with the table name included: TableName::AccountNameField = Get ( AccountName ) to limit access to tables that are related to the first table.

                  Now click OK and enter your full access password when asked. Open this file with the limited access account and password and see what happens. You should see records that are covered with a grey pattern and the text "No Access" for records that were not marked with this account name. If you perform a find, the "no access" records will be automatically omitted from the found set.

                  See if you can get this far and then we'll look at ways to keep the "no access" records from being visible so that your user interface is a bit nicer to your users.

                  • 6. Re: Display User Related Records
                    ShaikhAtta1238

                    Thanks Phil I have reached till here successfully. Now how can we hide the No Acess Records.

                    • 7. Re: Display User Related Records
                      philmodjunk

                      When the database is open with one of these accounts, any find performed will automatically exclude "no access" records. Thus, you can include this code to pull up all records in the database for which the current user is permitted access:

                      Enter find mode []
                      Set field [YOurtable::PrimaryKey ; "*"]
                      Set error capture [on]
                      Perform Find []

                      This sets up an initial found set for your users, but leaves two ways the user can still get "no access" blocked records to appear: Show All Records and Show Omitted Only. To control these options, you can use FileMaker advanced to set up custom menus with scripts that perform in place of the original actions specified for these two menu options. The above script works as a substitute for "Show All Records".

                      This script works for Show Omitted Only:

                      Show Omitted Only
                      Enter find mode []
                      Set field [YourTable::primaryKey ; "*"]
                      Set error capture [on]
                      Constrain Found Set[]

                      Note: you can use any field in place of PrimaryKey in these scripts if it is a field that is never empty (blank).

                      • 8. Re: Display User Related Records
                        ShaikhAtta1238

                        Phil thanks, where to add this code,

                        • 9. Re: Display User Related Records
                          philmodjunk

                          Make the first bit part of the script that runs everytime the file is opened.

                          • 10. Re: Display User Related Records
                            ShaikhAtta1238

                            Thanks Phil ! but how do i enter the ;"*" in the script. As currently my script is like this

                            Enter find mode []
                            Set field [Project::Proj_id]
                            Set error capture [on]
                            Perform Find []

                             

                            but as you said to enter it like this 

                             

                            Enter find mode []
                            Set field [YOurtable::PrimaryKey ; "*"]
                            Set error capture [on]
                            Perform Find []

                            I also tried to enter it in the calculated result window but its not accepting and also Phil where to enter the second script ?

                            • 11. Re: Display User Related Records
                              philmodjunk

                              Enter "" in the calculated result window but don't try to enter the ;.FileMaker displays that delimitter for you after you click OK to close Specify Calculation.

                              "where to enter the second script ?"

                              This requires FileMaker Advanced.

                              1. Open Manage | Custom Menus...
                              2. Select Custom Menu Set 1 and click duplicate to make a copy of it.
                              3. Now Click the Custom Menus tab, select "Records Copy" can click duplicate.
                              4. Edit this copy of the file menu as follows:
                              5. Give it a more descriptive name if you want
                              6. Select the Show All Records menu item
                              7. Select all the check boxes in the Override default behaviors section. When you click the Actions check box, the specify script dialog will appear and you can select the first script--the one that also runs when you open the file.
                              8. Select Show Omitted Only and repeat the same selections, but choose the second script as the override action for this menu option.
                              9. Click Ok until you get back to Manage  | Custom Menus.
                              10. Click the Custom Menu Sets tab
                              11. Click Add and add the new Records menu you just created and modified. Drag it up til it is next to Records Copy. Select Records Copy and delete it.
                              12. Click OK to dismiss manage | custom menus. Go to each layout based on your table where access is restricted and open layout setup. Select your new custom menu set in the menu set drop down.

                               

                              Now, when a user selects Show All Records on any layout with this custom menu set specified, your script is performed to find all permitted records and no "access denied" records appear.

                              • 12. Re: Display User Related Records
                                ShaikhAtta1238

                                Thanks alot Phil you are really very much help full! With out your support I couldn't have had come this far. 

                                • 13. Re: Display User Related Records
                                  ShaikhAtta1238

                                  Thanks phil is working as required.