Creatio

Important Notes about Creatio

• If you do not want to get duplicate City and State values in the City and Region Lookups, you must include a Country when inserting an Address.
• Capitalization differences will create different values in a Lookup. For example, if you insert Chicago and CHICAGO, you will get two values in the City Lookup.
• Creatio always adds commas to integer fields. For IDs and Member Numbers you want to display without a comma, you should use a Text field.
• If you are attempting to do something and you get a foreign key constraint or similar error, run this query with the specified constraing_name. The issue most likely has to do with a Lookup.
  • SELECT * FROM information_schema.constraint_column_usage WHERE constraint_name = 'F8y79b61A5DN4tsM3gX2IPy1yeLHo';

Creatio Connector Versions

StarfishETL has two connector types for Creatio.

OData: Allows for more complex querying. 4-6k records per hour

Data Services: Faster than OData. Approx 30k records per hour

Creatio (OData) Advanced Filtering

Sometimes it is necessary to filter Creatio in ways that are not possible through a SQL statement. You may use the 'Filter' virtual field to override the filter that is sent to the Creatio OData web service. 'Filter' will show up as a field for all tables. It is possible to match on this column or perform a SmartLookup on it. In the value for this field you will supply the full filter in the OData Query format http://www.odata.org/documentation/odata-version-2-0/uri-conventions/#FilterSystemQueryOption.

Filter Example

This is an example of a query you would supply in the Filter column when matching on it.

Account/Id eq guid'1de420aa-9577-46ac-8326-c3a566a58c3b'

Coded SmartLookup Example

This example would filter Contacts on the Contact's related Account ID, returning the Surname field.

ScriptedField=SmartLookup("ContactCollection", "Surname", "Filter = Account/Id eq guid'1de420aa-9577-46ac-8326-c3a566a58c3b'")

C# Smart Lookup:

roleId = ConvertObj2String(Starfish.SmartLookup("HOBFinancialAccountRole","Id","Name = '"+role+"'"));

SQL SELECT Example

This example would filter for Products related to a single Order. This example would be used as your SQL Selection Statement when using Creatio as your Origin.

SELECT * FROM OrderProductCollection WHERE Filter = 'Order/Id eq guid''e1d0c2ab-7d41-4e23-b040-3ba524778063'''

Field Types

Checkbox

When mapping to a checkbox, Creatio expects a 1 or a 0. You can use a script in a function field mapping to convert values. See this VBScript:

Function ScriptedField
	If "@@ORG:cfgClient@@"="Y" Then
		ScriptedField=1
	Else
		ScriptedField=0
	End If
End Function

500 Internal Server Error

If you receive this message while trying to create stages or work with mappings, there's a good chance your Creatio metadata is corrupt. This can occur if you have a broken customization or have not completed a full compile within your Creatio instance. To know for sure, in a browser go to the following URL: https://YOURINSTANCE.creatio.com/0/ServiceModel/EntityDataService.svc/$metadata (replacing YOURINSTANCE with the actual name of your company). If everything is working you should get back a giant XML stream. If there is something wrong with your metadata, you will see a message like "Internal Server Error. Unable to find property 'SomeFieldName' on type 'Terrasoft.Configuration.SomeTableName'. Please make sure that the property name is correct.". If you see this you will have to resolve this error before continuing to work within Starfish.

Indexing a Field

You can add an index to a field to speed up performance, especially if you have a large data migration matching on a custom field.

Open the object via Advanced Settings. Click the + button next to the Indexes header. Give your Index a name and then add the field to it. At least for migration purposes, you'll want to index just your matched field on it's own. Save and publish.

If you already have data in Creatio before creating the index, use the SQL Command console to re-index. The syntax below works for SQL databases:

ALTER INDEX IndexName ON TableName REBUILD

For example:

ALTER INDEX IndexUsrSLXId ON Activity REBUILD

In my scenario, indexing this field made my migration about 4 times faster.

Cleaning Up City and State Lookups

If data is imported incorrectly, a Lookup can get duplicate values in it. To clean this up, you need to delete or update up all records that use the duplicate Lookup value and then you can clean up the Lookup Values. If you have the SQL Console tool installed, you can use SQL statements to speed this process up over manual cleanup. These SQL statements could be helpful:

DELETE FROM "ContactAddress";
DELETE FROM "AccountAddress";
UPDATE "Contact" SET "CityId" = null, "RegionId" = null WHERE "CityId" is not null or "RegionId" is not null;
UPDATE "Account" SET "CityId" = null, "RegionId" = null WHERE "CityId" is not null or "RegionId" is not null;
DELETE FROM "City";
DELETE FROM "Region";