3 Replies Latest reply on Oct 11, 2012 7:09 PM by StephenSkoutas

    auto-enter serial for a recurring excel import



      auto-enter serial for a recurring excel import


           I'm new to FM and I'm stumped by recurring imports.  I have set up a template spreadsheet that I want vendors to complete (product quote data).  When they complete the excel template, I paste their new data to a master excel spreadsheet which collects all of the vendor quote data, regardless of vendor or time.

           I then set up FM (pro12) to import these records into a table on a recurring basis.  FM set up a script and a button for me, and the import works very nicely.

           What I am trying to do at the same time is have FM assign my item number (basically a 5 digit serial).  I added a field to the table that the recurring import process created, and called the field PKInternalItemNo.  I defined it as a number, auto-enter serial, can't modify, numeric only, range (10000-99999), and allow override.

           When I go through the process (complete a vendor EXCEL quote template, paste it into the master EXCEL, and run the FM script), the new items appear properly but they are not being assigned my item number (serial).  Any ideas on what I've done wrong, or am I just going about it in the wrong way?

           Many thanks for any insight.

        • 1. Re: auto-enter serial for a recurring excel import

               In your script go to Import Records and click specify import order and then click specify. Make sure all field are correct then click Ok. Another window will popup (Import Options).  Check the Perform Auto-Enter on import.

          • 2. Re: auto-enter serial for a recurring excel import

                 You're 3 for 3 on helping me with perfect, quick, understandable, and correct answers.  Thank you very much.  I greatly appreciate it.

            • 3. Re: auto-enter serial for a recurring excel import

                   I'm seeing an issue with this solution now that I've looked at it more.  The script the FMP12 set up for me during the recurring import process is constantly increasing the serial numbers of the items.  The way I had it set up, I have one master excel spreadsheet that will, over time, contain more and more item specs (vendor quotes).

                   Let's say today is day 1.  Factory A quotes two items.  I paste them onto the master spreadsheet (which is linked to FM via recurring import script).  I save the spreadsheet and go over to FM and run the script.  The items import, and per your fix above, the serial numbers are assigned.  The first item is assigned 10000 and the second item is assigned 10001.  Perfect.

                   Tomorrow, another factory quotes one item.  I paste it into the linked spreadsheet below the first two entries.  I save.  I go to FM and run the script.  All 3 items transfer over, but the serials reset from where they left off and continue increasing. 

                   The desired outcome is:

                   item 1 - serial 10000

                   item 2 - serial 10001

                   item 3 - serial 10002 (the objective is for these serials to remain fixed from first import forward, permenantly)

                   The actual outcome is:

                   item 1 - reassigned next available serial, 10002

                   item 2 - 10003

                   item 3 - 10004

                   The problem will keep on iterating with each recurring import.  Any ideas?