import match

[rogermax]

Can anyone tell me if it is possible to import xml and have the records import data only if there is a match of one of the fields? If so, what is the key? Thanks

[Mariano Peterson]

Yes, it is possible. Choose File > Import, then choose an XML file that conforms to either the FXP_XML or DSO_XML grammars. From there, the rest is identical to the process of importing from one FileMaker file to another. In the Import Field Mapping dialog, just choose"Update matching records in current found set", and make sure to uncheck the "Add remaining records" option. Finally, to specify your match field(s), just click in between the fields you want to match, and keep toggling the Map icon until it is a double pointed arrow.

That's it! You can try this easily by exporting your data from one FMP file using the FMP_XML grammar and then trying to re-import that same XML file.

[DanBrill]

The real trick is when you need to get rid of records in your database that don't have a matched record in the dataset you are importing in order to keep 2 data sets in sync. (This works for xml imports or any kind of imports.) In this case you essentially do the import twice.

First you import the data you need using the "update matching records in current found set" option. You also import one additional dummy field, set to 1 in the data you are importing, and empty in the dataset you are importing into.

Then you create a self join relationship on whatever field you are matching on. That is, each record will be related to its matching record. Create a calc field that is count of the number of records in the self-join relationship. So if a record has no match, the count is 1. If it has a match, the count is > 1. Find all the records where the count <= 1 and delete them. Then find all the records where the dummy field = 1. Delete these.

What you are left with is your original set of records less those records that won't have a match in the data you are importing. Then import the data one more time with the "update matching records in current found set" option. Now both sets of data are in sync.

I've done this to keep lists in sync between QuickBooks and FM. And if memory serves me, you have a deadline approaching, no?

Dan

[rogermax]

Yes my deadline is today. I made it. I think I got the problem worked out in synchronizing the customer lists between FM and QB. The trouble came in that the FM list may have NO matching fields with QB even though they are the same customer. Misspellings on either side would kill it, and of course neither side had the other's ClientID. So I worked out a scheme to do the import match to get the QB ListID for each matching FullName. Then the user will have to cycle through and match them, then click a button to synchronize.

I have learned a lot about the xml stuff in the last month but I have so far to go.