3 Replies Latest reply on Jun 28, 2015 7:52 AM by philmodjunk

    Script to Lookup and Find Data based on Account Name

    ArthurDruzcz_1

      Title

      Script to Lookup and Find Data based on Account Name

      Post

      Hello,

      So, Filemaker 13 Pro, I have layout Employee Info which has Account Name as well as Employee Name , Layout Main Menu, and then 3 Layouts which are all organized by Employee Name not by Account Name. The 3 Layouts Are Time Logging, Expenses and Safety Journal. 

      When Employee Logs in Using their Account Name they are taken to Main Menu which has 3 corresponding buttons for the 3 layouts. What I want is to write a script that I could use on each button that would Take them to the right layout then Look up the Employee Name based on Account Name and Show only Results with Employee Name.

      So I really don't know how to go from Get Account () to Employee Name and to input this into Find ()

      Each button:

      Get Layout (1 out of the 3)

      Look up Account Name in Employee Info Layout and Return Employee Name and set this as the Find in this layout. 

      Please help,

       

        • 1. Re: Script to Lookup and Find Data based on Account Name
          philmodjunk

          You should use an Employee ID and not a name. Employee Names are not always unique and can sometimes change.

          But there are several basic approaches (that can be used with Names or IDs)

          1) Use this script to get the current user's employee name ( or ID ):

          Go to Layout ["Employees" (Employees)]
          Enter Find Mode []
          Set Field [Employee::AccountName ; Get ( AccountName ) ]
          Perform Find []
          #Then do one of the following Two steps:
          Set Variable [$$EmployeeName ; value: Employee::EmployeeName ]
          Set Field [GlobalFields::gEmployeeName ; Value: Employee::EmployeeName ]

          Once you have a global field or variable set up with the name or ID. Other navigation scripts can use this value to find the desired records. You can also set up a "lock" expression with them in Manage | Security to control which records are accessible for the current user. If this is of interest, 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 description of how to set this up.

          But with a global field set up with this value in the appropriate table (Not a "globals" table in this case), You can also use a relationship with portals or Go To Related Records to pull up the correct set of records for a given user.

          And you can use a relationship or executeSQL query for the initial query to pull up the data as well. An unstored Calculation field with: Get ( AccountName ) can be used as a match field in relationships to access account specific data.

          • 2. Re: Script to Lookup and Find Data based on Account Name
            ArthurDruzcz_1

            Thank you, your amazing.  It still took me 5 minutes to figure it out an apply it but I'm not done yet and would like to pick your brain some more. 

            But with a global field set up with this value in the appropriate table (Not a "globals" table in this case), You can also use a relationship with portals or Go To Related Records to pull up the correct set of records for a given user. So if I set up a global variable does it needs a field and what happens when multiple users log on at the same time? If this is not the case then can you explain more how this works. 

            And you can use a relationship or executeSQL query for the initial query to pull up the data as well. An unstored Calculation field with: Get ( AccountName ) can be used as a match field in relationships to access account specific data. I would prefer to know how to script using execute SQL query, when you say all the data what does it mean from all tables or just the one reference. 

             

            Also how to I apply all this to each layout, for instance - Expenses, or Time Logging, or Safety Journal and many others. Let me know if you could talk or have time to remote desktop and review my database.  I dont know anyone close who uses filemaker and could give me guidence.  Or plan B could send you an empty database so you could review it?

            • 3. Re: Script to Lookup and Find Data based on Account Name
              philmodjunk

              So if I set up a global variable does it needs a field...

              Read what I posted again. This method uses a global FIELD not a variable. Global fields provide each user with their own value specific to them. If User 1 enters or selects "1" i the global field, other users do not see that value and can select their own values in the field. And this does not need to be a global field in every case. I suggest that you read up on portals, how they work and how to set them up.

              see: http://help.filemaker.com/app/answers/detail/a_id/3604/kw/global to learn more about how globals work in a hosted database.

              ExecuteSQL, will list data from the fields and records referenced in the query all in one field. You can set up a query with tabs and returns to present the user will a table of data much like a kind of "read only" portal. ExecuteSQL can also return the ID's of a set of records in a list. That list of ID's can also be used in a standard FileMaker relationship to match to all of the listed records by their IDs (Primary keys). This approach requires a working understanding of SQL--a query language that can require some effort to learn before you can get it to consistently work for you.

              Let me know if you could talk or have time to remote desktop and review my database...

              This is not a service that I provide for free and my available time is quite limited. A cheaper alternative might be to research the training resources available to find one best suited for your knowledge level and budget. (Some are free.) A number of people new to Filemaker go that route and return here with specific questions when they hit a question that can't be answered from their training materials.