3 Replies Latest reply on May 2, 2011 12:19 PM by philmodjunk

    Weekly Data Upload - Need a template - How?



      Weekly Data Upload - Need a template - How?


      The task is to import several files - each laid out differently - weekly. The layout of the files is repetitive - all importing is from xls files. Each file has several thousand records and about 45 fields. Idealy I would like to create a database with sufficient fields (about 50) to accomodate importing each of these data sources into the same database.

      The good news is Import Field Mapping works. The bad news - it only works once because I haven't been able to figure out how to save my meticuliously designed field maps. I can do the save-as-clone-thing but this is inconvenient and risky because the the layout is accidently changed too easily. After my first setup I was playing with options and wound up with f1, f2,f3... replacing my column heads and had to start over again.

      The new file data does not replace old file data and if there is a duplicate file it is the old file that must be preserved - not replaced. This eliminmates the recurring import feature.

      There must be something I'm missing. Please point me in the right direction.


        • 1. Re: Weekly Data Upload - Need a template - How?

          You can write a script for this and there are ways to use a validation rule to keep new duplicate data from overwriting the old.

          Once your script is written, you can just run the script each time. There are details that can be handled if you need to such as ways that an Open file dialog can pop up at the right time if the actual file names aren't the same and aren't predictable.

          The script will preserve your field mapping, but you will need to be careful to make sure the format of the excel file does not change. You may want to make a practice of always saving a back up copy of your current file just before performing the import script. (Or the import script can, in some cases, save the backup copy for you just before importing.)

          Do you know how to create a script?

          What data in the xls file makes it a "duplicate" of an existing record?

          • 2. Re: Weekly Data Upload - Need a template - How?

            Scripts are an area of interest that I know nothing (almost nothing) about. I'll dig into them today and see what I can figure out.

            Once a file is in my database the records in it are subject to significant editing and changes. When duplicate records, prior to editing, are presented again for upload they are not wanted and need to be eliminated. There is a unique number in an ID field associated with each record. My intention is to use match-ups of this unique ID to reject duplicate additions to the database. If I can do this in one step at the time of addition to the database it will be extremely efficient and helpful.

            For sorting purposes, two fields that do not exist in the upload file need to be added to each new record as it is entered into the database, a "source field" and a "date field". Every week, File A, File B, and File C will be added to the database. They are named by this protocol "File A 2011 04 16". The following week the same file - now named "File A 2011 04 23" and containing new and duplicate information will be added to the database. Idealy, when the file is uploaded to the File Maker database, it will automatically add information to source and date fields for each record, i.e. Source Field = File A, Date Field = 4/23/2011.

            After this the plan is to modify the Contact Management Starter Solution to opperate the database. I hope once I figure out scripts I'll be close to getting here.

            All input and suggestions very much appreciated.




            • 3. Re: Weekly Data Upload - Need a template - How?

              There is a unique number in an ID field associated with each record

              And does this same number exist in a column of the spread sheet file? If so, this part is easy.

              1. Open up Manage | Database | Fields.
              2. Find the field definition for your ID field and double click it.
              3. On the validation tab, specify Unique Values and validate always.
              4. Now, use Import Records | File, and when you click the Import button after matching fields, one last dialog pops up asking if you want to enable auto-enter options. Click this check box and the duplicate records will not be imported, only the new records.
              5. Immediately after import, your imported records will form the found set. You can then click into each of your two empty fields and use Replace Field Contents to assign the same value to all your records.
              6. Once you can do this by hand, you can try doing it with a script.


              Both Import Recors and Replace Field Contents have the potential to add and/or modify very large numbers of records. You cannot use Undo to reverse the changes. They also have a number of different options to choose from. Thus, you should read up on both these tools in FileMaker help and make back up copies of your file before trying them out.