12 Replies Latest reply on Feb 8, 2016 7:18 PM by miw

    How can I count the number of found records from a loop?

    miw

      I am doing a replace field contents using a loop.  How can I tally the number of records in which the field was replaced so I can show in a dialog box at the end of the process?  Here is what I have scripted:

       

      Go to Related Record [ From table: "Inventory" ; Using layout:  "Layout"]

      Loop

      Replace filed contents [ No dialog ; Field1 ; Fieldx ]

      Go to Record/Request/Page [Next ; Exit after last ]

      End Loop

       

      So what I want to count are only those records where the relationship existed and for which the field data was replaced.  I would then create a dialog box which notes "## records have been found and data replaced"

        • 1. Re: How can I count the number of found records from a loop?
          RickWhitelaw

          if you're using Replace Field Contents then ALL records in the found set will be updated. I see no need for a loop here. Just do a find (then you have the found count) and then replace field contents.

          • 2. Re: How can I count the number of found records from a loop?
            miw

            I am not replacing all records.  That is why I have the Script only going to related records in the table. 

            • 3. Re: How can I count the number of found records from a loop?
              RickWhitelaw

              Maybe I oversimplified. If you want to replace content in a field depending on a condition you would enter the loop and then If(some condition) replace field contents. Before the loop starts create a variable $count. Value would be 0. Then within your If clause redefine $count as $count plus 1. When the loop ends after the last record $count will be the number of records altered.

              • 4. Re: How can I count the number of found records from a loop?
                RickWhitelaw

                I didn't say all records. I said records in the found set. There is always a found set.

                • 5. Re: How can I count the number of found records from a loop?
                  siplus

                  A Set Variable [$found ; get(FoundCount)] after your gotorelated records will tell you how many records.

                   

                  The loop with a replace field contents in it is stupid. RTFM.

                  • 6. Re: How can I count the number of found records from a loop?
                    Fahri Akar

                    As RickWhitelaw and siplus declared no need to loop in this script, because Replace Field Contents menu command or script step is a loop itself.

                     

                    Your script must be:

                     

                    Go to Related Record [ From table: "Inventory" ; Using layout:  "Layout"]      > this step activates your Found Set

                    Replace field contents [ No dialog ; Field1 ; Fieldx ]      > this step is a loop works through the Current Found Set

                     

                    If your replace field contents script step is conditional and some data remains the same and you need a list of changed ones and report it like 8/16 records changed and id lists of changed, your script or structure will change.

                     

                    These are your options:

                     

                    1. The simple way is a mirror field. Fill the temporary mirror field with original. After replace unmathced values are replaced ones. A simple calculation gives the change result.
                    2. Declare a variable in script. Set a loop in script. Do not use Replace Field Contents. Go to first record before loop. In loop set field with your calculation before going next if data changed set your var +1, then next exit loop after last.

                     

                    Example for Second option (I added a list of changed ids to the dialog):

                     

                    Go to Related Record [ From table: "Inventory"; Using layout:  "Layout"]

                    Set Variable [$Replaced; Value:0]

                    Go to Record/Request/Page [First]

                    Loop

                          If [your condition]

                               Set Field [Field; Replacement Data ]

                               Set Variable [$Replaced; Value: $changed+1]

                               Set Variable [$Replaced_ids; Value: Case ( not IsEmpty ($Replaced_ids) ; ($Replaced_ids) & "¶" & Inventory::id ; Inventory::id )]

                          End If

                          Go to Record/Request/Page [Next; Exit after last ]

                    End Loop

                    Show Custom Dialog [$Replaced  & " / " & Get ( FoundCount ) & " Records replaced." & "¶" & "¶" &

                    "Replaced Records IDs: "  & "¶" & Substitute ( $Replaced_ids ; "¶" ; ", " )]

                    dialog.png

                     

                    Fahri

                    • 7. Re: How can I count the number of found records from a loop?
                      miw

                      Fahri - you provided the solution I was looking for.  I knew I needed the loop function to get a count of only the changed records and your solution worked just fine.  Siplus' solution did not do that - the returned count value in his scenario was the total of all the records (not just the records where a change was made) - I wish it could have been that simple.

                      • 8. Re: How can I count the number of found records from a loop?
                        Fahri Akar

                        Although RickWhitelaw gave the same answer before me. I just wanted to give you a more detailed answer.

                        Maybe I oversimplified. If you want to replace content in a field depending on a condition you would enter the loop and then If(some condition) replace field contents. Before the loop starts create a variable $count. Value would be 0. Then within your If clause redefine $count as $count plus 1. When the loop ends after the last record $count will be the number of records altered.

                        Fahri

                        • 9. Re: How can I count the number of found records from a loop?
                          miw

                          Please read my post.  While a loop may be otherwise "stupid" for a replace field contents script, I want to find the population of only the changed records (not total records in the table) in my replace script and it seems a loop with a count variable is the only way to get what I was looking for.  If you have an easier way without a loop, please constructively post.

                          • 10. Re: How can I count the number of found records from a loop?
                            siplus

                            miw wrote:

                             

                            it seems a loop with a count variable is the only way to get what I was looking for.  If you have an easier way without a loop, please constructively post.

                             

                             

                            Yes SIR ayeaye SIR, private Siplus reporting:

                             

                            so you have a value ($Value) that will go via Replace Field contents in all the records found with a GTRR.

                             

                            Well, after the GTRR, store the total found records in $Before := Get(FoundCount).

                             

                            then


                            do a Constrain Find set with action: Omit records (instead of the default Find records), yourFieldToBeReplaced, $Value.

                             

                            This will reduce the found set of GTRR to just the records that really need a replace because their value is different.

                             

                            Now you can do your Replace Field contents (which will be more efficient and faster, because it's done on less records)

                             

                            Grab the Get(FoundCount) in $After := Get(FoundCount) and happily report the situation in merge field $$WorkDone := $After & "/" & $Before.

                            • 12. Re: How can I count the number of found records from a loop?
                              miw

                              Awesome and efficient work-around to get the job done without the loop.  Will work so much faster.  Thanks!