Export/Import: Split number of fields in exports into rows in another entity.

[murugappan]

Hi Everyone,

I faced a challenging export/import (not using extension) task in my recent re-engineering process. The following is the scenario:

In the old Account Entity:

(1) There are four fields "Email Address for OP", "Email Address for GL", "Email Address for CL" and "Email Address for IP".
(2) I want to export these fields in a csv file.
(3) This is used for sending email notifications to specific users in a company when our transaction system hits specific transaction types which the company wants to monitor

In the target Transaction Notification entity in the new instance:

(1) The each row is defined with the following fields:
- Email Purpose --> enum ["OP","GL","CL","IP"]
- Email Address --> email address
(2) When I import the exported CSV file i want "explode" the four fields and generate a row for each one of the fields.


The intentions for doing this are:

(1) Avoid exceeding table size limitation when more such item as the added in the accounts entity
(2) Provide user with the flexibility to create their own email addresses for additional purposes.

I am knocking my head against the wall on this. I know this could be done this using PHP external script and Espocrm APIs. But I am trying to keep all such tasks within the confines of Espocrm as integrated package.

Any help would be much appreciated. Thank you in advance.

[zerosix]

excel will do the work

[murugappan]

zerosix

Thank you for the advice. I not good at excel. Appreciate if you could show me an example.

[zerosix]

give me an input file sample, and the desired output

[murugappan]

zerosix sample attached.

[zerosix]

this is the input, i will need the output too

[murugappan]

zerosix the output is entity as shown below. It should create one record per entry in the csv file

[murugappan]

Hi zerosix Its ok, i have solved the problem. All I needed was some clear thinking. It was small bit of extra formula script coding and it worked. Thank you for your assistance.

[shalmaxb]

Always good practice to present the solution, could help others.

[murugappan]

What I did was a long-winded way but it was clear, simple and easy. This is what i did:

(1) Imported the fields into another entity which contains only these fields
(2) I developed a workflow which access each field and formatted a row into the target entity.

The workflow formula looks something like below. It may not make sense as it applies to my instance. Basic idea is

(1) Check the field for empty values (php empty() function does not seem to work)
(2) create a row in the target enitity and insert the values (formula provides both these in a single function)

if (tpaOpgl != '' && tpaOpgl != null) {
    $rourtetoId = record\findOne('Account', NULL, NULL, 'accTpaid=', tpaOpgl);
    record\create("InvoiceRouter",
    "accountId", oldAccountid,
    "routeToAccountId", $rourtetoId,
    "rtrApply2claimtypes",'["OP-2","OP-2R"]',
    );
};​