1 Topic by apmuthu (edited by apmuthu 10/12/2014 12:01:12 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Topic: Customers Import WebService

Visit my FA Utilities page for FA related Utilities as WebService.

Visit the Customer Import SQL generation page and input the Company Number (without the underscore) and a tab separated Vales text file containing the customers you wish to import.

Use the generated SQL in a MySQL client in the relevent database.

The input file should be a tab separated value file.
Have No headings
Must have .tsv file extension
File Size < 30Kb.
Nothing is stored on the server
Uploaded temporary file is parsed on the fly.
Only addslashes is used to clean input
No CR/LF in any of the fields

Fields in Order:
NickName
FirstName
LastName
Address
Phone
Email
NRIC
Gender

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Customers Import WebService

This has been supplemented with a pure SQL means documented in the Wiki.

apmuthu's Website

  • From: UK, Norfolk
  • Registered: 12/03/2014
  • Posts: 28

Re: Customers Import WebService

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

4 Reply by apmuthu (edited by apmuthu 12/12/2014 08:00:53 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Customers Import WebService

@geoffreywalton: Thanks for the checkout.

The payment_terms sql typo stands corrected as also the heading for the persons import para.

AUTO_INCREMENT=@person_id also does not work. I too actually used only hardcoded values and assumed that expressions would be acceptable. The wiki article now stands corrected with an assignment of zero (0) value that automagically acquires the correct value.

Which fields in which statement(s) is not in the same order as those in the FA table definitions? Attached one instance that differs from your post.

Being a pure SQL solution, skipping the header was not an easily accommodate choice.

The reason why the surname was not included was that during creation of a new customer in FA, the branch gets auto populated with the same value as the Customer Name for the "name" field and blank for the "name2" field. These sqls were designed to mimic the default FA behaviour. Subsequent edit of the branch and the associated contacts is where the "name2" can be added in. This will be evident when you import multi branch customers where an extra HQ branch is created first where no surname (name2) is envisaged, but rather a space for accommodating longer client company names. This can however be mitigated by selectively using the name2 field for the extra branches added alone.

No CSV import was done in phpMyAdmin - only tsv (tab separated values) was used in LOAD DATA LOCAL constructs. The usage of linefeeds (\n) especially in the addresses will need to be checked.

The currency used is also only the default company currency and individual cases can be SQLed in or manually edited if only a few.

The first iteration of sqls was to add in the customers one by one, take the last inserted id and then progress to the next table - it was very slow and painful (SAP!).

The last 4 fields of the temporary cust_list table are declared as DEFAULT NULL as they will have to be filled in manually (or possibly with some more sql magic) instead of by some tsv import, though in my case I manually did it in excel and then imported it.

The debtor_ref and branch_ref fields are VARCHAR(30) fields that have to be manually prepared to be unique and meaningful generally alphanumeric being similar to the actual Customer / Branch names without spaces and special characters (hyphens and underscores allowed) since the dropdown boxes have limited width onscreen.

Using a MySQL client like SQLyog (community edition is free) makes it a lot easier.

Post's attachments

Field_Order.png 67.1 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

5 Reply by geoffreywalton (edited by geoffreywalton 12/12/2014 08:38:58 pm)

  • From: UK, Norfolk
  • Registered: 12/03/2014
  • Posts: 28

Re: Customers Import WebService

Thanks for the reply.

I just created the site in the last 2 days using the current download and installed an English COA during the installation.

I could send you a screen shot showing debtor ref as field 16, but here is a cut and paste from phpmyadmin

15     inactive     tinyint(1)             No     0        
16     debtor_ref     varchar(30)     latin1_swedish_ci         No     None

On my installation debtor_ref and cust_branch are the last field so the instructions in the wiki will not work for new installations.

Could it be the alter2.2.sql?

ALTER TABLE `0_debtors_master` ADD COLUMN `debtor_ref` varchar(30) NOT NULL;

&

ALTER TABLE `0_cust_branch` ADD COLUMN `branch_ref`    varchar(30) NOT NULL;

adding them as the last 2 fields?

Needs an "AFTER ....." added.

About a sample excel spreadsheet, for a new person to the system the explanation is overwhelming and an example to see how the explanation is applied would be a great help.

Cheers

G

  • From: UK, Norfolk
  • Registered: 12/03/2014
  • Posts: 28

Re: Customers Import WebService

Found it!!!

en_GB-general.sql

CREATE TABLE `0_debtors_master` (
  `debtor_no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `address` tinytext,
  `tax_id` varchar(55) NOT NULL DEFAULT '',
  `curr_code` char(3) NOT NULL DEFAULT '',
  `sales_type` int(11) NOT NULL DEFAULT '1',
  `dimension_id` int(11) NOT NULL DEFAULT '0',
  `dimension2_id` int(11) NOT NULL DEFAULT '0',
  `credit_status` int(11) NOT NULL DEFAULT '0',
  `payment_terms` int(11) DEFAULT NULL,
  `discount` double NOT NULL DEFAULT '0',
  `pymt_discount` double NOT NULL DEFAULT '0',
  `credit_limit` float NOT NULL DEFAULT '1000',
  `notes` tinytext,
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  `debtor_ref` varchar(30) NOT NULL,
  PRIMARY KEY (`debtor_no`),
  UNIQUE KEY `debtor_ref` (`debtor_ref`),
  KEY `name` (`name`)
) ENGINE=MyISAM;

I have not checked any other chart of accounts.

Cheers

G

7 Reply by apmuthu (edited by apmuthu 12/15/2014 04:25:56 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Customers Import WebService

@geoffreywalton: You're right! All CoA's should be synched with the standard US ones in FA. This is the reason why I wanted to separate out the schema from the INSERTs but the devs overruled it then on grounds of character encoding if I remember right.

All CoA schemas and the order of their fields in the INSERTs should be synched with the standard US CoAs present.

Also the upgrade (alter) sql(s) should be updated with the AFTER specific field wherever necessary.

apmuthu's Website