Mapping SQL Database - Migration

Issue #453 new
Ben created an issue

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. 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`
    ;

Comments (0)

  1. Log in to comment