Query Creatio Data

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

Example walk through

A.

>addserver creatio "creatio2215" "https://server2215.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

D.

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.