Viewing records that WERE NOT imported

[Kristen]

I frequently import records from Excel files and fp5 files. I update records in found set, based on a matching field. However, if I have 50 records in my source file, and only 46 can me matched, and therefor updated, is there anyway to "display" somehow the records that WERE NOT updated? As it is now, I export the updated records to Excel, and match the data until I find the missing records. This is really inconvienent, and I am sure there is a much better way. Thank you...

[Lee Smith]

Don't you have a modification date field?

[Kristen]

Yes, but my problem is isolating those that WERE not updated, therefore not modified.

[Jim McKee]

Hi Kristin ...

Just to clarify:

You are updating matching records, but you are NOT importing records that don't match on the matchfield (the "Add other records" option in the Import options is NOT checked). And you want to identify the records in your source file that did not match on the matchfield, and therefore, were not imported. Correct?

I'm assuming this because your post is titled "Viewing records that WERE NOT imported".

What version of FileMaker are you using? How many records are in a typical import?

[Lee Smith]

Find on the modification date and omit, this will leave the ones that were not modified.

Lee

[Kristen]

Jim: Your scenario is correct. I am using FM 6.0 A typical import is about 200 records. Let me explain more clearly. Clients send me sale information for vehicles that they have sold. These records already exist in my database, but I need to import their sale information. The matching field I use is VIN number. But because a typical VIN number is 17 chracters long, there are some VIN's that do not exactly match, therefore, records are not updated. If the VIN does match, it updates the existing record with the sale price, and the comission fee. Here is my problem. I have 200,000 records in my database. If a client sends me a file for 180 sold vehicles, it should update 180 records. But if it only updates 175, I am wondering if there is a way to know what 5 (from the source file) did not update.

Thanks for your help.

[Jim McKee]

Hi Kristin ...

If you were only importing from .fp5 files this would be simpler, but since you also import from .xls files, here's my solution:

1. Make a clone (copy, no records) of your existing db. This will serve as an intermediate file. Let's call it "Temp.fp5".

2. Create a new text field in your existing db called "ImportID_VIN key". Turn indexing On.

3. If you don't already have a "Constant" field in your existing db, create it. This is a calculated field, type Number. In the formula window just enter 1 (i.e., the number "1"). In the Storage options for the calculated field, turn indexing On.

4. Create a new field in Temp.fp5 called "ImportID", and also create a calculation field called "ImportID_VIN key". Enter the formula: ImportID & "_" & VIN, and make the output type "Text".

5. Create a Relationship in Temp.fp5 linking the "ImportID_VIN key" field with the same-named field in your existing db. Let's call the relationship: "import Match"

6. Create a calculation (type: Number) in Temp.fp5 called "Import Match". Enter the formula: Case( IsValid(Import Match::Constant), "", 1). Put this field on a layout in Temp.fp5.

7. Import ALL records from your source file into Temp.fp5. This will be a full import, with no matchfield, so new records will be created for all records in your source file.

8. Go to the ImportID field of the first record in Temp.fp5 and enter "0001". Then, from the Records menu select "Replace Contents", so all records receive the same ImportID.

9. In the Import process in your existing db, keep your existing matchfield setup, and configure it to also import the "ImportID_VIN key" calculation from Temp.fp5 into the "ImportID_VIN key" Text field in your existing db.

10. Import the records from Temp.fp5 into your existing db. Then, return to Temp.fp5 and do a search on the "Import Match" field for the value "1". The found set will be the records that did not match a VIN in your existing db.

11. When you are finished examining, fixing, or whatever you do to the "unmatched" records in Temp.fp5, you can delete all records in Temp.fp5 so it will be ready for your next import process. Also, unless there is a compelling reason to keep the data in the "ImportID_VIN key" field in your existing db, you can clear this field as well.

It sounds more complicated than it actually is. Once you create your fields and the relationship, this whole thing can be easily scripted, including incrementing a global field in Temp.fp5 that would be used to automatically assign an ImportID to the records in Temp.fp5. See attached demo. The "new data.txt" file contains 400 records, 8 of which I've edited so they don't match any records in the Master.fp5 file. Click the "Import New Data" button on the Entry layout in the Temp.fp5 file to step through the import process.

Good luck!

VIN Updating.zip

[Kristen]

WOW! That is great. Thanks a TON for your help. I will try it and let you know how it goes. It sounds great!!

Kristen