In the academy documentation, there is limited information about using the ESQ/ORM server-side methods to delete data, with only 2 or 3 toy examples. We need to efficiently delete large volumes of data using server-side ESQ in one of our processes, but we can't see anything about bulk deleting while still firing off any event-based triggers on the entity or generating change log records for it. Obviously the direct Terrasoft.Core.DB delete statement would be very efficient, but would skip the Creatio logic where needed.

 

We've been able to delete records by iterating over a collection of entities matching the required filtering condition retrieved by using GetEntityCollection and then for each entity in the collection, running a FetchFromDB on its Id before deleting it, but this seems like quite a few steps more than should be required and doesn't do it in a batched way.

 

Any help would be greatly appreciated.

Like 0

Like

2 comments

Hello Harvey,

 

You can use out-of-the-box examples of bulk deletion logic. For example we can take the BulkEmailTargetArchiver class and the DeleteArchiveLevel method in it:

private void DeleteArchiveLevel(string sourceSchemaName, string targetSchemaName) {
			int processedRecords;
			do {
				var deleteQuery = new Delete(_userConnection).From(sourceSchemaName)
					.Where("Id").In(new Select(_userConnection)
						.Top(_batchSize)
						.Column("s", "Id")
						.From(sourceSchemaName).As("s")
						.Where().Exists(new Select(_userConnection)
							.Column(Column.Parameter(1))
							.From(targetSchemaName)
							.Where(targetSchemaName, "Id")
							.IsEqual("s", "Id")));
				processedRecords = deleteQuery.Execute(_dbExecutor);
			} while (processedRecords != 0);
		}

This is just one example of the logic. You can use:

 

select * from SysSchemaSource

where Source like '%DeleteQuery%'

 

to get other schemas in the application configuration with delete queries examples and build your own one using all examples you may find in the system.

Hi Oleg,

 

using these Delete classes bypasses Creatio’s internal logic though, right? So change logs will be inaccurate and any BPs that should be triggered on delete would not be triggered etc. This seems risky to me as important processes could hang off this, and I don’t know what other internal aspects of Creatio might be relying on logic that Creatio manages at an application level.

 

Is there any way to bulk delete while respecting that?

Show all comments

Hi Community,

I want to add all the columns to the ESQ query without giving manually for each column ?

Like 0

Like

1 comments

Hi Rakshith,

 

Please don't do this since this may result in heavy SQL query with unnecessary columns that is sent to the database each time the logic is triggered. It's much more correct and better to specify only needed columns since it will reduce the possibility of performance issues in the website.

Show all comments

Hello Creatio community,

I am using Creatio base method:

/**
 * Opens edit page for selected record.
 * @protected
 */
editCurrentRecord: function() {
	const primaryColumnValue = this.getPrimaryColumnValue();
	// ** ESQ Method
	var object = this.GetObjData(primaryColumnValue);
	// existing implementation
	this.closeMiniPage();	
	if (primaryColumnValue) {
		this.editRecord(primaryColumnValue);
	}
},

Before page redirection I want to get all object data using ESQ filter ("GetObjData" method). The problem is that ESQ is asynchronous and the main method doesn't wait for "GetObjData" method to finish.

 

How can I convert "GetObjData" to synchronous so the main method "editCurrentRecord" awaits the child method?

 

Regards,

Lirzae

Like 0

Like

1 comments

Hello,

if you want to make your methods work in a defined order, you need to use the Terrasoft.chain logic. More about it in this article.

Show all comments

Hi Team,

 

I'm looking for a solution where I can fetch the list of records from a section if the value of any three fields that i enter in the edit page form of a new record match with the current entry field values.

 

For example : In my current record I enter the values as follows for the customer CustomerA:

Field1 = 1;

Field2 = 2;

Field3 = 3;

Field4 = 4;

Field5 = 5;

 

Existing entries :

entry 1 : CustomerB

Field1 = 1;

Field2 = 2;

Field3 = 3;

Field4 = 6;

Field5 = 9;

 

entry 2 : CustomerC

Field1 = 3;

Field2 = 2;

Field3 = 3;

Field4 = 4;

Field5 = 9;

 

entry 3 : CustomerD

Field1 = 10;

Field2 = 12;

Field3 = 33;

Field4 = 62;

Field5 = 91;

 

then, in this case system should be able to fetch the entry with customer names-CustomerB and CustomerC  in a list and display in a dialogBox .

 

Kindly help.

Many thanks in advance.

 

best regards,

Sarika

 

 

Like 0

Like

1 comments

Hello,

 

Please, specify the problem in more detail, what are the user's steps when reproducing it, what is the actual result and what is expected.

Screenshots are highly appreciated and will help us to find the best solution for you.

Show all comments

Why is this not working? I am genuinely confuse.



Setting visibility using ESQ is not working. Am I missing something?

 

define("OpportunitySectionV2", ["ConfigurationConstants"], function(ConfigurationConstants) {
	return {
		entitySchemaName: "Opportunity",
		details: /**SCHEMA_DETAILS*/{}/**SCHEMA_DETAILS*/,
		attributes: {
			"isSysAdmin": {
				dataValueType: Terrasoft.DataValueType.BOOLEAN,
				type: Terrasoft.ViewModelColumnType.VIRTUAL_COLUMN,
				value: false
			}
		},
		diff: /**SCHEMA_DIFF*/[
			{
				"operation": "merge",
				"name": "DataGridActiveRowDeleteAction",
				"values": {
					"visible": {
						"bindTo": "isSysAdmin"
					}
				}
			}
		]/**SCHEMA_DIFF*/,
		methods: {
			onGridDataLoaded: function() {
				this.callParent(arguments);
				this.getUserRights();
			},
			getUserRights: function() {
				var scope = this;
				var currentUser = Terrasoft.SysValue.CURRENT_USER.value;
				var sysAdmins = ConfigurationConstants.SysAdminUnit.Id.SysAdministrators;
				var esq = Ext.create("Terrasoft.EntitySchemaQuery", { rootSchemaName: "SysUserInRole" });
				esq.addColumn("SysRole");
				esq.addColumn("SysUser");
				esq.filters.add("SysUser", Terrasoft.createColumnFilterWithParameter(
					Terrasoft.ComparisonType.EQUAL, "SysUser", currentUser));
				esq.filters.add("SysRole", Terrasoft.createColumnFilterWithParameter(
					Terrasoft.ComparisonType.EQUAL, "SysRole", sysAdmins));
				esq.getEntityCollection(function(response) {
					if (response && response.success) {
						var result = response.collection;
						var isSysAdmin = (result.collection.length !== 0);
						debugger;
						scope.set("isSysAdmin", isSysAdmin);
					}
				}, this);
			},
		}
	};
});

Thank you.

Solem A.

Like 1

Like

5 comments
Best reply

Solem Khan Abdusalam,

It's been a bit since I last tried this, but I've tried binding visible (and caption) of buttons in ActiveRowActions and they never seem to work. 

I do this instead: Check and store in the attribute if the user has the needed role. Then, in the method that adds the delete action, do something like this: 

getDeleteRecordMenuItem: function() {
    if (this.get("isSysAdmin")) {
        return this.callParent(arguments);
    }
}

This way, the delete button is never added since when the method is called, it only calls the parent if the user has the role. 

Note, you have to pre-fetch the user's role earlier, before the getDeleteRecordMenuItem is called. Since the ESQ is asynchronous you can't call it in within that method.

Ryan

hi Solem Khan Abdusalam,

 

Please call the getUserRights() method inside init : function() as below

init: function() {
    this.callParent(arguments);
    this.getUserRights();
},

Also, Object permission can be used to achieve the functionality (though the button is visible the operation to add, delete can't be performed).

 

 

BR,

Bhoobalan Palanivelu.

Bhoobalan Palanivelu,



Yes I disabled the role to delete but I need the button to disappear.

Will it work in init? It's ESQ. I tried doing this.set() in without the ESQ and it is not working. Is attribute binding not working anymore?



I am trying to remove the delete button in ListView at Section.

 

//

 

I tried removing methods just to test if attributes works, and it didn't

		attributes: {
			"isSysAdmin": {
				dataValueType: Terrasoft.DataValueType.BOOLEAN,
				type: Terrasoft.ViewModelColumnType.VIRTUAL_COLUMN,
				value: true
			}
		},
		diff: /**SCHEMA_DIFF*/[
			{
				"operation": "merge",
				"name": "DataGridActiveRowDeleteAction",
				"values": {
					"visible": {
						"bindTo": "isSysAdmin"
					}
				}
			}
		]/**SCHEMA_DIFF*/,

 

Solem Khan Abdusalam,

It's been a bit since I last tried this, but I've tried binding visible (and caption) of buttons in ActiveRowActions and they never seem to work. 

I do this instead: Check and store in the attribute if the user has the needed role. Then, in the method that adds the delete action, do something like this: 

getDeleteRecordMenuItem: function() {
    if (this.get("isSysAdmin")) {
        return this.callParent(arguments);
    }
}

This way, the delete button is never added since when the method is called, it only calls the parent if the user has the role. 

Note, you have to pre-fetch the user's role earlier, before the getDeleteRecordMenuItem is called. Since the ESQ is asynchronous you can't call it in within that method.

Ryan

Ryan Farley,



Would this work on Section?

For a section you can override checkCanDelete to return a boolean indicating if the user can delete or not.  Sadly it won't remove the delete button, will instead just give them a message that they can't delete (which is how it works out of the box).

Ryan

Show all comments

Hi Team,



I would like to modify the filters for the list of Product Displayed while adding products in OrderProduct (In Order record).

 

Step 1: How does the add button event open ProductSelectionSchema?

Step 2: Need to update the filters in ProductSelectionSchema?

The Product selection schema has "loadGridData" method that calls "getProductInBasePriceListEsq" where the BasePriceList is taken and filtered.

Also, in OOTB the Account and its price list are sent to ProductSelectionSchema and how do the values are transferred?

 

Below are the filters required,

1.Dont need to display any base price list for any products.

2.Filter the Product Price List only if it has start date.

3.Filter the Product Price List only if end date is empty or null.

4.Need an additional parameter to filter the Product (price list table, column is grade).

i.e., Grade column value is passed from Order page and it has to be compared in ProductSelectionSchema.



Note: Date filters or other filters are not applying and it always shows the base price list. And how to get the values from OrderPageV2 to ProductSelectionSchema (if it is through message mechanism, how the OOTB Account and its pricelist is transferred and what are the schemas and where it is defined both subscription and publish of basepricelist)

define("ProductSelectionSchema", [],
function() {
return {
    methods: {
      init: function(callback, scope) {
            this._initViewActionItems();
            this.set("CurrentDataView", "GridDataView");
            this.set("DataViewToChange", "GridDataView");
            this.moneyModule = MoneyModule;
            this.initAttributeDefaultValues();
            this.callParent([function() {
                this.Terrasoft.chain(
                    this.initEntitySchema,
                    this.initProfile,
                    this.requestMasterEntityData,
                    this.loadCurrencyRates,
                    this.initCurrencies,
                    function() {
                        this.loadGridData();
                        this.subscribeSandboxEvents();
                        this.Ext.callback(callback, scope);
                    },
                    this
                );
            }, this]);
        },
 
 
    getProductInBasePriceListEsq: function(basePriceList) {
        var basePriceListProductEsq = this.getBaseESQ("Product");
        var productPricePrefix = "[ProductPrice:Product:Id].";
        basePriceListProductEsq.rowCount = 40;
 
        basePriceListProductEsq.addColumn("Price", "ProductPrice");
        basePriceListProductEsq.addColumn(productPricePrefix + "Price", "Price");
        basePriceListProductEsq.addColumn(productPricePrefix + "Currency", "Currency");
        basePriceListProductEsq.addColumn(productPricePrefix + "Tax", "Tax");
        basePriceListProductEsq.addColumn(productPricePrefix + "Tax.Percent", "DiscountTax");
        basePriceListProductEsq.addColumn(productPricePrefix + "PriceList", "PriceList");
        basePriceListProductEsq.addColumn(productPricePrefix + "DBLStartDate", "StartDate");
        basePriceListProductEsq.addColumn(productPricePrefix + "DBLEndDate", "EndDate");
        basePriceListProductEsq.addColumn(productPricePrefix + "DBLProductGrade", "DBLProductGrade");
 
        basePriceListProductEsq.filters.addItem(this.Terrasoft.createFilter(this.Terrasoft.ComparisonType.EQUAL,
            productPricePrefix + "Product.Id", "Id"));
        basePriceListProductEsq.filters.addItem(this.Terrasoft.createColumnFilterWithParameter(
            this.Terrasoft.ComparisonType.EQUAL, productPricePrefix + "PriceList.Id", basePriceList.value));
 
        /*basePriceListProductEsq.filters.addItem(this.Terrasoft.createColumnFilterWithParameter(
            this.Terrasoft.ComparisonType.LESS_OR_EQUAL, productPricePrefix + "DBLStartDate", today));
        basePriceListProductEsq.filters.addItem(this.Terrasoft.createColumnFilterWithParameter(
            this.Terrasoft.ComparisonType.EQUAL, productPricePrefix + "DBLEndDate", null));
 
            Need Filters
            1.Dont need to display any base price list for any products.
            2.Filter the Product Price List only if it has start date.
            3.Filter the Product Price List only if end date is empty or null.
            4.Need an additional parameter to filter the Product (price list table, column is grade).
            i.e., Grade column value is passed from Order page and it has to be compared in ProductSelectionSchema.
 
 
        */
 
        basePriceListProductEsq.filters.addItem(this.Terrasoft.createColumnFilterWithParameter(
            this.Terrasoft.ComparisonType.EQUAL, productPricePrefix + "DBLProductGrade", productGrade.value));
 
        this.applyAdditionalFilters(basePriceListProductEsq);
        this.initializePageableOptions(basePriceListProductEsq);
        basePriceListProductEsq.filters.addItem(
            this.Terrasoft.createColumnFilterWithParameter(this.Terrasoft.ComparisonType.EQUAL,
                "IsArchive", false));
        return basePriceListProductEsq;
    },
    },
    diff: []
};
});



Any support on this case is appreciated!





BR,

Bhoobalan Palanivelu.

 

Like 0

Like

3 comments

Hello Bhoobalan,

 

Regarding the first question, the add button is defined in the ProductDetailV2 schema from the ProductCatalogue package. In this part:

onProductSelectionButtonClick: function() {
					var isCardChanged = this.isCardChanged();
					if (isCardChanged) {
						var args = {
							isSilent: true,
							messageTags: [this.sandbox.id]
						};
						this.set("OpenProductSelectionModule", true);
						this.sandbox.publish("SaveRecord", args, [this.sandbox.id]);
					} else {
						this.loadProductSelectionModule();
					}
				},

 

Regarding the second part where you need custom filters, pleast contact us at support@creatio.com specifying each request and each question in detail in order to understand the business task and what is the current issue.

 

Best regards,

Dariy

 

Dariy Pavlyk,

Thanks for sharing this!

 

Is it possible to make the Product object in the Opportunity section (OpportunityProductInterest) to open the product catalogue?

What are the steps to be carried out to make this OpportunityProductInterest open the product catalogue list?



 

BR,

Bhoobalan Palanivelu.

 

Bhoobalan Palanivelu,

 

We're glad to see that you were able to achieve this functionality as mentioned in these articles

https://community.creatio.com/questions/enable-product-catalog-list-ord…

https://community.creatio.com/questions/enable-product-catalog-list-ord…

 

Unfortunately, as it's mentioned there, it wouldn't be possible to set up a product catalogue using our OOTB object, as it is already implemented for other functionalities. 

 

Best regards,

Dariy

Show all comments

Hi Team,

 

There is a requirement in which I have to verify a specific condition based on fields and lookup values on clicking the SAVE button. I am using ESQ in edit page schema while overriding asyncValidate method to achieve this. 

 

But, I am unable to figure out which commands can be used to get further field values of that lookup, and then compare it. For reference, here in my case, Contact lookup is on Custom section and I want to fetch Department value of it .

 

Thanks in Advance, Prashant Jha

Like 0

Like

4 comments

Hello, 

 

Please check the below Community post for an example of the implementation:

https://community.creatio.com/questions/actually-i-have-developed-code-…

 

Let us know in case of any questions. 

Best regards,

Anastasiia

Hi Anastasiia,

 

How can we fetch columns of other object through ESQ by matching them with a condition. Any example or syntax will be helpful.

Prashant Jha,

Hi, take a look at this article, I believe this is what you needed.

In any case, you can use another ESQ request based on the values you got from the previous one.

Hi, I am overriding asyncValidate method with multiple filters (A AND C) AND (B AND C). Here C is the field from the same object, A and B are the fields from other object using ESQ.

 

I have created a separate function to achieve this as below :

 

ConditionCheck : function(ID, BUnit, esq)

            {

                

            var esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Id",ID);

            var esqSecondFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL,"Department.Name",BUnit);

            

             esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;

            

            

                     esq.filters.add("esqFirstFilter",esqFirstFilter);

                     esq.filters.add("esqSecondFilter",esqSecondFilter);

                

                esq.getEntity(function(result)

                              {

                    if(!result.success)

                        {

                            this.showInformationDialog("Error");

                            return false;

                        }

                    return true;

                },this);

            },

 

And, I have used this function to call (A AND C) and (B AND C) separately as:

 

var result1 = this.ConditionCheck (A, C, esq);

var result2 = this.ConditionCheck (B, C, esq);

 

Now, I am not sure how we can combine the result1 and result2 in asyncValidate method such that it will only going to save when both result1 and result2 holds "true". Please help me on this.

Show all comments
Question

Hi all,

 

Have you played with Freedom UI Sdk yet ?

Have you any idea of how to execute an esq ?

 

Best regards

Like 1

Like

5 comments

Hello,

 

Thank you for reaching out.

 

Could you please clarify what issue do you have and what is your business task? 

 

Thanks in advance.

 

Best regards,

Anastasiia

 

 

Anastasiia Marushenko,

Hi need to be able to do the following, but in freedom UI : 

			setDefaultLieu: function(clientFinalId) {
					if(!this.get("UsrLieu")) {
						var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
							rootSchemaName: "UsrLieu"
						});
						esq.addColumn("Id");
						esq.addColumn("UsrName");
						var esqAccountFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL,
						"UsrAccount", clientFinalId);
						esq.filters.add("esqAccountFilter", esqAccountFilter);
 
						esq.getEntityCollection(function(result) {
							if (result.success) {
								debugger;
								var collection = result.collection;
								if(collection.collection.length == 1) {
									var entity = collection.getByIndex(0);
									this.set("UsrLieu",{value: entity.get("Id"), displayValue: entity.get("UsrName")});
								}
							} else {
								this.showInformationDialog("Request error. Please contact your system administrator");
							}
						}, this);
					}	
			}

 

Hello,

 

Freedom UI allows to call esq query (for example using the code below when the esq call is connected to attribute change):

{
				 request: "crt.HandleViewModelAttributeChangeRequest",
				 handler: async (request, next) => {
					 if (request.attributeName === 'DateTimeAttribute_m2b4mok') {
						 var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
							 rootSchemaName: "Account"
						 });
						 esq.addColumn("CreatedOn");
						 var esqFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Id", "1e5d4e6e-13db-43c8-8b4e-fc04d514932b");
						 esq.filters.add("esqFilter", esqFilter);
						 esq.getEntityCollection(function (result) {
							 if (result.success && result.collection.getCount() > 0) {
								 result.collection.collection.each(function(item) {
									 request.$context.UsrEndDate = item.values.CreatedOn;
								 }, this);
							 }
						 }, this);
					 }
					 return next?.handle(request);
				 },
			 }

but the issue is that esq is async and we always get the value for the column after all the handlers are completed. I will ask our core R&D team to add esq support in Freedom UI. Thank you for helping us in making the app better!

Oleg Drobina,

There is already support for EntitySchemaQuery in the new Creatio DevKit SDK that returns a promise that you can await rather than using a callback as in the Ext version. I can't seem to get it to work yet although it's difficult when I'm just guessing at how it is used since there is no documentation on it yet - or by trying to figure it out by examining the code (which is very difficult since it's minified and transpiled from TS).

If you could get one of the devs to provide a small sample using ESQ from the DevKit SDK that would be fantastic.

Ryan

Jerome, 

I've been told the intended use on a Freedom UI page is to use the Model class for retrieving, adding, updating, deleting data from client-side code on a Freedom UI page (instead of using ESQ).

I've started some articles on this topic, the first one for retrieving a single record here: https://customerfx.com/article/retrieving-a-record-via-the-model-class-…

I'll have several more articles coming on doing queries with the Model class. I actually quite like using the model class so far, but there are for sure some things that I am not sure it can handle like ESQ - I hope we'll see ESQ become available in Freedom UI pages, the classes are all in the DevKit SDK - except for the one that matters (the QueryExecutor to actually execute the ESQ).

Ryan

Show all comments

Hi Community,

I am facing an issue which is related to SectionActions Button for sending multiple records. I need to send some records (out of all selected records) to a business process in an array after filtering with esq inside section edit page.

Issue: The filtered Id's are not being pushed into array correctly. Means when we explore the array, the values are there but when we try to print or send we get initial value of that array (i.e. empty string or null).

 

getSectionActions: function() {

                var actionMenuItems = this.callParent(arguments);

                actionMenuItems.addItem(this.getButtonMenuItem({

                    Type: "Terrasoft.MenuSeparator",

                    Caption: ""

                }));

                actionMenuItems.addItem(this.getButtonMenuItem({

                    "Caption": {bindTo: "Resources.Strings.AMDAssignMultipleRecords"},

                    "Click": {bindTo:"runCustomProcess"},

                    "IsEnabledForSelectedAll": true

                }));

                return actionMenuItems;

            },

            runCustomProcess: function(){

                if(this.get("SelectedRows") == "" || this.get("SelectedRows") == undefined){

                    this.showInformationDialog("Please select records for batch process!");

                }

                else{  

                    var selectedRows = this.get("SelectedRows");

                    console.log("Selected records : ", selectedRows);

                    var recordsnumber = selectedRows.length;

                    var resIdRows=[];

                    var resIdForArray="";

                    selectedRows.forEach(fnProgramABCArray);

                    console.log("Selected ABC records : ", resIdRows);

                    var selectedOrder = resIdRows.toString();

                    console.log("selectedOrder: ", selectedOrder);

                    var args = {

                        sysProcessName: "AMDProcess_BatchAssignments",

                        parameters: {

                            SelectedRowsCol: selectedOrder,

                            NoOfRes: recordsnumber

                        },

                    };

                    ProcessModuleUtilities.executeProcess(args);

                }



                function fnProgramABCArray(item){

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

                    esq.addColumn("AMDStatus");

                    esq.addColumn("AMDProgram");

                    esq.filters.addItem(esq.createColumnFilterWithParameter(

                        Terrasoft.ComparisonType.EQUAL, "Id", item));

                    esq.getEntityCollection(function (result) {

                        let status = result.collection.getByIndex(0).get("AMDStatus").displayValue;

                        let program = result.collection.getByIndex(0).get("AMDProgram").displayValue;

                        if(status === "In Progress" && program === "ABC"){

                            resIdRows.push(item);

                        }

                    });

                }

            }

 

Please find attached screenshot below with an array of selected records vs another array of selected ABC records and string output as Selected order (empty string).

 

Like 0

Like

1 comments

Hi,

 

The issue here is that ESQ is asynchronous and as a result once the:

 

selectedRows.forEach(fnProgramABCArray);

 

is called, the browser continues executing the main runCustomProcess, but in addition executes the fnProgramABCArray in the background and when the

 

console.log("Selected ABC records : ", resIdRows);

 

and 

 

console.log("selectedOrder: ", selectedOrder);

 

are executed they won't show anything, these will be an empty array and an empty string.

 

You need to modify this logic and perform the filtration of records in the business process directly where you can check all the records passed using either the read data element and the conditional flow or a script task that uses the SelectQuery class to check data consistency.

 

Best regards,

Oscar

Show all comments

I'm optimising a query attempting to use a filter with 'Terrasoft.ComparisonType.EXISTS', I'm guessing at the syntax and it isn't working.  If anyone could point out where I'm going wrong here:

...
const subFilter = Terrasoft.createFilterGroup();
subFilter.addItem(Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "UsrRefundItem", true));
const esqSecondFilter = Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.EXISTS, subFilter);
esq.filters.add("esqSecondFilter", esqSecondFilter);
...

Thanks,

 

Like 0

Like

8 comments

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

Show all comments