6 Replies Latest reply on Oct 16, 2012 1:26 PM by StephenSkoutas

    reopening an issue - auto-enter serial for a recurring excel import

    StephenSkoutas

      Title

      reopening an issue - auto-enter serial for a recurring excel import

      Post

           I am reposting because I believe that I was too quick to apply the forum checkmark (answered) on my previous post and I can't see how to undo that.

           The original post is here: http://forums.filemaker.com/hives/12f66388aa/posts/create?type=36

           In quick summary, I'm trying to auto-serial a recurring excel import.  It works, but I need the serials to "stick" once they've been assigned the first time.  Those serials = product item numbers and should not change after each import.  Is there a way to assign a serial on import and then leave those fixed and only assign new serials to new excel rows on the next import?  Thanks for considering.

        • 1. Re: reopening an issue - auto-enter serial for a recurring excel import
          philmodjunk

               Disable the auto-enter options during import and instead, use replace field contents to apply serial numbers to the found set of records immediately after import.

          • 2. Re: reopening an issue - auto-enter serial for a recurring excel import
            philmodjunk

                 Oh yeah, and assuming that you are doing an "import matching" type operation to import records and add new for those that don't match, do a constrain found set to omit all records except those that do not yet have a value in the serial number field, then do the replace field contents operation.

            • 3. Re: reopening an issue - auto-enter serial for a recurring excel import
              StephenSkoutas

                   Thanks, Phil.  I'm sorry for the delayed reply.  I'm going to see if I can figure out your suggestion above tonight and I'll report back.

                    

              • 4. Re: reopening an issue - auto-enter serial for a recurring excel import
                StephenSkoutas

                     Phil, I think I'm doing something wrong.  This isn't working for me.  I see where you're going with the "replace field contents" on found set, but for the import to work, it seems to want to always pull in all of the data from the source table, right?  So in doing that, it is going to "reimport" items that are already in the FM table, overwriting them with Excel data (which does not include a serial number).  Therefore, my serial is wiped out with each refresh of the recurring import.

                     I think I found a simple way to accomplish this, albeit not from within FM.  From within Excel, I've added a column to store the serial no.  I put it in column A, and set the formula to =ROW(B2)+9999, then fill it down.  With the first row containing titles, this sets the first line of data (2) to serial no. = 10000, then the serials will increase by one from that point on.  I can then import the whole lot into FM via recurring import, and the serials come along for the ride.

                     1)  Assume the source spreadsheet is never sorted, does anyone see any danger in this method?

                     2)  My preference would still be to contain the serial assignment within FM, so if anyone can tell me where I'm going wrong, I would still appreciate it, and probably adopt the FM solution if possible.

                     Thank you all very much.

                • 5. Re: reopening an issue - auto-enter serial for a recurring excel import
                  philmodjunk
                       

                            but for the import to work, it seems to want to always pull in all of the data from the source table, right?

                       Only if the import so specifies. It's controlled by what field mapping options have been set up for the import.

                       If you must import the data over and over again instead of entering it directly via layouts designed for the purspose in FileMaker, then adding a serial number field makes sense. You can use the serial number column as a match field to the corresponding serial number field in the FileMaker table you can specify that the import be an import matching/add unmatched records as new type of import. The key detail you need to watch out for with this import is to do a show all records to pull all records into your found set before you import the data.

                       Note: It might still be possible to import the data and generate the serial number in FileMaker if you import from either a named range in the spreadsheet that only defines the newly added rows of if you import into a secondary table, omit or delete the rows that already exist and then import the remaining records from the temp table into your current table. This, however, will leave previously imported records unchanged so this is not an option to consider if you need the data in fileMaker to update when previously imported data in the excel file are modified.

                        

                  • 6. Re: reopening an issue - auto-enter serial for a recurring excel import
                    StephenSkoutas

                          

                         

                              but for the import to work, it seems to want to always pull in all of the data from the source table, right?

                         

                              Only if the import so specifies. It's controlled by what field mapping options have been set up for the import.

                          

                         I should have been more clear.  I meant that it wants to pull all of the rows (records) from the Excel auto import.  The individual columns (fields) can be imported or excluded via mapping as you point out.

                         Your understanding is correct - I must import the data over and over again.  Layout forms are certainly the best option, but the source data will be coming from multiple factories in the US, Canada, and Asia.  They are not likely to be running FileMaker, they like email, and they are very used to tacking an Office attachment on an email, so Excel quoting seems like a logical choice.  As long as I can keep a master spreadsheet going with all of the specs in one file (therefore one file name to use for the recurring import), I think I should be all set.  I'll add an Excel generated serial and use that as the primary item key once it is in FileMaker as you have mentioned.

                         I think I'm good, and I thank you very much for your help!