apmuthu wrote:This has been supplemented with a pure SQL means documented in the Wiki.
Hi
Thanks for this, I tried it but ran into a few problems, there is a slight error in the sql
`payment_terms` INT(11) NOT NULL DEFAULT DEFAULT '1',
should be
`payment_terms` INT(11) NOT NULL DEFAULT '1',
The AUTO_INCREMENT commands such as
ALTER TABLE 1_crm_persons AUTO_INCREMENT=(@person_id+1);
gave me an error message and I never managed to resolve that one, just substituted numbers.
ALTER TABLE 1_crm_persons AUTO_INCREMENT= 1;
It is not clear that the phpmyadmin option to import data should be used.
It is probably easier to keep the header row in the file and use the option to skip 1 line during the import. Thus you will get the right number of columns if you have blank columns at the end of row 1.
Select CSV format and you may need to change the "Line Terminated With" to \n from auto.
Then I thought I must have got the set up values wrong as I got the error
#1062 - Duplicate entry '0' for key 'debtor_ref'
Finally worked out I had to move some statements in most of the commands to get the fields to be in the same order as the fields in the tables.
This worked for me.
INSERT INTO `0_crm_persons`
SELECT
`debtor_no` AS `id`
, `debtor_ref` AS `ref`
, IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
, '' AS `name2`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `address`
, `phone`
, `phone2`
, `fax`
, `email`
, '' AS lang
, IF(NeedPO, 'Need PO', ' ') AS notes
, 0 AS inactive
FROM `cust_list` WHERE NOT IsMB
UNION ALL
(SELECT
`debtor_no` AS `id`
, `debtor_ref` AS `ref`
, IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
, '' AS `name2`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `address`
, `phone`
, `phone2`
, `fax`
, `email`
, '' AS lang
, IF(NeedPO, 'Need PO', ' ') AS notes
, 0 AS inactive
FROM `cust_list` WHERE IsMB GROUP BY debtor_no)
UNION ALL
SELECT
`branch_code` AS `id`
, `branch_ref` AS `ref`
, IF(LENGTH(`Contact`) > 0, `Contact`, `BranchName`) AS `name`
, '' AS `name2`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `address`
, `phone`
, `phone2`
, `fax`
, `email`
, '' AS lang
, IF(NeedPO, 'Need PO', ' ') AS notes
, 0 AS inactive
FROM `cust_list` WHERE IsMB;
# Importing into #_debtors_master table
SET @currency=(SELECT MID(`value`,1,3) AS currency FROM `0_sys_prefs` WHERE category='setup.company' AND `name`='curr_default');
INSERT INTO `0_debtors_master`
(SELECT
`debtor_no`
, `CustName` AS `name`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `address`
, `TaxNum` AS `tax_id`
, @currency AS `curr_code`
, `Pricing` AS `sales_type`
, 0 AS `dimension_id`
, 0 AS `dimension2_id`
, 1 AS `credit_status`
, `payment_terms`
, 0 AS `discount`
, 0 AS `pymt_discount`
, `credit_limit`
, IF(NeedPO, 'Need PO', ' ') AS notes
, 0 AS inactive
, `debtor_ref`
FROM cust_list WHERE NOT IsMB)
UNION ALL
(SELECT
`debtor_no`
, `CustName` AS `name`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `address`
, `TaxNum` AS `tax_id`
, @currency AS `curr_code`
, `Pricing` AS `sales_type`
, 0 AS `dimension_id`
, 0 AS `dimension2_id`
, 1 AS `credit_status`
, `payment_terms`
, 0 AS `discount`
, 0 AS `pymt_discount`
, `credit_limit`
, IF(NeedPO, 'Need PO', ' ') AS notes
, 0 AS inactive
, `debtor_ref`
FROM cust_list WHERE IsMB GROUP BY debtor_no);
# Import the branches into #_cust_branch table
SET @salesdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_sales_discount_act');
SET @debtorsact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='debtors_act');
SET @paymentdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_prompt_payment_act');
INSERT INTO `0_cust_branch`
(SELECT
`debtor_no` AS `branch_code`
, `debtor_no`
, `CustName` AS `br_name`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `br_address`
, 1 AS `area`
, 1 AS `salesman`
, `Contact` AS `contact_name`
, 'DEF' AS `default_location`
, `TaxExempt`+1 AS `tax_group_id`
, `sales_account`
, @salesdiscact AS `sales_discount_account`
, @debtorsact AS receivables_account
, @paymentdiscact AS `payment_discount_account`
, 1 AS `default_ship_via`
, 0 AS `disable_trans`
, REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), " ", " ") AS `br_post_address`
, 0 AS `group_no`
, '' AS `notes`
, 0 AS `inactive`
, `debtor_ref` AS `branch_ref`
FROM `cust_list` WHERE NOT `IsMB`)
UNION ALL
(SELECT
`debtor_no` AS `branch_code`
, `debtor_no`
, `CustName` AS `br_name`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `br_address`
, 1 AS `area`
, 1 AS `salesman`
, `Contact` AS `contact_name`
, 'DEF' AS `default_location`
, `TaxExempt`+1 AS `tax_group_id`
, `sales_account`
, @salesdiscact AS `sales_discount_account`
, @debtorsact AS receivables_account
, @paymentdiscact AS `payment_discount_account`
, 1 AS `default_ship_via`
, 0 AS `disable_trans`
, REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), " ", " ") AS `br_post_address`
, 0 AS `group_no`
, '' AS `notes`
, 0 AS `inactive`
, `debtor_ref` AS `branch_ref`
FROM `cust_list` WHERE `IsMB` GROUP BY `debtor_no`)
UNION ALL
(SELECT
`branch_code`
, `debtor_no`
, `CustName` AS `br_name`
, REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), " ", " ") AS `br_address`
, 1 AS `area`
, 1 AS `salesman`
, `Contact` AS `contact_name`
, 'DEF' AS `default_location`
, `TaxExempt`+1 AS `tax_group_id`
, `sales_account`
, @salesdiscact AS `sales_discount_account`
, @debtorsact AS receivables_account
, @paymentdiscact AS `payment_discount_account`
, 1 AS `default_ship_via`
, 0 AS `disable_trans`
, REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), " ", " ") AS `br_post_address`
, 0 AS `group_no`
, '' AS `notes`
, 0 AS `inactive`
, `branch_ref`
FROM `cust_list` WHERE `IsMB`);
# Import the persons mappings into #_contacts table
# Each Customer (Individual and Multi Branch Headquarters) will have one customer and one cust_branch contact record for each imported into the debtors_master table
# Each Multi Branch Customer's non HQ branch will have one cust_branch contact record
# In the FA Web UI, the individual contacts can be edited for their Contact Names or alter the earlier (persons) sql to directly put in the Contact into the contact_name field in the #_crm_persons table
# The following sql will import the contact mappings:
INSERT INTO `0_crm_contacts`
SELECT * FROM (
SELECT
NULL AS id
, `debtor_no` AS person_id
, 'customer' AS `type`
, 'general' AS `action`
, `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`
UNION ALL
SELECT
NULL AS id
, `debtor_no` AS person_id
, 'cust_branch' AS `type`
, 'general' AS `action`
, `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`
UNION ALL
(SELECT
NULL AS id
, `debtor_no` AS person_id
, 'customer' AS `type`
, 'general' AS `action`
, `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)
UNION ALL
(SELECT
NULL AS id
, `debtor_no` AS person_id
, 'cust_branch' AS `type`
, 'general' AS `action`
, `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)
UNION ALL
SELECT
NULL AS id
, `branch_code` AS person_id
, 'cust_branch' AS `type`
, 'general' AS `action`
, `branch_code` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB`
) a ORDER BY entity_id, person_id, `type` DESC;
To make this easier to follow/understand could I suggest a sample excel file showing how to import customers with branches and how the debtor code and branch number should increment.
Finally having the Surname or name2 field as part of the basic import.
HTH someone.
Cheers
G
