3 Replies Latest reply on Jan 23, 2017 9:42 AM by philmodjunk

    What is the best to merge 2 different files




      I have 2 files that need to relate. I would like to run reports on each of them so I would like to keep them in separate tables. My question may be very basic due to my beginner knowledge of FM and building databases.


      The 2 files are from my sales territory. It is an csv export coming from my employers CRM. The first file is accounts with basic information such as address and phone number and their existing software systems for each department. The second file is the opportunity history that various people have entered.


      When I exported the files I pulled the Account file to include a unique identifier called the "Legacy Field number". I intended to use this number as the link between the 2 files/tables. Unfortunately, the export was shabby at best. Some Legacy Field numbers were missing from the Account export and possibly 10% of the opportunities had a proper corresponding legacy field number. What I was able to do in a second export is pull the exact matching account name into the opportunities export. So each opportunity line has the correct account name. Can I link the 2 files using this exact matching account name? How do I create unique relationship keys with this situation?


      Please correct me if my logic is poor. I would like to run reports on the opportunities and accounts and this would be best with 2 different tables. Next I would like view these opportunities on a combined layout page via a portal and would need the opportunities to be the child in the relationship. Then I would like to be able to enter new opportunities from the combined layout view and have this properly reside in the opportunity table with a relationship to the Account Table. Finally, these opportunities are all separate line items for different products or services and have a corresponding contact person. Many times this contact shows up again and again with each unique opportunity and I would like to eventually create a separate contact table event.


      I hope this makes sense any help is appreciated.

        • 1. Re: What is the best to merge 2 different files

          It makes sense as far as it goes--a lot of detail is missing, but you really need to break this down and tackle one issue at a time. The first question appears to be, (correct me if I misunderstood): You have exported data from some other system into two files and you want this data to be in two related tables of the same relationship. You have an account name field that matches entries in the two files of exported data. Is this correct?


          If so, you can use import records to import this data into two tables of the same file. In Manage | Database, you can set up a relationship that links the records in the two tables by account name. Once the data is in place, you can work on your layouts and the portal you describe.


          And working with your imported data to link by a unique ID that is not the account name is also likely to be a worthy improvement to make to your original design once you have your data imported--but you'll need to let us know if this is a one time export/import or if you will be importing new data from this source on a regular basis.

          1 of 1 people found this helpful
          • 2. Re: What is the best to merge 2 different files

            Thank you. This is very helpful. To answer your question... this will be a import that will happen again with more and new data.

            • 3. Re: What is the best to merge 2 different files

              Then you will need to keep your relationship that matches records by account name in place. But if possible, I would use this relationship only during the import process via scripting or auto-enter options that leave the newly imported data linked by an account ID that is generated from within FileMaker. This makes for a bit safer relationship for linking the data in your two tables. Basically, you use the name during or just after import to (create if new first) copy over the needed ID--which you then use throughout the rest of your database. This requires using two occurrences of the same table so that you can have a relationship that matches by account name and also one that matches by ID.