Query Sage CRM Data

Example walk through

A. Add SageCRM as a server

For SageCRM: eg.addserver sagecrm <ALIAS> "<URL>" "<USERNAME>" "<PASSWORD>"

Syntax:

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

EG

 >addserver sagecrm "crm" "https://crm.yourserver.com" "Supervisor" "********"

This sets up Sage CRM as a connection in the App

B.

List the tables(collections) in the system

 >dbtables
 

C.

See the columns in a table (collection)

 >describe Company
 

Filter strings and comparison commands

eq - =
ne - !=/<>
ge - > or >=
le - < or <=
like - a string contains a value 
Strings only
"Comp_Name like '%Worldwide%'"

Dates

Date values must be in ISO format

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

Example ISO format

 2026-02-08T11:04:02.321Z
 

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 expect the "Id" value of the lookup table if used in a comparison

EG

 Comp_CompanyId eq 1222

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 "getID"

EG in jint

 var _compId = di.getID('Company', 'Union Computer');

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

Query some data

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

Example

 >querydb "Company" "Comp_Name like '%Worldwide%'" "Comp_CompanyId, Comp_Name" "Comp_Name DESC"
 

---

Codes and Lookups

In Sage CRM some fields are lookups/selects and they use codes and translations.

To do this you use

 var val=di.getlookupcode("Company", "Comp_Sector","Construction")