4 Replies Latest reply on Aug 21, 2012 10:15 PM by russbrad

    Re-doing a search at the end of each loop - is there a better way?


      I'm performing a script to duplicate an item along with its related records, as I outlined here: https://fmdev.filemaker.com/message/90759#90759


      The related records are line items, whose data is populated by copying from records in the product table. When duplicating the line items, I'm changing their price group, so they need to get their data from a different product. So instead of using Duplicate Record, I'm using New Record and giving each line item just the information it needs to get the data from the appropriate product.


      However, I'm looping through a found set of records to do this, but when I use New Record, the new record adds itself to the found set, so the loop is thrown out of sequence, and I get unpredictable results.


      So what I've resorted to doing is as follows:


      • perform the initial search (blue)
      • record the appropriate bits of information to manually increment through the records (orange)
      • start the loop (green)
      • after performing the New Record operations, redo the search (yellow)
      • apply the manual looping logic (orange).

      Multiple Searching.jpg


      Is there a better way of doing this? What would be ideal would be the ability to retain a found set and return to it later without performing the search again, but I'm open to suggestions.

        • 1. Re: Re-doing a search at the end of each loop - is there a better way?

          For situations like this, it sometimes works well to do the initial find, then go to the LAST record in the found set, instead of the first. When you create your new record, it will be right after the last record; once you're done with the new record, and presumably the last record, omit them both (two Omit Record commands: the first one omits the new record, the second one omits the original last record). Then repeat those steps (New Record, Set Fields as needed, Omit Record, Omit Record) and exit the loop when Get (FoundCount) = 0.

          • 2. Re: Re-doing a search at the end of each loop - is there a better way?

            If you make sure the found set is unsorted, a new record is added to the end and made the active record, so it's not really unpredictable.


            That said, the entire approach is very roundabout. You can create new line items by duplicating the record, then overwriting fields conditionally (using the same conditions you use to calculate the variables which you then set – or not; see below), then doing the productID lookup based on the combination of old and new info. This alone would make the script a whole lot shorter!


            Either way, you can do a loop with "duplicate/new (depending), modify & lookup, omit, go to first, omit", using "Exit after Last" as the loop condition (or using a counter). This will take care of another bunch of script steps, because it not only saves you going back to your original layout (where you land on the original record anyway, meaning searching it again it superfluous), but also the second loop.


            PS: You could wrap the GTRR into an If statement (and exit if there are no related records), instead of all the scripts that follow. Makes the script a bit easier to read.


            PPS: There are some variables which are assigned, but not used (category, description).

            • 3. Re: Re-doing a search at the end of each loop - is there a better way?

              Excellent!  Worked a treat, thanks.

              • 4. Re: Re-doing a search at the end of each loop - is there a better way?

                Thanks, it was the information that the new record is added to the end of the found set that I was missing.  The omit record idea was precisely what I needed.


                The problem with duplicating line items is that they need to be populated with fresh data from the product table using lookups that exist in the fields themselves.  Those lookups can't overwrite existing data, because the user needs to be able to make modifications to the line items after the lookups have happened - if the lookups overwrote existing data, those customisations would be erased.


                I don't want to add a bunch of script steps to overwrite the data, because then it's not robust to new or deleted fields, making the database difficult to maintain.  It also makes for a much longer script.


                Ideally I'd want a system for recording which parts of the line item have been customised and carry over those customisations, but that's a lot of complexity for a tiny benefit.  This duplication system is only designed to allow for quick creation of multiple options in a quote, and it doesn't need to incorporate that level of customisation.


                Can you explain what you mean by wrapping the GTRR into an if statement?  Are you talking about removing the $lineItemCurrentPosition and $lineItemFoundCount variables and their associated code?


                Also, those category and description variables are used in this script, in the ExecuteSQL statement, it just stretches off the edge of the image.  They're used to find the appropriate product.  I could have directly used the fields instead of copying them into variables, but I used the variables to aid in debugging.