Link to home
Start Free TrialLog in
Avatar of topgun0621
topgun0621

asked on

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
Avatar of Will Loving
Will Loving
Flag of United States of America image

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.

 User generated image
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.
Avatar of topgun0621
topgun0621

ASKER

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.
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?
I created a macro for this serialization, however, FM will not let me import this macro enabled file now.  Any suggestions?
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.
ASKER CERTIFIED SOLUTION
Avatar of Will Loving
Will Loving
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
Ok will try that.

thanks willmcn