When designing data structures for CRM, we work hard to create data formats that are usable and flexible enough for the needs of all users. However, when a CRM is integrated, there can be data formatting conflicts.

Consider a situation where there is a list of Client Codes stored for each contact in Creatio. This might be because the backend system has multiple client codes assigned to a given contact based on their customer setup.

To exchange this Client Code information correctly between Creatio and that other system, the data needs to come across per contact, which makes a comma separated value list most convenient when exporting from the back office system. When it comes to reporting and queries in Creatio, we need this same data to be stored in a better format so we can run the necessary analysis. A large section of text with commas is not optimal to count/summarize information.

For illustrative purposes, we will set up a field on the contact level called “Client Codes”. This field can hold the comma separated values at the contact level. This field is expected to be populated by an integration process that updates these values each day. The data eventually needs to be restructured so that it can better utilized for CRM reporting.

client code image 1

To accomplish this, we need to create an object named UsrClientCodeList that will hold the actual CSV values, but as individual line items so that each line has the client code associated with a contact. Lets create this object now.

usrclientcodelist

In this object, we need to create two columns:

  1. A Contact lookup column named UsrContact that allows for linking to the Contact record that we are working with
Contact lookup column UsrContact

2. A text column named UsrClientCode that will contain a client code for a given contact

UsrClientCode column

Now that we have the object set up, we need to update the Contact page to show this information in a list. Using the no code designer, it is easy to set up a list and populate it directly on the contact page.

contact page

Now that we have a place to store list of client codes, we can proceed to building a business process that can take a comma separated value like “AJ15,BM46,JH68” for a contact named James Smith and process it in a way that stores it in the UsrClientCodesList object we created earlier. In theory, the value of AJ15,BM46,JH68 should be processed such that it gets stored as multiple lines in the UsrClientCodesList object and logically looks like below:

James SmithAJ15
James SmithBM46
James SmithJH68

With this list-like format, we can now query based on the number of codes, count the number of codes etc. using the inbuilt reporting and dashboard features.

Lets set up the business process that can handle this.

update client codes process

The first step involves reading the ClientCodes comma separated value for a given contact. So each time we add or modify a contact, we should look for any changes to the comma separated value and prepare to retrieve it for processing.

read data process

Once the comma separated value has been read, we can clear out the current list of client codes for this contact by adding a Delete step to clear out data for this specific contact.

adding delete step

The final step is to add a low code script that will split up the values and add them to UsrClientCodeList object.

low code script

There are two variables you may be wondering about. They are actually parameters that are defined in the process for convenience in holding the values we are processing with.

CSV parameter

So now we have all the necessary pieces in place. Let’s try to update a contact and check what this process does. After testing, it should process the list of comma separated values and populate the codes on the Contact page.

process outcome

Now that the list of comma separated values is showing up in the Client Codes list, it becomes a lot easier to query it for existence of a given code, or count the number of codes as the system can automatically perform these operations using the built-in filtering functions and generate a needed data visualization for your reporting purposes.

Posted in:

Looking for Creatio help?

We do training, customization, integration, and much more. Contact us today.