Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Importing question

everyday this forecast sheet gets modified with new orders and the orders that have been manufactured  get deleted.

Once I type in all the new orders under the perspective machines (G95,G5,K95,G126,QC7) that is going to manufacture the orders I simply copy and paste those into the the certified sheet. Once the quality testing is complete I add the L/W heat and C/W heat numbers.  Lot orders are designated by the the thick bold line.   These orders need certified for our customers who purchase these styles of mesh.  We create these certified reports in filemake pro 11 when ever they are shipped.  I have already imported the current certified sheet into FM and need help to figure out how to keep the data in FM updated with the data that goes into excel daily or anytime a change occurs in the production schedule.  I want to import only the data that has been added or changed.  Everytime I try to import new data it imports what is already there and what was added.  Can someone help me figure this out?

This forecast has to be done in excel not FM as others utilize the data in it for other purposes.  

here are the files I need help with. Certs-Combined-5-9-2011.xlsx Copy-certs-5-9-2011-Converted.fp7
0
topgun0621
Asked:
topgun0621
  • 5
  • 4
1 Solution
 
Will LovingPresidentCommented:
In order to import only data that has changed or is new, you need to have a way for Filemaker to match incoming records against existing records. This is usually accomplished by have a unique record identifier or serial number, so for instance you could have a column in the spreadsheet that has an auto-incremented serial number that is always unique and never duplicated. The FileMaker table also needs to have a field for this serial number or identifier.

When you import the data, instead of doing a normal "Add Records" import, you use a matching import, setting the Serial Number in FM to = the serial number in the spreadsheet. You would then check "Update matching records in found set" and also check "add remaining data as new records". That means that any records in FM that have matching serial numbers will be updated and any incoming records (rows) in the spreadsheet that do not have a matching serial number in Excel will be added to FM and be available for matching in the future.

 Import Matching dialog
As an additional tip, I suggest greatly simplifying your file and table names. There is no reason to have the word "Converted" in the table or file name, it is just something the FileMaker tacks on when it does the conversion to FM 7+ format. It's also probably best to not include the date in the file name. You're only using one FM file and table right now, but if you ever change that have long file and table names can be confusing. I would revise your FM table to something simple like "Certifieds" and your FM file to something similar.
0
 
topgun0621Author Commented:
yes I agree on the file names, I only named it what I did so that I knew that new data was pasted into the excel file, and in FM also. The orginal files are like you have have suggested.  Going to try the serial format like you suggested.
0
 
topgun0621Author Commented:
i see it is possible to create a auto number, however, once I copy and paste the new orders into thecertified tab, I will be performing a sort in the weight column from smallest to largest.  This allows me to look up product quickly (by the weight) and add the neccessary heat numbers once testing is completed.  Will this mess up the serialization of the sheet?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
topgun0621Author Commented:
I created a macro for this serialization, however, FM will not let me import this macro enabled file now.  Any suggestions?
0
 
Will LovingPresidentCommented:
When I was playing with this, I just added a new column A and put in a series starting with and continuing down the column way beyond where your current records are. You can always extend the series is need be later on. It's not elegant but short of working within FileMaker directly for entering these records - which I'm guessing might be your ultimate solution, it will do.
0
 
Will LovingPresidentCommented:
Regarding the sort order. As long as you have a non-formula serial column and you include that with your sort, so the serial number stays with the order, then it should work. Alternately, if these are indeed 'orders', the presumably each order has a unique order number. You can use this instead of a serial number but it then become incumbent on you to make sure that the order number is always unique and never mistyped.
0
 
topgun0621Author Commented:
Yes I need the unique number to stay with each line added.  We make multiple products that are the same but on different days. Example:Monday-25 roll of 2231#rolls, we will make these and then stack outside untill customers orders them.  Friday we make 20 more because customers commited to 20 of the 25 we produced Monday.  So we make more and these get stacked outside with the rest.  However, this is treated asa different batch of lot, therefore testing data will be different.

So the macro does this just fine but I cannot import the file into FM because the worksheet is macro enabled.  

So now what do I do?  Everything works just fine but importing?
0
 
Will LovingPresidentCommented:
See my previous note about adding serial numbers. Don't use a macro, just start a series - 1,2,3,4 - then select those cells and extend the series in the column way beyond what you would ever need. Then you will have a static, non-macro serial number that you can use to import with.
0
 
topgun0621Author Commented:
Ok will try that.

thanks willmcn
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now