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 0

Like

5 comments

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

2 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);
					}	
			}

 

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

I have a 'UsrPayment' detail with a column 'UsrPaymentPackage' that is a lookup.  I would like to filter only for the 'UsrPayment' detail records for which the 'UsrPaymentPackage' lookup column is empty.  The code I have tried is below, however returns records which have both a lookup value and which are empty:

const caseId = this.get("MasterRecordId");
const esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
	rootSchemaName: "UsrPayment"
});
esq.addColumn("Id");
esq.addColumn("UsrCase");
esq.addColumn("UsrPaymentPackage");
const esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL,
		"UsrCase", caseId);
esq.filters.add("esqFirstFilter", esqFirstFilter);
const esqSecondFilter = Terrasoft.createColumnFilterWithParameter(
		Terrasoft.ComparisonType.EQUAL, "UsrPaymentPackage", null);				
//const esqSecondFilter = Terrasoft.createColumnFilterWithParameter(
//		Terrasoft.ComparisonType.IS_NULL, "UsrPaymentPackage");
//const esqSecondFilter = Terrasoft.createIsNullFilter("UsrPaymentPackage");
esq.filters.add("esqSecondFilter", esqSecondFilter);
esq.getEntityCollection(function(result) {
	debugger;
	if (result.success) {
		Terrasoft.each(result.collection.getItems(), function(entity) {
			debugger;
		}, this);
	}
}, this);

I'm probably missing something obvious here but if anyone can point it out.  Cheers,

Like 0

Like

1 comments

Hi Gareth,

What you need is a createIsNullFilter. Like this:

esq.filters.addItem(Terrasoft.createIsNullFilter(
    Ext.create("Terrasoft.ColumnExpression", {columnPath: "UsrPaymentPackage"})
));

Ryan

Show all comments

Could I ask for an example of the 'createExistsFilter(columnPath)' ESQ filter in use.  I'm not sure what 'columnPath' is.

 

Thanks,

Like 0

Like

5 comments

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.

Show all comments