An Introduction to Performing Client-Side Queries Using EntitySchemaQuery in Creatio (formerly bpm’online)

Something that is pretty necessary to know how to use when developing in Creatio is how to perform queries. When doing a query from client-side code on a page in Creatio you’ll use the EntitySchemaQuery. This article will cover some EntitySchemaQuery basics and some samples for performing a query for a single object, given it’s Id, and also a collection of objects, given some filter criteria.

The basics of performing an EntitySchemaQuery (ESQ) is as follows:

1. Create the ESQ object and indicate the object, or table, you want to query
2. Add the columns you want returned by the query
3. Next depends on whether your query is for a single object or a collection of records
   • For a single record, call getEntity providing the record’s Id value and get the result
   • For a collection of records, provide the filters, or conditions for the records you want, then call getEntityCollection to get the results

Performing a Query for a Single Object

First, we’ll look at a query to retrieve a single object, or record, given it’s Id. In this case, you know, or have the record’s Id value. For example, maybe you have a lookup on the page, the user selects something in the lookup and you want to read additional columns from the record the user selected. Let’s look at the basic structure of this type of query. Again, this will return a single record only and you must have the Id of the record you want. In this sample, we’ll be querying the Account object and will ask for the Id, Name, and Type of the account record.

var someAccountId = "405947d0-2ffb-4ded-8675-0475f19f5a81";

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
	rootSchemaName: "Account"
});

esq.addColumn("Id");
esq.addColumn("Name");
esq.addColumn("Type");

esq.getEntity(someAccountId, function (result) {
	if (result.success) {
		var id = result.entity.values.Id;
		var name = result.entity.values.Name;
		var type = result.entity.values.Type;
  }
}, this);

Note, when we create the EntitySchemaQuery object, we specify the Account object to query. We then add some columns, and lastly, call getEntity passing an account Id value for the record we want. The result we get back from this query has an entity object with values for each of the columns we requested. If our column we requested is named UsrMyColumn, we’d get it from the results as result.entity.values.UsrMyColumn. In the sample above, we’re asking for the Type column, which is a Lookup column. This value will be an object with two properties, value (the type Id) & displayValue (the actual type text), for example result.entity.values.Type.displayValue (however, be sure to check it’s not null or undefined first in case the value is empty).

It is extremely important to note that the call to getEntity is asynchronous. This means, we’re telling it to do the query, and when it has the results to provide it to the callback function (the internal function passed to getEntity) when the query is done. Whatever we intend to do with the results, needs to happen inside the callback function, not after. You cannot set variables here and use them after the code since that code will execute before the result is returned.

Performing a Query for a Collection of Records

To perform a query for a collection, or multiple records, you’ll use a similar approach, but instead of passing the Id of a record, we’ll add filters for the record or records we want. Note, you can use this method for a single result as well, it doesn’t have to return multiple records, but that all just depends on your filters you provide. Let’s look at the basic structure of this type of query. In this sample, we’ll be querying the Account object, asking for the Id, Name, and Type columns, and then adding a filter for a specific account by name:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", { 
	rootSchemaName: "Account" 
});

esq.addColumn("Id");
esq.addColumn("Name");
esq.addColumn("Type");

var filterName = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Name", "Accom (sample)");
esq.filters.addItem(filterName);

esq.getEntityCollection(function(result) {
	if (result.success) {
		// get an array of the results
		var results = result.collection.getItems();
		// loop through results
		for (var i = 0; i < results.length; i++) {
			var entity = results[i];
			var id = entity.values.Id;
			var name = entity.values.Name;
			var type = entity.values.Type;
		}
	}
}, this);

In this sample above, we create an EntitySchemaQuery object and specify we want to query the Account object, we add some columns we want in the results, and then add a single filter. We can add as many filters as we need, not just one (you can see an example of different filters in Tate’s article here). The three parameters we’re including to createColumnFilterWithParameter are:

1. The operator, in this case “=” (Terrasoft.ComparisonType.EQUAL). See all the available operator types here (or just type Terrasoft.ComparisonType in your console)
2. The field you’re filtering on, in this Name
3. The value you’re filtering for, in this case “Acccom (sample)”

Then, we call getEntityCollection to perform the query and get back our results. Just like before with getEntity this function is asynchronous. We can get an array of the results/records using result.collection.getItems() and then loop through them as needed. The array of results will be an array of entities, each with the fields requested. We can use that array however we’d like, but I like to use Terrasoft.each to loop through the results:

Terrasoft.each(result.collection.getItems(), function(entity) {
	var name = entity.values.Name;
	var id = entity.values.Id;
}, this);

Those are the basics for using EntitySchemaQuery. There’s quite a bit more it can do, there’s also server-side EntitySchemaQuery objects for use in C# as well.