1 2 Previous Next 15 Replies Latest reply on Feb 9, 2012 6:32 AM by philmodjunk

    Matching records to Users



      Matching records to Users


      Hi All,

      I have created a layout called "Users Records" which i want to create a button on another layout "Data Entry" so that when clicked, the user will be taken to the "Users Records" layout and only those records assigned to that user appear.  I cannot seem to get the script to perform.   I created a field UserLastName that is calculated from Get (AccountName), but how do I get the UserLastName retrieved then matched to the InvestigatorLastName field then sort all the records with that match to display them by case status so that they can be edited.  I thought a perform find would work, but i cannot seem to get it to work.

      Thanks, Emmett

        • 1. Re: Matching records to Users

          Why use a last name instead of the entire account name? And be careful of folks that get married, adopted or just decide they like their nickname better than their birth name and have their name legally changed...

          If every record has an account name field that lists the user to whom it is assigned, this script will pull up a found set of all records assigned to the current user:

          Enter Find Mode []
          Set Field[YourTable::AccountName ; get ( accountName )]
          Set Error Capture [on]
          Perform Find[]

          You can also use record level access controls in Manage | Security to prohibit access to all records not assigned to the current user. With this option, any find the user performs automatically filters out any matching records that are not assigned to them.

          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: Matching records to Users

            Hey Phil,

            Thanks for the reply.  I'm a bit confused.  So the value list for the investigators (those who would be assigned records) would become the list of end users (all of those with an account) to get this to work correctly, right? 

            • 3. Re: Matching records to Users

              And what value list would that be? (I look at a lots of stuff here in the forum, so please don't assume I remember previous threads accurately.)

              As long as you have a text field that stores the account name of the assigned user, it will work. There are a number of ways to enter the correct account name to assign a given record to a given user.

              This assumes that a given record is only assigned to one person. A more sophisticated structure is needed to assign a given record to more than one user.

              • 4. Re: Matching records to Users

                Sorry about that assumption.  I thought about that as I left the office, but it was posted. ;)  I've got it now.  Thanks for the help.  Emmett

                • 5. Re: Matching records to Users

                   Sorry, another quick question about this.  Long term, is the best way to set up a table with global fields to store the user's employee information and user account name that is specific to each? 

                  • 6. Re: Matching records to Users

                    A table of user info would use fields that are not global so that you can change the data in the record as needed and your changes will be their when you access the file the next time.

                    It is possible to use a start up script to find the user record and then load a set of global fields with data copied from the user record. This way, the data is available to all layouts throughout your system without needing to define relationships to the user table for every table occurrence where you need it.

                    I use a variation of this to track which client machines have two monitors so that I can input pixel dimensions for each monitor to better size and position windows to fit the two different monitors.

                    • 7. Re: Matching records to Users

                       Nice. Thank you.

                      • 8. Re: Matching records to Users

                        Hi Phil, Shouldn't this be as easy as   Set Field [Report::User ; Investigator::UserName]?  Report::User is a Value List of each of the users and Investigator::UserName is the table where the info for the users is stored.  They are related via kf_ReportID. 

                        • 9. Re: Matching records to Users

                          But why would you want to link a record in Investigator to a single record in Report like this? Can't one investigator be linked to many report records? If so, you'd want to link by InvestigatorID (not a name).

                          • 10. Re: Matching records to Users

                            Sorry, i wrote that incorrectly, they are related via InvestigatorID not ReportID because as you said the relationship is one investigator to many reports.  But shouldn't the Set Field accomplish the task of assigning the user name?

                            • 11. Re: Matching records to Users

                              It should. If it doesn't, better check the values in the InvestigatorID fields that are supposed to define the relationship linking these two tables. And check to make sure that the script is actually being performed when you expect it to be.

                              (The reason I suggested global fields loaded at startup, is that then you do not need a relationship. You may not even need a script as you can set up auto-enter calculations that enter this data each time a new record is created.)

                              • 12. Re: Matching records to Users

                                The records are created by one user and assigned to 14 other users.   The 14 other users are selected from a drop down list.  So, that's why it seemed easier to match the user from the drop down list to the usernames in the investigator table.  is there a better solution that I may need to be using?

                                • 13. Re: Matching records to Users

                                  Why not use the drop down list directly on the field in the report table instead of the investigator table? That would seem more efficient if each report is likely to be assigned to a different person.

                                  If, on the other hand, a typical "work flow" is for the user to create a group of reports all assigned to the same user, then use a global field with your drop down and set up the auto-enter calculation. With the auto-enter calculation, you can always edit the field in the report table using the same value list format to change the "default" assignment when needed.

                                  • 14. Re: Matching records to Users

                                     Hi Phil, I got it to work using the drop down list from the related field directly and it sorts correctly for the user that is signed in, but it will allow the user to "show all" records with the other users' names blanked out, which is fine i guess, but is that the way it should be working?  Thanks, Emmett

                                    1 2 Previous Next