5 Replies Latest reply on Aug 29, 2013 3:24 PM by philmodjunk

    How to access records in table not on active layout.

    DanielHerd

      Title

      How to access records in table not on active layout.

      Post

           In a script, is it possible to retrieve and insert data from fields in records that are in tables that are not on the current layout without using Go To Layout script step to change layouts.

        • 1. Re: How to access records in table not on active layout.
          philmodjunk

               Why must you avoid using go to layout? (Knowing why you don't want to do that helps up suggest workable alternatives.)

               To retrieve data from another table than that specifed in Layout Setup for the current layout can be done without changing layouts, but it requires setting up a relationship that correctly links to the desired table and record within that table.

          • 2. Re: How to access records in table not on active layout.
            DanielHerd

                 I have a table with employee hourly pay rate for specifice jobs, w4 info etc.

                 I have a table that stores timecard info like employee name, employee id, hours at specific job, etc.

                 Each employee has a unique Employee id and these tables are related by this id.

                 I have another table which will hold payroll info, weekly hours, pay etc.

                 So to fill out the payroll table, I select specific employee, then I have to to go to timecard table to get the job and hours at that job, then I have to go to related record in pay info table to the the employee pay rate for that job, then I have to swith to payroll table to store the result of multiplying the two to get the employee pay for that job. So I am constantly changing layouts to access the tables associated with each layout which creates constant flashing of each layout on the screen whill processing all the timecards for each employee. Switch to timecard layout to get time and job, switch to pay info layout to get payrate for that job, switch to payroll table to store the result of multiplying the two and do this for each time card...a minimum of 6 per employee per week.

                 Why cant I just stay on payroll table, which is also related to other two tables by employee Id field, and access the data from other two tables without changing layout?

                  

            • 3. Re: How to access records in table not on active layout.
              philmodjunk

                   Employee---<Timecard

                   Employee::EmployeeID = Timecard::EmployeeID

                   which creates constant flashing of each layout on the screen whill processing all the timecards for each employee.

                   If you start your script with Freeze Window, you should not see any "layout flash" produced when your script changes layouts.

                   That's not your only option. You should be able to establish a relationship that matches to the correct data in Employee and Timecard. The match to time card may well require a relationship with mutliple match fields such as a an EmployeeID and A date or range or dates. Depending on the details of what you need to do in records of this third table, you may be able to do all or most of this without scripting if you have the correct relationships defined.

              • 4. Re: How to access records in table not on active layout.
                DanielHerd

                     I have all the tables related only by Employee ID.  So are u saying, that to do this without changing layouts, that or each field in a record in Table A that I want to access while Table B is in active layout, I need to have a specific relation from Table B to each of those fields in the record of Table A?

                • 5. Re: How to access records in table not on active layout.
                  philmodjunk

                       A specific relationship to table A that matches to the record or records that you need in order to access the needed data. That could easily require more than one pair of match fields in order for that relationship to work.

                       Please note that you are not limited to a single relationship between two tables. By adding additional table occurrences, you can add as many relationships as you need.

                       And in FileMaker 12, you can use ExecuteSQL to both define the relationship and access data (read only) from one or more tables without modifying your relationship graph at all.