3 Replies Latest reply on May 12, 2011 11:22 AM by HerbLoner

    Set Field to increment record count challenge

    HerbLoner

      Title

      Set Field to increment record count challenge

      Post

      I want to take a file of (let's say) 28 records, perform a script on the first record, count it as "1", advance to the second record, perform the same script, count as "2", and continue doing this until I reach "7". Then I want to continue with record number eight, perform a different script, count it as "1", and continue till I reach "7" again, and do this with every 7 records.  In FoxPro this has been very easy: "counter = counter + 1". It would appear that the Set Field command would work similarly in FM.

      Here is my script:

      Go to [First] Record

      Set Field [ filename:fieldname;  filename:fieldname = -1]

      (Note: if I use a 0, it inserts a "1" in the first record)

      Loop

         Exit Loop If [ filename:fieldname = 7]

         Perform some script

         Set Field [ filename:fieldname; filename:fieldname + 1]

         Go to [Next] Record

      End Loop

      Exit Script

       

      This script puts a “1” in the first 27 records until the last record where it loops 7 times, puts a “7” in the field and then exits.  What am I going astray?  Thnx for your  help.

       

        • 1. Re: Set Field to increment record count challenge
          philmodjunk

          The value of this expression: filename:fieldname = -1

          will evaluate as True (the value in the field is -1) and return 1 or it will evaluate as false (the value of the field is not -1) and return 0.

          Correct syntax is: SetField [ Filename::fieldname ; 1 ]

          Also it's not really "filename" here though it may look like it. It's actually "table occurrence name::field name". You can define any number of tables in the same file, but FileMaker automatically names the first such table with the same name as your file when you first create the database. you can use Manage | Database | Tables to change this name if you want to.

          It then creates a table occurrence "box" of the same name in manage | Database | Relationships. That "box" is what your are actually referring to here in your script.

          If "table occurrence" is a new concept, you may find this tutorial useful:

          Tutorial: What are Table Occurrences?

          • 2. Re: Set Field to increment record count challenge
            LaRetta_1

            Some things to consider about the theory you are applying ...

            Since you are looping through a found set of records, you can use the record number itself as your counter.  If you apply the following script, it will insert a 1 in the first 7 records, a 2 in the next 7 and so on...

            Go To Record/Request/Page [ First ]
            Loop
            Set Field [ yourField ; Ceiling ( Get ( RecordNumber ) / 7 ) ]
            Go To Record/Request/Page [ Next ; Exit after last ]
            End Loop

            But I'm unsure why you are performing different scripts on the same set of records and I wonder if it couldn't possibly be ONE script but taking into account certain variables within your records (or their location within the found set).  Can you say more about it?

            Unfortunately, you cannot specify a script name via calculation so you will need to have enough scripts (using your theory) to address your largest record set.  If you really wanted to apply different scripts to every 8th record in your found set, it would look something like:

            Go To Record/Request/Page [ First ]
            Loop
            If [ not Div( Get ( RecordNumber ) ; 7 ) ]
            Perform Script [ Script 1 ]
            Else If [ Div( Get ( RecordNumber ) ; 7 ) = 1 ]
            Perform Script [ Script 2 ]
            End If ... etc

            I would suspect that we can use one script but use variables to specify the differences between Script 1 and Script 2.  I just can't imagine an instance where we would run different scripts on records only based upon their record number and we haven't even considered whether the record set is sorted or not. 

            Otherwise your script would need to look like:

            Go to Record/Request/Page [ First ]
            Loop
            Exit Loop If [ Get ( RecordNumber ) = 8 ]
            Perform Script [ Script 1 ]
            Go to Record/Request/Page [ next ; exit after last ]
            End Loop
            Loop
            Exit Loop If [ Get ( RecordNumber ) = 15 ]
            Perform Script [ Script 2 ]
            Go to Record/Request/Page [ next ; exit after last ]
            End Loop

            ... and so forth.  But this all means the record set must be finite and static along with the scripts and it all must be hard-coded.  I would think that there are other options to consider here.  What do you think?

            • 3. Re: Set Field to increment record count challenge
              HerbLoner

              Thank you, LaRetta.  You provided me several greast alternatives.  Very clever solution.