We had an interesting Creatio use case recently. We needed to maintain a single value that was a combined list of all email addresses associated with each contact. Because email addresses are stored in separate records for each contact, it is not straightforward to extract this information as a single value associated with the contact. So it made sense to create a "calculated field" that would combine a contact's list of email addresses into a single semicolon separated list in the format “email1@test.com;email2@test.com;email3@test.com;”.
Once this value is present for a contact, it becomes much easier to extract each contact and their combined email addresses using a simple export process for later use in reporting, or for feeding to an email marketing process.
The basic idea is to create a business process that triggers each time an email address is added to the contact in Creatio. For this, we have to trigger off the "Contact communication option" object so we know that a new email address was added. When this happens, we then determine the contact associated with the newly added ContactCommunicationOption record, and then process all the available email addresses into a single field.
First, we create a field at the contact level that will contain the combined list of email addresses for a contact. We call this the "Combined Email Addresses" field.
Part 1:
We start with creating the main business process in Creatio. Here is how it would look:
First, we must make sure we start with the appropriate trigger that fires off when a new Contact communication option record is added. Make sure there is a filter on the TYPE field to only look for "Email" type records.
Next, read the contact Id associated with the communication option record that was just added.
Now we need to add a step to clear out the Contact's CombinedEmailAddresses field before it processes the full list of email addresses.
Next, read ALL communication option records (i.e. all email addresses) associated with the current Creatio contact.
Finally, call the sub-process that does the update (for each of the email addresses). This part is interesting because there is no visible looping/cycling through each of the records. It looks like a straight process, but it somehow handles a "bunch" of records automatically. How does this happen? Well, the sub-process takes a collection of (Communication option) records and therefore knows it has to repeat the sub-process operation for EACH record it processes. So, if there are three email addresses, the sub-process gets called three times, one time for each Contact communication option record supplied.
Here are the settings for the call to the sub process. You'll need to go to "Part 2" of these steps to set up the sub-process before you can finish this step.
Part 2:
Now, we need to create the sub-process that handles the update to the contact record.
First, the supplied Communication option record (from the main process) needs to be read, to get the associated contact record via its contact Id.
Next, query the Creatio contact record to get the current value of the CombinedEmailAddresses field.
And finally, update the CombinedEmailAddresses field with existing value plus the new email address obtained.
That should be it! If this is implemented correctly, you should see the CombinedEmailAddresses field be updated automatically when a new email address is added to a contact. Here is an example of a contact who has had 3 email addresses added:
And here is how the combined email addresses value has been populated.
As you can see, the same approach can be extended to "cycle" through a list and generate an aggregate (sum) of numbers, join together different pieces of text on multiple records, etc.