1 Reply Latest reply on Apr 25, 2013 9:36 AM by philmodjunk

    Help with Updating an Old Company Solution



      Help with Updating an Old Company Solution


           Our company has been using a FM solution that is back from FM3.  All of our computers now no longer run the old FM software as we are now on Intel based macs and the networking of the old database is lacking so we have been running it though Citrix for way to long.  I am now in the process of updating the system to take advantage of FM12 with all the new features and iOS support.  I have a good start on recreating many of the tools and am now working on setting up the database for the eventual migration.  I have started breaking up the database into Data, UI, Reports and Script files to make future migration possible.

           My first step is to work on migrating the buisness contacts into the new solution.  I am able to get the data into FM12 by importing to FM11 then FM12 and have the basic solution working with this file, but I have 2 problems.

           1 - We will need to keep the old system live during migration so exporting all the data and importing it into the new solution will not work.  I need a way to export the data then import the data into the new solution, then be able to repeat the process later to update the new solution over the corse of migration to update the changes in the old solution in the new.  I know this is possible, I just need help pointing me in the right direction for advise.  My basic plan is to have one file called "Contacts" look at the "Old Data" file as an external data source and have the "UI" File look at the "Contacts" file for data, but I need help with the scripting to move the data in a way that I can also update it later with new "Old Data" files.  I do not need to migrate the changes in the new system back into the old system, thank fully.

           2 - During the process of this migration I also need reorganize the original data structure.  This is the main purpose of creating a new database called "Contacts" and not just using a sync solution.  The original file had People and their Company information as one record.  I would like to break these into different tables (People & Companies) as we track many people at the same company and seperating these items would reduce data entry and errors.  Will also allow a portal in the Company table display all their employees and we can track when a person goes from one company to another.  We also contract with the company, not the individual, so looking at data based on a company is more usefull then an individual.

           The old databas also has a problem as it was created before email was the primary communication tool so the contacts email address has been saved in a phone number field.  To make matters worse, the phone numbers was not assigned a specific field in the database.  Each phone number is basically two fields.  Phone Number # and Phone Number # Description (A drop down menu where you pick what type of number it is) and there are seven such instances, so an email address can be in any one of the 7 phone number fields as can the fax, cell, pager, car phone, nextel #, main number, direct number, etc.  I don't think carrying this forward is a good idea.  If it was just phone numbers, I would consider it, but having the email address in a random spot makes using email features a problem.  Any advise?  Like what scripts to learn how to use to resolve.

           Any suggested fields to add to a contact now for the furure?  Going to add all the current social media platforms as these may be needed sooner than later, just like email in the old system.

           Thank you all for your help.

        • 1. Re: Help with Updating an Old Company Solution

               1) Take a look at the added features for importing records. Theres an "Import Matching Records" option you can use if you first show all records in the target table, then import specifiying a primary key field (auto-entered serial number in most cases) as the "match" field, with the option to add a new record if there is no match selected.

               2) I don't really see a question here, you've just described what you plan to do. A script can loop through the data as it now exists and move date from the company fields into a new, related company table, generating new records as needed. But be careful as you will only have a company name field to uniquely identify the company. Like all names, company names are not unique, subject to change and vulernable to data entry errors. Though your script can do the heavy lifting here, you'll need to review the results for possible issues in the data thus split off into the related table. You might keep the old fields in place, but hidden from view in order to "debug" data issues with any contacts that don't split off the company data perfectly.

               And your phone number/email address issue is much the same as 2) above. When setting up a contacts system, I prefer to define a related table with just those two specific fields. I then put phone numbers, emaili addresses, social network links all in the same field of different related records with the "description" field used to identify which are which. That shouldn't take too complex of a script to generate from your 7 pairs of fields.