• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Import question

I set this file up like suggested but everytime I import it still imports all the data thus duplicating my data in FM.  What am I doing wrong?

This was the suggestion:

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.

0
topgun0621
Asked:
topgun0621
  • 9
  • 7
  • 2
1 Solution
 
Will LovingPresidentCommented:
Have you added serial numbers in the serial number field in the FileMaker file?
0
 
Will LovingPresidentCommented:
In order to match and update rather than import, the values in those fields have to match for each record.
0
 
topgun0621Author Commented:
yes created serial ID for the styles I had in the certifieds.  I also dragged the serial ID down for 40,000 records so that any time we add new ones it would already have the serial ID there.  I also added the serial ID in FM also.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
topgun0621Author Commented:
I wish i could just paste right into FM, is that possible?
0
 
Will LovingPresidentCommented:
If the records in FM are in the same order and an exact match for what is in the Excel spreadsheet, then you can go into FM, click in the serial number field and then go to Records -> Replace Contents...  Use the serial number option and FM will enter an incremental SN value in each field going from the first record to the last.

Alternately, if you have a unique ID such as a order number that is already in FM, you can use that as your matching field.
0
 
challengedayCommented:
Here's a screenshot of what your Import dialog should look like:

 import.JPG
Make sure you
1. Choose "Update matching records in found set"
2. Click on the arrow between the source field and the target field of the field you want to match until it becomes an equal sign (It cycles through import field, dont' import, match.)
0
 
topgun0621Author Commented:
is it possible that you can complete this procudure...I have tried and tried and something is not right. I  have  deleted all the records now which is not good as we have added to some of the fields and now we are going to have to add to them again.  (wire size, results for L/W and C/W)

Enclosed is the FM file and the excel file where we are getting the data from.
again we copy and paste what we type in the forecast right into the certs sheet.

Thanks


 Daily-Paperwork.xls Certifieds.fp7
0
 
challengedayCommented:
I took a look and it looked like all of the data from the spreadsheet was already in the database. I ran the import anyway and it updated only one record (Serial ID 1443).

Here is the import I ran and the updated db.

 import2.jpg

Certifieds.fp7
0
 
Will LovingPresidentCommented:
I used your two files and ran an import/Update with Add remaining records. It worked fine and when I added an additional record to the spreadsheet and updated again, that record was added. I have saved the import configuration into a script under the Scripts menu.

By the way, when you are doing this kind of testing and figuring out, it's always good to work on a test copy of the data rather than your production files.

Certifieds.fp7
 Daily-Paperwork.xls
0
 
topgun0621Author Commented:
well you are a genuis cause that works like a champ.  What did I have wrong out of curiosity?
0
 
topgun0621Author Commented:
In the begining I was using limited data, but my head hurts from trying to figure this out so I just uploaded the whole file.

This is a program that got thrown on us that we never used to do.  It was always hand typed... every field.  The sales people no how to type, the three of us do not.  I just did some research and figured out that there is an easier way to do this procudure. I started watching some videos and looking online, but everything I was doing is trial and error. Very hard to do while production is running if you know what I mean.  I was trying to do some things at home also.

Again what you did was awesome and i appreciate it.
0
 
Will LovingPresidentCommented:
I'm not sure other than  possibly some setting in the Import. If you're just figuring this out completely on your own without background, you're doing great.
0
 
topgun0621Author Commented:
Well, after updating our orders for the past 3 days now it duplicates again lol.  I know it works because I tried it.  I am going to have to redownload what you did and try to fix it tomorrow.

There has got to be an easier way to do this.  Any suggestions?  Such as all in FM perhaps? frustrating that I only use 3 sheets and endure all these functionalty problems.  
0
 
topgun0621Author Commented:
very informative and patient with my problems.
0
 
Will LovingPresidentCommented:
Yes, I would do it all in FM. That would eliminate duplicate and import issues entirely. You would probably want some additional functionality, but I've modified one of your layouts to be in Table View and look similar to your spreadsheet.
Certifieds.fp7
0
 
topgun0621Author Commented:
my issue with using FM for everything is the first sheet we use "forecast"  because we continually add and delete lines all the time.  That sheet means nothing in the tracking aspect, its just an informative sheet that is typed and printed so that everyone knows what we are running and planning to run.

To me however it is kind of the "bible" because everything that is typed on there is an actual "Lot" of wire that has many different descriptions and weights.  This "Lot" of wire is what needs tracked as we need to provide the customer with just one of the heat numbers for the Longwire (L/W) and one of the heat numbers for the crosswire (C/W) as well as one of the results of each of the yield and tensile.  Some weights and descriptions are common items we stock in inventory, but more often than not they are completely new descriptions that i have never seen.  To make it worse we have no "Product Code" for these styles of mesh we manufacture.  The weight is used as this "product code" which creates a problem because there are many weights that are the same but the description is different.  I have brought this issue up many times explaining that a product code would simplify many aspects of our business but to no avail.  No one wants to change...common in industries it seems.  This is how they been doing it for  years.
 
0
 
Will LovingPresidentCommented:
Sounds like a you could implement this in FM with your forecast sheet, and then mark those records as completed with a time/date stamp. Then there would be no re-entry or re-typing.

You could also thinking about whether there is a way that you could implement your own Product code for tracking purposes. If you can organize the data in a way that helps you and eventually makes things more efficient for the company then maybe it would eventually get adopted...sorting of sneaking it in the back door by making it so useful to them that they can't ignore it.
0
 
topgun0621Author Commented:
since I am am not that proficient with FM perhaps I should just create a table for the weights and descriptions, and customers only.  This is the longest part of the certification process and the most error prone part of the process as well.  We are not typist after all.  We can simply grab the 6 points of data that we need from the orginal spreadsheet in Excel and type into FM. In my spare time I can try to set everything up in FM like you suggested.  The date/time stamp is an interesting approach and may be doable but will need to tinker with it some to get famaliar with it and of course send more questions your way no doubt on issues I am having .LOL  Whats your thought on just using two tables?  A little easier or willl I run into same issues of importing?
The original meeting that took place on this certification process was my doing actually.  When the question came up about the process I explained to my bosses that the certification is complete when it is in my drawer back in my office.  The fact that we retype into a prettier form is redundant.  This was my explanation and 4 days later they threw the whole procedure back here in the QC department.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now