5 Replies Latest reply on Dec 6, 2011 9:42 AM by Stephen Huston

    Troubleshooting an unusual import

    user

      I need to merge to sets of records the primary has 60k and is in fmp format the secondary has 16k and is on excel. For convenience sake I'll refer to the fmp set as primary and the excel set as secondary. the primary set has no duplicates in itself neither does the secondary set but there will be when i import the secondary set into the primary . I was thinking i could import the secondary into a separate fmp set then mark all duplicates and separate all marked records into a separate window. If anyone wouldn't mind giving their two cents i'd greatly appreciate it.

        • 1. Re: Troubleshooting an unusual import
          BruceHerbach

          If there is a matching key or key field,  you can import the first set.  Then import the second set, match on the key field and have it update matching records,  and add remaining records as new records.

           

          Bruce Herbach

          • 2. Re: Troubleshooting an unusual import
            user

            I'm importing the secondary into the primary, sorry bout that i'm fairly

            certain i forgot to mention that .All fields in the secondary match a field

            in the primary but not the other way around, but there's no way to ensure

            that there would be no duplicates in the primary set after the import if it

            was only based upon a single key field. What I'm trying to do right now is

            "Mark" all new imports into the Primary database initially or set up a

            relationship between the two tables after i convert the secondary into

            filemaker pro format, and through that relationship not allow any

            duplicates to enter or if they are simply mark them. Is that in anyway

            possible?

            • 3. Re: Troubleshooting an unusual import
              pthomas

              When you are importing the data from the secondary into the primary you can have the import match on any number of the fields.

               

              So if your unique identifier for each record is made up of a combination of multiple different fields just match on each of them.

               

              Is there any reason why when importing a record that already exists you wouldn't want to simply update that record in the primary table?

               

              Cheers,

               

              Paul.

              • 4. Re: Troubleshooting an unusual import
                RayCologon

                Hi user,

                 

                As others have said, the "Update matching records in found set" import action, along with the associated "Add remaining data as new records" option would seem to match what you've said you are trying to achieve.

                 

                That is, you can define what you mean by 'duplicate' records by setting one or more fields up as the match fields for the import. If the data in the primary and secondary sources is the same in all the fields you designate as match fields, the relevant secondary record won't be imported as a new record but instead, data in fields marked for import will be written into the existing (matching) record.

                 

                If that's not the behavior you want, then yes, you could set up a "custom" import screening process where you first bring the secondary records into a utility table, then have a script loop through the utility table vetting each record according to whatever logic you choose (including whether a corresponding record exists in the primary table, deleting those it deems to be surplus to requirements, then importing the remaining records from the Utility table into your primary table and deleting them from the Utility table.

                 

                Assuming that your excel 'secondary' files are in the same format each time, the whole process coudl be scripted so you simply point it to the file you want to process and the rest occurs automatically.

                 

                Regards,

                Ray

                ------------------------------------------------

                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia

                http://www.nightwingenterprises.com

                ------------------------------------------------

                • 5. Re: Troubleshooting an unusual import
                  Stephen Huston

                  Hey "user,"

                   

                  I have a similar situation with a marketing department which wants to add the results of an email campaign (recd in a predictable Excel format) into a FM file of Contacts but without entering duplicates to what is already in the file.

                   

                  My solution was to build them what I called a "blackbox" file (because they never see into it, just import to and export from via the only 2 scripts visible in the scripts menu. They import the Excel into it. That script imports, checks the match fields used for identifying duplicates via a relationship to the ultimate FM Contact file, uses a looping script to flag those not to import, then allows the import into Contacts of the unmarked records only, or uses the flag to delete the flagged records at the end of the final import script.

                   

                  The second import script (FM-to-FM) is actually in the final target file, but it's called from the blackbox file.

                   

                  My "blackbox" utility  is not even on the FM server. It resides on the client machine of the person who performs this process, so the Excel import into it is faster than with a served file, and even the read time for the final FM-to-FM import is improved some.