Query Sage CRM Data: Difference between revisions
No edit summary |
No edit summary |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 47: | Line 47: | ||
Date values must be in ISO format | Date values must be in ISO format | ||
where=Note_UpdatedDate le ${to} | |||
where=Note_UpdatedDate le '2023-02-08T11:04:02.321Z' | |||
Example ISO format | Example ISO format | ||
| Line 79: | Line 81: | ||
Example | Example | ||
>querydb "Company" "Comp_Name like ' | >querydb "Company" "Comp_Name like 'Union Computer'" "Comp_CompanyId, Comp_Name" "Comp_Name DESC" | ||
[[File:sage crm rest etl query.png]] | |||
--- | --- | ||
Latest revision as of 13:04, 24 February 2026
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/crm" "admin" "********"
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
You can then view details on a field by passing in the field name also
>describe Company Comp_type
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
where=Note_UpdatedDate le ${to}
where=Note_UpdatedDate le '2023-02-08T11:04:02.321Z'
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 'Union Computer'" "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")
