6 Replies Latest reply on Sep 24, 2010 10:34 AM by nanders

    Script to omit records based on calculation among multiple fields

    nanders

      Title

      Script to omit records based on calculation among multiple fields

      Post

      Filemaker Pro Advanced 8.5

      I am working with two tables related by ID# in an education database. Table 1 includes fields such as kp_ID#, GRADUATION TERM, and ADMISSION TERM.  This table also has a summary field, COUNT_ID# to count the number of students who meet specified criteria in a subsummary report. Table 2 includes fields such as kf_ID# and COURSE TERM.

      My problem is that I am unable to figure out how to write a script that will return the number of admitted students who took a course during a given COURSE TERM, but also omits students whose GRADUATION TERM occurs earlier than the COURSE TERM and students whose ADMISSION TERM occurs later than the COURSE TERM.

      For example, if we are searching for the number of students enrolled in courses in COURSE TERM 2008, we want to

      • include students whose ADMISSION TERM occurred during or prior to 2008
      • include students whose GRADUATION TERM occurred during or after 2008
      • omit students whose ADMISSION TERM occurred after 2008
      • omit students whose GRADUATION TERM occurred before 2008

      I have written the script to the point where it will do everything I need except the bulleted items listed above. At the end of my current script, I can constrain the found set by omitting records with ADMISSION TERMs that occur after the COURSE TERM and GRADUATION TERMs that occur before the COURSE TERM, but I would like to add the process to the script if possible. It seems that I would need to constrain the found set based on a calculation involving multiple fields in two related tables, but I’m just not sure. A screenshot of my current script is attached

      Script2.png

        • 1. Re: Script to omit records based on calculation among multiple fields
          philmodjunk

          The problem with listing scripts with steps such as Constrain Found Set [Restore] is that you can see what criteria is being restored.

          It would help to know what criteria is being specified in each of these steps.

          I'm scratching my head over this section.

          1. include students whose ADMISSION TERM occurred during or prior to 2008
          2. include students whose GRADUATION TERM occurred during or after 2008
          3. omit students whose ADMISSION TERM occurred after 2008
          4. omit students whose GRADUATION TERM occurred before 2008

           

          It would seem that a find that meets the criteria for 1 and 2 automatically omits records meeting the criteria for 3 and 4.

          Are admission term and graduation term fields number or text fields? If they are number fields storing just a 4 digit number for the year, the following scripted find should find the records you are looking for:

          Enter Find Mode[]
          Set field [Yourtable::AdmissionTerm ; "< " & 2008 ]
          Set Field [Yourtable::GraduationTerm ; "> " & 2008 ]
          Set Error Capture [on]
          Perform Find[]

          Note that you can use a global field or a variable in place of the number 2008 and use this script to find all students for any course term you wish to specify, passing the value to this script in a global field or as a script parameter.

          • 2. Re: Script to omit records based on calculation among multiple fields
            nanders

            PhilModJunk

            Thank you for your prompt response. I appreciate your suggestions and  questions. I can definitely see that you are pointing me in the right  direction. In regard to the “I’m scratching my head of this section”  comment, I am now doing the same. At the time I was writing, including  criteria 3 and 4 seemed to make sense, but after rereading them, they do  seem redundant. I edited the original post to attached a picture that  highlights more details in the script, including screenshots of the two  layouts that are used. (I woud've attached the picture to this comment,  but I wasn't sure if I could add a picture from my computer. I am sorry  that the field names changed from my original question. I modified the  names a bit in my first question because I thought it would be easier to  read and interpret (PeriodEnding_ADMITTED = ADMISSION TERM. Also the  search value “2008” would appear as “PESU08” in the database to  represent “period ending summer 2008”.) I apologize for any confusion  the name and value modifications may have caused. The PESU08 format  seems to be working because we don’t have any records that date back to  the 1900’s. This type of a value, which is formatted as a number, would  appear in the fields for graduation, admission, and course term.

            The script you propose sounds like it might be a good fit if it includes the suggestions you offer in your ending note: “you  can use a global field or a variable in place of the number 2008 and  use this script to find all students for any course term you wish to  specify, passing the value to this script in a global field or as a  script parameter.” The term “global field” is familiar to me, but I  am not familiar with how to use one. Could you please explain more  about how I would be able to accomplish what you are suggesting in your  note? Understanding how to write the script for this report will aid in  the design of approximately 20 separate reports that require students to  be found under the same search criteria.

            Thank you so much for your time and assistance!

            • 3. Re: Script to omit records based on calculation among multiple fields
              philmodjunk

              First a problem: PESU08 is text and comparisons work differently for text than a number such as 2008. Given that the field is defined as a number field adds a further complication in that its indexed value will be just 8--the numerical value of 2008. Are there other possible values that might be entered in this field that end in 08 such as Peroid ending Fall 2008?

              • 4. Re: Script to omit records based on calculation among multiple fields
                nanders

                The only 08 value that can exist in this field is PESU08. This value is calculated based on the term that is entered for each course. Our annual reporting period follows a Fall, Spring, Summer sequence. Therefore, courses that were offered in Fall 07, Spring 08, and Summer 08 will all fall into the PESU08 category.

                • 5. Re: Script to omit records based on calculation among multiple fields
                  philmodjunk

                  To use a global field in a search, first define a field in one of your tables, select field options and specify global storage.

                  Let's call it a global number field named gPeriodEnding. You can format it with a value list (pop up menu, drop down list or radio buttons) to limit errors.

                  In your script, put tableName::gPeriodEnding in place of the spots where I used 2008.

                  Note that you can't refer to a global field inside Find[restore] or Constrain found set [restore]. You have to enter find mode, use set field to set the criteria and then use either Find[] or Constrain Found Set[].

                  • 6. Re: Script to omit records based on calculation among multiple fields
                    nanders

                    PhilModJunk, you are an absolute lifesaver. This is exaclty the type of help I was looking for! Thanks so much for your time and expertise. I really appreciate your assistance!