4 Replies Latest reply on Feb 11, 2014 12:18 PM by philmodjunk

    YAEQ - Reading and Writing files in scripts

    gregdc

      Title

      YAEQ - Reading and Writing files in scripts

      Post

           Yet another elementary question:

           Background

           In FM13 I have these tables

           Person   Role   TempFile   ReportFile

           I need to read from Role, retrieve some information from Person, and then Write that to TempFile.    I will then read TempFile do some calcs and write Records to ReportFile. 

           Person is related to Role by a one-to-many relationship.  TempFile combines the info from Role & Person (FullName, RoleName, RoleStart, RoleEnd.   The TempFile is then processed to do the Summaries of For a given Year Who/how many are in a Role.  Those results are to be written to ReportFile and Displayed/Printed

           (My background is Basic/Fortran/Procedural programing, and am very new to FM scripting) 

           Now for the Elementary Question:

           I have tried this

           Go to Layout Role

           Go to Record First

           Loop

           create a set of $Variables

           GoTo Layout TempFile

           Set Fields from $Variables

           Commit Records

           Go to Layout Original Layout

           Go to record next, Exit after Last

           End Loop

           I am watching the TempFile in another window.  Records appear there, then disappear.    I do not see what I am doing wrong.   Could someone point me in the right direction?  Please!

           GregDC

        • 1. Re: YAEQ - Reading and Writing files in scripts
          philmodjunk

               I can see the Fortran mind set here. But this is not Fortran, it's a relational database. You need to think in terms of tables and relationships first, then design a report layout that shows the results that you want.

               Your script could be fixed--From the outline that you posted, it looks like you aren't creating new records and thus your data is overwriting the same record over and over again, but there's a much simpler way and there is really no need to copy data from one table to another when you can set up relationships to link to that data instead.

               From what you have described, a single person can have more than one role, but can the same role be listed for more than one person?

          • 2. Re: YAEQ - Reading and Writing files in scripts
            gregdc

                 Hello again Phil

                        First thanks for the quick response.   I really appreciate.

                        Second,  to answer your question the relationship between Person and role is  PersonID -|----< PersonID, RoleID-Sdate-Edate.    The use of the temp file is because of the counts the report needs

                 PersonName  RoleName  SDate  EDate

                 AB                    Floor Clerk   2008 

                 CD                    Floor Clerk   2008

                 EF                    Floor Clerk   2008   2010

                 GH                     Floor Clerk   2009 

                 IJ                      Floor  Clerk  2009

                 KL                      Floor Clerk   2010

                 The challenge is that there is a running total in the Summary and Detail report is

                 Summary

                 Year    Role      Current Number = Last years total + add people - resigned people

                 2008    FC            3                                   0                    3                    0

                 2009    FC            5                                  3                     2                    0

                 2010     FC           5                                  5                     1                    1

                 Detail

                 Year     Person    Role   Resigned in    Current Number

                 2008      AB          FC

                 2008      CD          FC

                 2008       EF          FC       2010

                 Total                                                               3

                 2009      GH          FC    

                 2009       IJ            FC

                 Total                                                                5

                 2010       KL          FC                                     

                 Total                                                                5

                 I use the TempFile to write the Total Current Numbers into the ReportFile

                 Does that make any sense to anyone but my client and me??  

            • 3. Re: YAEQ - Reading and Writing files in scripts
              gregdc

                   Oh and by the way the reports MUST work on WebDirect   so totals in the footers is not really an option, they will not show correctly. :(

              • 4. Re: YAEQ - Reading and Writing files in scripts
                philmodjunk

                     Technically, this is a many to many relationship, But if the only "Role" specific data is the name, we can keep your data as structured, but I'm not convinced that you need a looping script to do this.

                     It occurs to me that your roles table could be setup to record one new record for each person's role per year. If a person starts as an FC in 2008 and resigns in 2010, You could log two records for that person in Roles, one for 2008, and one for 2009. That would greatly simplify the process of computing total counts as you can use sorting, relationships or ExecuteSQL to produce the parts of this report much more simply with that structure to Roles.