Hello John,

If you mean adding a quick built-in filter as the one for date you can see in Activies section, unfortunately these are only available for specific sections and it is possible to customize such filters only with means of development.

Instead, you can use standard filter. If for example, you need to filter records by column "created on", click on the small calendar icon and there will be a field that will automatically set the filter for current date.

If you need to set it for current time, you need to switch to advanced mode and specify it for current hour, for example, as shown below.

Depending on what filter exactly you require you can simply modify it all to your needs. As an option, you can also save the advanced filter as afolder and mark it as favorite, so it will appear in te list as on the first screen above (favorite folder there - "Test").

Also, please feel free to check this article to understand better how filters work.

Kind regards,

Mira

Hello Gareth,

Terrasoft.ComparisonType.EXISTS is not used in the client-side system logic anymore since it was replaced with the createExistsFilter function. So you need to use it instead of EXISTS comparison type.

Best regards,

Oscar

Oscar Dylan,

I have tried the following but it is returning more than one record:

const caseId = this.get("MasterRecordId");
const esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
	rootSchemaName: "UsrItemInOrder"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "RefundCount", Terrasoft.AggregationEvalType.ALL);
const esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrCase", caseId);
esq.filters.addItem(esqFirstFilter);
var subFilter = Terrasoft.createFilterGroup();
subFilter.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrRefundItem", true));
const esqSecondFilter = Terrasoft.createExistsFilter("Id", subFilter);
...

I am not filtering with a subquery here, just on the one table.

Gareth Osler,

Hi Gareth. Here is a sample of how to get a count (this gets a count of all accounts with a type of "Customer", however, to just get a count of the entire table you'd just omit the filter):

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Account"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "AccountCount");
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Type.Name", "Customer"));
esq.getEntityCollection(function(result) {
    console.log("Total customers", result.collection.first().get("AccountCount"));
});

With an exists sub-filter like in your code, it would look like this:

// get a count of all accounts with a "UsrClientSystem" record that has a system type of "Creatio"
var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Account"
});
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "AccountCount");
 
var subFilter = Terrasoft.createFilterGroup();
subFilter.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrSystemType.Name", "Creatio"));
esq.filters.addItem(Terrasoft.createExistsFilter("[UsrClientSystem:UsrAccount].Id", subFilter));
 
esq.getEntityCollection(function(result) {
    console.log("Total accounts with Creatio system", result.collection.first().get("AccountCount"));
}, this);

Ryan

Ryan Farley, Thank you for the reply.  I am querying with an exists filter on the same table as the esq query, the esq query is on table UsrItemInOerder, for an exist filter of there exists a record with a UsrRefundItem column value of true.  What would the column path be for a createExistsFilter() call in that situation?

Gareth Osler,

I'm not 100% sure I follow. So you want to know if there's a UsrItemInOrder record for the parent Order that has a value of true for the UsrRefundItem column? If that's the case, you can just simplify the query (no need for the exists sub filter):

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "UsrItemInOrder"
});
// get a count of items with a refund
esq.addAggregationSchemaColumn("Id", Terrasoft.AggregationType.COUNT, "RefundCount");
// for all UsrItemInOrder rows for the parent Order
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Order", this.get("MasterRecordId"));
// add filter for refund items only
esq.filters.add(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrRefundItem", true);
// get result, this will return 1 row with a count of refund items in RefundCount column
esq.getEntityCollection(function(result) {
    var hasRefunds = (result.collection.first().get("RefundCount") > 0);
});

Ryan

Ryan Farley,

That is essentially what I have done.  But could that query be optimized with an exists query, it is being run on a database with circa 1.5 million records.

Gareth Osler,

What I was trying to do is illustrated in SQL terms in this stackexchange question,

SELECT TOP (1) id 
  FROM dbo.table
  WHERE price IS NULL
    AND category = 'hardware';

which I am arriving at the conclusion is not possible with a Creatio ESQ query.  The solution counting the records matching the case Id and 'UsrRefundItem'

flag true conditions is probably the fastest otherwise.

Gareth Osler,

The only way to do a "top" or "limit" that I know of via ESQ is like this:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
	rootSchemaName: "UsrEntity"
});
// get only first row in results
esq.rowCount = 1;
esq.addColumn("Id");
// add any filters
esq.getEntityCollection(function(response) {
	// only one row returned
}, this);

To be honest, I've never ran profiler to see if rowCount=X actually does translate to TOP X or LIMIT X, but I assume it does. However, I think the previous method of getting the count would be a more efficient way. The ESQ I posted earlier is the equivalent of:

select count(Id) as RefundCount from UsrItemInOrder where OrderId = 'SOMEID' and UsrRefundItem = 1

Ryan

Hello,

The examples can be found here and you can also create an advanced filter in some section and review its body. For example here is the filter to list all accounts that have any activity connected to an account:

If we take a look at the network tab of the console to get the SelectQuery (to identify the needed query you can clear the network tab and then hit the "Accounts" section header in the top left corner or click the "Apply" button in the filter and as a result the query will be sent) that went to the database to retrieve data we can see the path to the column in the filters object:

comparisonType and filterType are standing for the "exists" function (can be seen in the Terrasoft.ComparisonType and Terrasoft.FilterType objects).

So if you need to retrieve a correct path to some column you can use this approach to find the correct path.

Best regards,

Oscar

Oscar Dylan,

Thanks for the reply, and the 'columnPath' tips.

It's actually how the  'createExistsFilter(columnPath)' ESQ filter is used that I'm not sure of.  If you could give me an example of this function being used that would be great.

Gareth Osler,

It's not widespread in the system, but there was one use case when it was needed and it's described in this community thread. One of the examples in the system: please see the historyMessageEsqApply method from the PortalMessageHistoryItemPage module:

filterInnerGroup.add("PortalMessageExists",
							esq.createExistsFilter("[PortalMessage:Id:RecordId].Id"))

Best regards,

Oscar

Gareth Osler,

Hi Gareth, the usage of it is just like any ESQ, it returns the list of records that match the condition(s). For the example Oscar provided, to expand on that further, lets say you want a list of all accounts where an activity exists (and then to go further) where the activity has a certain owner. In this case we create an exists filter (for accounts where activities exist) that has a sub-filter (for activities with a certain owner).

That code would look like this:

var esq = Ext.create("Terrasoft.EntitySchemaQuery", { 
	rootSchemaName: "Account" 
});
 
esq.addColumn("Id");
esq.addColumn("Name");
 
// create the sub filter for the condition inside the exists
var subFilters = Terrasoft.createFilterGroup();
subFilters.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Owner", "a6b4ea0c-420c-45ab-81e7-6e76c8cc15f7"));
 
// now create the exists filter and pass the sub filter conditions to it
// note, this second param of subFilters is optional if all you want is the exists condition
var existsFilter = Terrasoft.createExistsFilter("[Activity:Account].Id", subFilters)
esq.filters.addItem(existsFilter);
 
// now use the results how you would any ESQ
// in this case a list of accounts where an activity exists with a certain owner
esq.getEntityCollection(function(result) {
	if (result.success) {
		Terrasoft.each(result.collection.getItems(), function(entity) {
			var name = entity.values.Name;
			var id = entity.values.Id;
			console.log(name, id);
		}, this);
	}
}, this);

This is the same as this: 

Ryan

Thank you for that. I think I should now be able to shave a few machine cycles off by implementing the correct query operator.

I have to admit I didn't fully understand the advanced filter 'exists' operator until posting this question and finding this post which gives the SQL equivalent of an ESQ 'createExistsFilter(columnPath)' filter.  The Creatio exists operator essentially implements a SQL EXISTS operator with a correlated subquery.  The column path using a reverse connection sets up a join to another table with the option of adding more filters to the subquery.

Hello,

Thanks for reaching out.

We double-checked your request and found that such functionality cannot be implemented neither by basic nor by development tools.

Best regards,

Anastasiia 

Hello Chani,



In order to achieve your business task, we recommend creating aggregate columns for each semester by Start date. Then you will be able to choose the needed value, 



Best regards,

Bogdan

Hello Saurabh,

Unfortunately, there is no in-built functionality to do so. Although you can add a lookup column to Contacts based on the Contact Folder lookup and choose the folder to which the contact belongs. After that, you will be able to achieve your goal. 

You can also add any column to differentiate contacts and add them to different folders based on the value in the column.

Best regards,

Max.

Dear Surabh,

I actually was able to find a way to do just what you need!

You need to set up the filter the following way:

Best regards,

Max.

Hello,



Regarding your first filter on the picture - once filter for Class of Business is added to the section, the assigned value would be fixed despite of jumping to other sections in Creatio, since it is stored in SysProfileData table.    

In order to add your second quick filter to the [Premium Register] section, you should create a replacing schema of the [Premium Register] section and add your quick filter which will filter records by Policy Number.  

You can find useful information regarding adding quick filters to the section on this link https://academy.creatio.com/docs/developer/elements_and_components/basi…

Best Regards,

Tetiana Bakai

Tetiana Bakai,

do you have idea for quick filter for string type?

Fransetya Alfi Syahrin,

Please provide the business task that is followed by creating such a functionality? To activate a string type filter two buttons should be pressed:

1) "Filter" context menu

2) "Add filter" button

Also why can't you create a dynamic folder in case you need to access some filtered data on a regular basis?

If you need to develop such functionality you need to look at the showSimpleFilterEdit function and its call in the CustomFilterViewModelV2. We don't have a ready solution and you need to develop it on your own.

Best regards,

Oscar

Hello, 

You can create a new dynamic folder in the Opportunity section, that will be populated with records based on the set filter:

1. Add filter condition based on the opportunity stage and choose all the needed stages from the list. 

2. Add a group of conditions based on your "role": owner/contact/etc. 

For an example, you can refer to the attached screenshot.

Best regards, 

Anastasiia