Query Creatio Data

From Apps for Creatio

Example walk through

A. Add Creatio as a server

For Creatio: eg.addserver creatio "<ALIAS>" "<URL>" "<USERNAME>" "<PASSWORD>"

Syntax: 

 >addserver creatio "<ALIAS>" "<URL>" "<USERNAME>" "<PASSWORD>"

EG 

 >addserver creatio "live2026" "https://live2026.creatio.com" "Supervisor" "********"

This sets up creatio as a connection in the App

B.

List the tables(collections) in the system 

 >dbtables

C.

See the columns in a table (collection) 

 >describe Account

Filter strings and comparison commands 

 eq - equals/=
 ne - not equals/!=/<>
 ge - greater than or equals/=
 le - less than or equals/=
 contains - a string contains a value 
 Strings only
 contains(Name,'a')

Dates

Date values must be in ISO format 

 filter=StartDate ge ${from} and StartDate le ${to}

Example ISO format 

         2025-05-08T10:24:01.564Z

using the Javascript engine you can get this as follows: 

 var date = new Date();
 date.setDate(date.getDate() - 1); //yesterday
 var from = date.toISOString();

Id fields (typically defined as GUID types) expect the "Id" value of the lookup table if used in a comparison

EG 

 $filter=Owner/Id eq 410006e1-ca4e-4502-a9ec-e54d922d2c00 and stage/id ne a9aafdfe-2242-4f42-8cd5-2ae3b9556d79

So if you have only the string (EG Qualification) you need to get the Id value. You can use "querydb" or a method designed for this called "getGuid"

EG in jint 

 var _accInGuid = di.getGUID("OpportunityStage","Qualification")

ref: https://creatio.crmtogether.com/index.php?title=DI_Jint

Query some data 

 querydb <ENTITY> "<FILTER>" "<COMMALISTOFFIELDS>" "<ORDERBY>" "<CUSTOMPARAMS>" <ENTER>

Example 

 >querydb Account "contains(Name,'a')" "Id,Name" "NAME ASC"

---

So of course this is a fairly basic example. What about GUID fields. Lets say the requirement is to get the data into a Data mart of warehouse and really we dont want or need the GUID but we do need the translation or lookup code so people can write simpler queries in SQL.

To do this you use 

   getLookup

   Params are
   * Collection to lookup for the value 
   * Field to use
   * Column name in island dataset

EG 

 >querydb Opportunity "" "Id,Title,{getLookup('OpportunityStage','StageId','StageCode')}" "Title DESC"

and when we push this to the Data Islands cloud you can see the data

So this is the equivailent of a join in SQL to get the data a user can see in a report for example.

Its ideal if you need the data to upload to a reporting tool like PowerBI for example. Or import the island into a local database to use with a reporting tool of choice or import to a CSV and use in Excel.

Retrieved from "https://creatio.crmtogether.com/index.php?title=Query_Creatio_Data&oldid=365"