Migrating from ACT! to Salesforce

From Starfish ETL
Jump to navigation Jump to search

Please review the Salesforce Connection Page before getting started with this map!

The Staging Database is a local Access database contained in the Zip file. If you are running into performance issues while running the migration, check the staging databases' indexes.

Map Download

This map will migrate ACT! to Salesforce.

Please be sure to read this page, the instructions.txt file found in the .zip file and the Project Notes found on the General tab within Starfish for this Project.

This map will read from the ACT TBL_Company records as well as find Companies in the Contact table that do not have a TBL_Company record. Many ACT databases have unique accounts found in both the Company and Contact tables. This map merges the 2 into a local Access database to then be inserted into Salesforce.

This map is set up to use the SQL versions of ACT. If you have the older flat-file version of ACT, a simple upgrade process can get you on SQL for the migration. Duplicate companies are merged into a single Company record, with contact records underneath it, based on Company Name + City though this logic could be easily changed to fit your needs. This is done through use of a temporary database file, included with the download.

You will have to create custom External ID fields in Salesforce, to allow for ID cross-referencing. The primary table field properties are defined in the included "instructions.txt" file. Not all tables are listed in the instructions.txt file. If you use those jobs, the new fields required will be obvious.

This map migrates Users, Accounts, Contacts, Opportunities, Notes, History, Activities and Attachments.

Download Now

Leverage the ActDiag tool to help find custom fields in the database.

Notes

  1. The Staging Database is a local Access database contained in the Zip file. If you are running into performance issues while running the migration, check the staging databases' indexes.
  2. The initial Staging jobs do not bring over phone number extensions. If you want the extensions, you will need to add it to the Origin, p1.SUFFIX AS PhoneExt, and then update the PhoneNo field mapping to be a Hardcoded Function Field with "@@ORG:PhoneNo@@ @@ORG:SUFFIX@@" as the Value. Salesforce stores phone and Extension in the same field, so you can merge them here.
  3. The Opportunity.StatusNum field in Act is an integer. I'm unsure where to find the definition of each integer, but by comparison, I found that: 0=Open; 1=Closed - Won; 2=Closed - Lost; 3=Inactive.
  4. This map does not automatically bring over the Activity Priority Codes. These can be found in the Activity Accessor table.

SQL

You may find these SQL statements useful for finding data in the ACT Database. These can be used to determine which phone TypeID maps to what Phone field in Act, etc. 

select typeid as addresstype, count(*) from TBL_ADDRESS group by typeid
select typeid as emailtype, count(*) from TBL_EMAIL group by typeid
select typeid as phonetype, count(*) from TBL_PHONE group by typeid

select * from TBL_SYSCOLUMN where DISPLAYNAME like '%phone%'
select * from TBL_SYSCOLUMN where REF_TYPEID like '%C3AE9586-6158-4D0D-9FAF-253CB0152F18%' 

select * from tbl_contact where LASTNAME = 'Curtis' and firstname = 'Drew'
select * from tbl_phone where contactid = 'A10DD7A0-9F9B-43B2-9835-E1C0D7963BF6'
select * from TBL_EMAIL where contactid = 'A10DD7A0-9F9B-43B2-9835-E1C0D7963BF6'
Retrieved from "https://wiki.starfishetl.com/index.php?title=Migrating_from_ACT!_to_Salesforce&oldid=5231"