6 Replies Latest reply on Nov 7, 2014 3:55 PM by melcam

    Batch Numbers

    melcam

      I am just starting to use filemaker, and am trying to create a solution which will import a file, sort it based on the SKU field, and then assign a batch number to all the records with matching skus. The idea was that all records imported from the file would have the same batch number, the next time the script runs it would assign the next number up (IE file x=batch 1, file y=batch 2, file x=batch3), and each batch would be divided into subgroups based on the SKUS (so it would be like this batch 1-sku1, batch 1-sku2, etc). There are up to 80 different possible skus, so I would like to not have an if statement for each one. I was thinking I could use the "repitition" part of the replace contents action and have the calculation check if the SKU from the current record is equal to the sku from the last record. If they were not equal it would start the next serial number. I'm just wondering if it is possible to do it this way or if I should try a different method.

       

      So I have two questions: Is it possible to assign batch numbers which will always be unique, and is it possible to assign all records with the same SKU an ID with out using individual if statements.

       

      Thanks!

        • 1. Re: Batch Numbers
          keywords

          It's not clear to me exactly what you are doing, since you refer to importing a "file". Thereafter your description sounds like you are talking about importing a bunch of records. Can you please clarify?

          • 2. Re: Batch Numbers
            TomHays

            If I understand your situation correctly, you start with

             

            File X

            contains

             

            SKU     Widget

            -------     ---------

            P242     bolt

            P297     nut

            M448     washer    

            P297     nut

             

             

            You want to import this and generate

             

            Bat     SKU     Widget     Sub-batch

             

            1     P242     bolt     1-P242

            1     P297     nut     1-P297

            1     M448     washer    1-M448

            1     P297     nut     1-P297

             

             

            If this is accurate, then you don't need to sort the records after importing.  The imported records are in the current found set.

            Perform two Replace Field Contents script steps.

            The first is set the Batch field to the value of 1 for every imported record.

            The second would be a calculated result that is

            Batch & "-" & SKU

             

             

             

             

            The next time you import a file, you want to assign the records to be Batch = 2.

             

            While you could store the batch number of the previous import in a field with its storage setting as Global and increment it for the next import, you may want to create a new table that is dedicated to tracking imports.  Use a serial number field Batch that auto-increments for each new record.  Generating new batch numbers this way will let you keep track of the import date, user who imported, and perhaps some statistics about the import (e.g. # of records, # of sub-batches).

             

            Generating new batch numbers by generating new records in new table is the way you would want to do it in a multi-user environment.

             

             

            -Tom

            • 3. Re: Batch Numbers
              melcam

              Thanks for your help! That's exactly what I need to do. Ok, so I think I understand. In order to have it automatically increment the batch number, I need to have another table for the batch numbers. I can have the script automatically add a new record at the end of each run. But how do I specify that it sets the batch number to the last record created in that field?

              • 4. Re: Batch Numbers
                melcam

                Ok, so I got it to work by creating two fields in the Import tracking table, one called "Last Batch Number" which is global for all the records, and another called "Number for This Batch" which is not global, and keeps the same number that the batch has. Is there a better way to do this, or is this way fine?

                • 5. Re: Batch Numbers
                  TomHays

                  Say you have a new table called "Batch".

                  For records in this table you have a field called BatchNumber which is set to be an auto-enter with a Serial number.

                   

                  You have a script that is responsible for importing a new file to your ProductActivity table (where each record has SKU, BatchNumber, and SubbatchNumber)

                   

                  That script might go something like this.

                   

                  Go To Layout ["Batch table layout"]

                  Enter Browse Mode

                  New Record/Request

                  # remember the batch number of the new record to use on the newly imported records

                  Set Variable [$theBatchNumber; Value: BatchNumber]

                  Go To Layout ["ProductActivity table layout"]

                  Import Records[]

                  # Do your own error handling to make sure the import was successful.

                  Replace Field Contents [ProductActivity::BatchNumber; $theBatchNumber]

                  Replace FIeld Contents [ProductActivity::SubbatchNumber; $theBatchNumber & "-" & SKU]

                   

                   

                   

                  -Tom

                  • 6. Re: Batch Numbers
                    melcam

                    Thanks that works perfectly!