Creatio Connector

Creatio Connector



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'")

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'''

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.bpmonline.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.


Parameter
Description
Creatio Instance URL


bpm'online Connection Edit Screen


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 one scenario, indexing this field made the migration job about 4 times faster.

Writing to Creatio

Boolean Fields

You need to write values of 'True' or 'False'

Lookup Fields

You can write the ID of the lookup record to the .Id field shown in StarfishETL

You can write the text value of the lookup (lookup name) to the .Name field in StarfishETL. If the Lookup table name isn't exactly the same as the field name in the table you are writing to, you need to also write the name of the lookup table to the .LookupTable field in Starfish. When you use this approach, any values that are not already in the list will be added as the data is processed.

Or, if the lookup table has a column other than name that you want to match on, provide the value of the column in the .LookupName field, the table name in the .LookupTable field, and the column name in the .LookupField field. Just keep in mind that with this approach, the row will error if the source data has a value that's not already in the list, as it will try to insert the new value but error since you are only providing the value for a secondary field and not a Name value.

Address lookups

StarfishETL has special logic programmed in to the Address-related lookups (Cities, Regions, Countries). If a new value needs to be inserted, say to the Cities lookup, the Region and Country will be populated correctly on the new City row when it is inserted.

Calling a Creatio Process

You can directly trigger Creatio to start running a process from a job where Creatio is your destination. For example, you can do this in a before or after script, or even in a field script.

Here's an example of how you might do this via VBScript. Substitute the name of your process for the value UsrDeleteEmployeeBalances
  1. Sub VBScriptProcedure
        ExecSQL("Process UsrDeleteEmployeeBalances/Execute")
    End Sub

    • Related Articles

    • Creatio / Hubspot Starter Template

      Note: Before beginning it is important to understand that while this is a prebuilt integration it is highly recommended that someone who is familiar with both systems, their databases and your processes perform the implementation.  It is also ...
    • Connecting to Creatio

      Follow these steps to connect to Creatio: While in the StarfishETL Cartographer, navigate to the Connections tab Select the Creatio connector, if a connector already exists, that you will be using for the integration If no HubSpot connector appears ...
    • Hubspot Connector

      Additional Documentation This Connector is based on the Hubspot ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/DHG/ado/ Hubspot Connector Note: As of September 8, 2020 ...
    • SugarCRM REST Connector

        SugarCRM REST Origin Use JSONLint to validate JSON: https://jsonlint.com/. Sample Origin Filters See the GET /<module> filterList in the SugarCRM REST Help: https://SERVER/rest/v10/help/ Note the [{...}] surrounding the filter. This is required. ...
    • Sage 300 web service setup and configuration

      Setting up Sage 300 web service test Get ready - Prep Sage 300 credentials Sage 300 SQL database Company Username and password System Prereqs .NET Framework 4.7.1+ IIS with ASP.NET enabled. Installed somewhere where the Sage 100 client has also been ...