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.