Querying data directly against Creatio using its front end(javascript) API is quite straightforward using the EntitySchemaQuery(ESQ) object. If the filter is very simple, the Creatio no code filter lookup feature is perfect, as it lets you quickly build a lookup that filters data based on values on the current object page.

For the coded approach, the same can be implemented via the criteria below, which provides a greater degree of flexibility and configuration for more complex AND/OR type scenarios:

var esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Country.Name", "USA");

Sometimes however, there is a need to build a complex filter in Creatio that is effectively a subquery that depends on other related object information. In this case, the specification of the filter object is a little more involved and needs to be configured correctly so data from the related object links to the main object properly and applies the needed filter.

To illustrate this with an example, let's assume we have to create a contact based filter that filters a record owner field such that it excludes them when they are out of the office.

We would start with defining an object that would hold out of office information:

The UsrContact field is a reference to the employee. And we also have start and end dates that define when this person is going to be out of the office.

On the contact screen, we can now build a detail area using the "Detail wizard". This will provide a means of viewing and updating the dates when an employee is out of the office.

Once the Detail is in place and updated to have the correct data, we need to implement a filter that uses this information on a page to only show people who are working today.

In this example, we are using the Owner field on a Lead page so that only an available employee can be selected for a lead. This will ensure that each lead is quickly followed up on, when certain sales people are out of the office.

On the Lead page(LeadPageV2), set up an attribute(which maps to the owner field) that will house the necessary filter configurations:

Notice the use of the following:

• There is a join established to the out of office information via a query join indicated by the code [UsrOutOfOffice:UsrContact].Id . This is in the format [ChildObjectName:ChildObjectFieldToJoinOn.IdOfParentTable]
• The createNotExistsFilter indicates that we have to filter (employee)contacts such that there must NOT be an out of the office record for today that is in the date range specified by UsrStartDate and UsrEndDate

Once this attribute definition is in place on the page, the Owner field will automatically reference it and use it to filter out any person who is supposed to be out of the office.