2 Replies Latest reply on Apr 14, 2015 10:01 AM by sfishman@ou.edu

    Duplicate multiple records a specific number of times (i.e., for a label)

    sfishman@ou.edu

      Title

      Duplicate multiple records a specific number of times (i.e., for a label)

      Post

      I need help in creating a script to duplicate records a specific number of times for labels according to a record count.  I am working with a collection of objects and each object needs to have its own label.  For example, say I have a bag (x) with 4 objects and another bag (y) with 2 objects.  (another way to phrase it is Bag x has a count of 4 and bag y has a count of 2).  X will a total of 4 labels and y will need a total of 2 labels.

      I created a label database file where I am exporting the information (to safeguard the primary database).  The label database only has two fields: Number of Records Needed (equivalent to record count) and LabelName. 

      I tried to follow the instructions in the post How To Create A Specific Number Of Duplicate Records.  I added the fields listed in the script, but it is not working.  I work with >1000 records in each collection with a random number of records needing to be duplicated, so it is not feasible to individually duplicate them.

      What changes do I need?

      Set Error Capture [ On ]
      Replace Field Contents [ CatalogTbl::RecordID ; Replace with serial numbers: Entry option values ; Initial value: 225 ; Increment value: 1 ][ No dialog ; Update Entry Options ]

      Go to Record/Request/Page [ First ]

      Copy [ CatalogTbl::RecordID ][ Select ]

      Enter Find Mode [ ]
      Paste [ CatalogTbl::RecordID ][ Select ]

      Perform Find [ ]

      #COMMENT: The script steps above prepare our database to create duplicates. So that the solution will run from beginning to end without

      interruption, we have Set Error Capture to On. This will prevent any error messaging from FileMaker Pro to appear while the script is

      running. It will then find all of the records to be duplicated.

      Each record must have a unique ID. This is what our 'Replace Field Contents' step is doing. Once each record has its unique ID, we will copy the

      number from our ‘Record Number’ field and perform a find for the record which has that number. This will make the first record the only

      record in our found set.

      Go to Record/Request/Page

      [ First ]

      Set Field [ CatalogTbl::Global Match for Records ; 1 ]

      Set Field [ CatalogTbl::Original Marker ; "X" ]

      Set Field [ CatalogTbl::Global Match for Duplicates ; "1" ]

      Loop

      If [ IsEmpty ( CatalogTbl::Amount of Duplicates )=0 and CatalogTbl::Amount of Duplicates CatalogTbl::Global Match for

      Duplicates ]

      Duplicate Record/Request

      #Comment: The script steps above will go to the first record in our found set. The found set should be only one record. We then will mark

      the record as our original with an 'X'. We will then set our field called 'Global Match For Records' to the 'Record Number' number

      plus 1. This number is the unique ID of the next record. Our Set Field script then sets our 'Global Match For Duplicates' field to the

      number 1 for the purpose of comparing this number to the number found in our 'Amount of Duplicates' field. If the numbers do not

      match, a duplicate record will be created.

      Clear [ CatalogTbl::Original Marker ]

      [ Select ]

      Set Field [ CatalogTbl::Global Match for Duplicates ; CatalogTbl::Global Match for Duplicates+1 ]

      Else

      Set Field [ CatalogTbl::Global Match for Records ; 1 ]

      Copy [ CatalogTbl::Global Match for Records ]

      [ Select ]

      Enter Find Mode [ ]

      Paste [ CatalogTbl::RecordID ]

      [ Select ]

      Perform Find [ ]

      Go to Record/Request/Page

      [ First ]

      Set Field [ CatalogTbl::Original Marker ; "X" ]

      Set Field [ CatalogTbl::Global Match for Records ; CatalogTbl::Global Match for Records+1 ]

      If [ Get(FoundCount)=0 ]

      Show All Records

      Exit Script [ ]

      End If

       

       

       

        • 1. Re: Duplicate multiple records a specific number of times (i.e., for a label)
          philmodjunk

          I see a number of problems here. To start, you really should create your duplicate in a separate table related to the first. Then you need only delete all records when done instead of trying to separate out the original from the duplicates. As long as each record in your original table has a unique ID, you need only set up a relationship matching records between the two tables by this ID. Your script has go to record/request [first] so it never exits the loop and never moves to a different record. Copy/paste steps are also not optimum as set variable/set field may be used for the same purpose without destroying any data the user may have previously copied to the clipboard.

          So your relationship would be:

          CatalogTable::__pkUniqueID = Labels::_fkUniqueID

          I don't see anything in your script that specifies different label quantities for different records in your table. I will assume that you have a number field in catalogTable named LabelQty.

          #First perform a find for the records you for which you want to produce labels. This can be a manual find or a scripted process.
          #This should be start on a layout based on CatalogTable.
          Go to record/request/page [first]
          Loop
             Set Variable [$ID ; value: CatalogTable::__pkUniqueID ]
             Set Variable [$Qty ; value: CatalogTable::LabelQty ]
             Go to Layout ["Labels" (Labels) ]
             Set Variable [$K ; value: 0 ]
             Loop
                 Set Variable [$K ; value: $K + 1 ]
                 Exit Loop If [ $K > $Qty ]
                 New Record/Request
                 Set Field [Labels::_fkUniqueID ; $ID ]
              End Loop
             Go to Layout [original layout ]
             Go to Record/Request/Page [next ; exit after last]
          End Loop
          #Your script might print here automatically or it might pause so the user can load label stock into a printer

          • 2. Re: Duplicate multiple records a specific number of times (i.e., for a label)
            sfishman@ou.edu

            It works!!!

            I modified the script from new record to duplicate record and it works perfect.  Thank you!