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