6 Replies Latest reply on Feb 5, 2015 9:47 PM by MichaelHolden

    Using Loop Script Step to duplicate Records

    MichaelHolden

      Title

      Using Loop Script Step to duplicate Records

      Post

      Hi All,

      Hoping for some guidance in doing the following:

      I've written a Stocktake Solution that effectively uses 3 Tables: Products,Stocktake Batch and Stocktake Details.

      As a time saver the users would like the ability to be able to take the last Stocktake Batch and duplicate that as a new Stocktake - since approx. 80% of the Products' Stock on Hand levels don't vary from month to month. [Unusual Industry].

      I'm needing some help in how to do this. I can easily create the Stocktake Batch record using a Script and the Set Field Step.

      I'm assuming [hoping] I can use a combination of Set Field and Loop steps to duplicate the batch, i.e. 

      - Use the Perform Find Step to find all Stocktake details from the previous Stocktake

      - New Record Request

      - Loop

      - Set Field

      Am I on the right track? Is it possible to do this? Having never done this before, I am not sure best way to approach it.

      Of course each record in the Stocktake Details table has a unique ID which I will need to avoid duplicating the GUID/UUID.

      Any suggestions will certainly be appreciated.

      Regards,

      Michael

        • 1. Re: Using Loop Script Step to duplicate Records
          philmodjunk

          Don't forget the Duplicate Record step. That can reduce the need for set field steps.

          Here's a thread with a sample script that you may find helpful. Ignore the original form of the script and look at the revised version found at the end of this thread: Duplicating Bill Of Materials (duplicating portal line items)

          • 2. Re: Using Loop Script Step to duplicate Records
            erawson

            I like the way you use omit records in your script, I have been doing New Window -> Duplicate Record -> Close Window instead but that fires the on layout enter script trigger whereas yours does not.

            • 3. Re: Using Loop Script Step to duplicate Records
              MichaelHolden

              Hi Phil,

              Thanks for the link; the original query is exactly the same as mine [even down to my 3 tables serving the same purpose as the 3 tables listed in the OP on the link provided].

               

              I've created a script based on your revised suggestion. I've even created a new layout to ensure the Script isn't working on a portal.

               

              However, I get an error message when part way through running the Script. Using the Script Bugger it occurs on the line:

               

              Go To Record / Request / Page [$RecordNumber]

               

              The error message is:

               

              StocktakeHeader_ID is defined to require a Value, but it is not available on this layout. Use another layout to assign a value to this field.

               

              The layout is based on the Stocktake Header Table, the StocktakeHeader_ID is generated by calculation [UUID].

              At this the point the Script has duplicated the Stocktake Header Record, but of course the Unique ID has also been duplicated.

               

              If I don't revert the field, and continue running the script, the Stocktake Lines are also duplicated, but again all the Unique IDs remain the same.

               

              I can fix the problem by making both the unique ID of the Stocktake Header Table and the unique ID of the Stocktake Lines Table a Serial Number [e.g. STH000001 or STL000001] and not use a Calculation of UUID.

               

              I must prefer using the UUID. Is there away around this?

              Thanks for your help so far ... greatly appreciated!!

               

              Regards,

               

              Michael

              • 4. Re: Using Loop Script Step to duplicate Records
                philmodjunk

                The script was tested with a table that auto-entered a serial number--what I prefer unless there is a specific reason to use UUIDs. (I find the long strings of characters cumbersome and can't sort on them like I can a serial number...)

                I would think that an auto-entered calc with Get ( UUID ) would not produce this situation, but if it does, you can get around it by using a set field step to reassign the result of Get ( UUID ) to the field immediately after the Duplicate Record step. (Make sure to do this before the new record can be committed and trip the validation error.)

                • 5. Re: Using Loop Script Step to duplicate Records
                  philmodjunk

                  Just ran a quick test. I also found that I could avoid this issue if I cleared the "do not replace existing value" check box.

                  • 6. Re: Using Loop Script Step to duplicate Records
                    MichaelHolden

                    Thanks for following up Further Phil. smiley

                    I updated the UUID to Serial Numbers; I agree easier to work with than the UUID. 

                    FYI - I picked up on the Serial No. "issue" from this post:

                    http://forums.filemaker.com/posts/60ae214a83

                     

                    Have solved the problem; but will definitely look at your other couple of suggestions.