Jump to content


Photo

Mapping SQL Database - Migration

sql migration database

  • Please log in to reply
1 reply to this topic

#1 Benbison

Benbison

    Member

  • Members
  • PipPip
  • 19 posts

Posted 21 May 2019 - 12:08 AM

Has anyone successfully mapped the database to be able to migrate to another CRM.

It seems to be a very difficult database to humanly read.

 

I am looking at migrating to SuiteCRM.

 


  • RidgeGymn , BrianDow and FernizBuilm like this

#2 Benbison

Benbison

    Member

  • Members
  • PipPip
  • 19 posts

Posted 21 May 2019 - 01:13 AM

I have started creating mysql views for accounts to map to suiteCRM.  but there are a few fields that I cant find.

 

  • date_entered,
  • Modified_user_id,
  • created_by,
  • deleted,
  • assigned_user_id,
  • ownership,
  • employees,
  • phone_alternate,
 
I can see industry is stored in the `customfield` table, I just need to do a join to get this and not upset the `account_type` as that is stored in here as well.
 
CREATE OR REPLACE VIEW `suiteCRM_accounts` AS
	SELECT	
		`zurmo_zurm895`.`account`.`name` AS `name`,
		`zurmo_zurm895`.`account`.`latestactivitydatetime` AS `date_modified`,
		`zurmo_zurm895`.`account`.`description` AS `description`,
		`zurmo_zurm895`.`account`.`annualrevenue` AS `annual_revenue`,
		`zurmo_zurm895`.`account`.`officefax` AS `phone_fax`,
		`zurmo_zurm895`.`address`.`street1` AS `billing_address_street`,
		`zurmo_zurm895`.`address`.`city` AS `billing_address_city`,
		`zurmo_zurm895`.`address`.`state` AS `billing_address_state`,
		`zurmo_zurm895`.`address`.`postalcode` AS `billing_address_postalcode`,
		`zurmo_zurm895`.`address`.`country` AS `billing_address_country`,
		`zurmo_zurm895`.`account`.`officephone` AS `phone_office`,
		`zurmo_zurm895`.`account`.`website` AS `website`,
		`zurmo_zurm895`.`address`.`street2` AS `shipping_address_street`,
		`zurmo_zurm895`.`address`.`city` AS `shipping_address_city`,
		`zurmo_zurm895`.`address`.`state` AS `shipping_address_state`,
		`zurmo_zurm895`.`address`.`postalcode` AS `shipping_address_postalcode`,
		`zurmo_zurm895`.`address`.`country` AS `shipping_address_country`,
		
		`zurmo_zurm895`.`customfield`.`value` AS account_type
	
	FROM `zurmo_zurm895`.`account`
		LEFT JOIN `zurmo_zurm895`.`address`
			ON `zurmo_zurm895`.`account`.`billingaddress_address_id` = `zurmo_zurm895`.`address`.`id`
		LEFT JOIN `zurmo_zurm895`.`email`
			ON `zurmo_zurm895`.`account`.`primaryemail_email_id` = `zurmo_zurm895`.`email`.`id`
			AND `zurmo_zurm895`.`account`.`secondaryemail_email_id` = `zurmo_zurm895`.`email`.`id`
			AND `zurmo_zurm895`.`account`.`shippingaddress_address_id` = `zurmo_zurm895`.`address`.`id`
		LEFT JOIN `zurmo_zurm895`.`customfield`
			ON `zurmo_zurm895`.`customfield`.`basecustomfield_id` = `zurmo_zurm895`.`account`.`type_customfield_id`
	;

  • RidgeGymn , WilliamKA , BrianDow and 3 others like this





Also tagged with one or more of these keywords: sql, migration, database

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users