Question

ESQ verification

14:23 Jul 08, 2020

Hi,

My case is to construct this query :

select SUM("UsrAmountForTheMonth") from table abc where agent = 'A' and client = 'B' and resolved != 'C'

agent , client and resolved all three are lookup column and UsrAmountForTheMonth is of decimal type.

I have to do it on the frontend i,e using Edit page schema. I have written the below code but nothing pop up on the screen when I refresh and open the record.

I want to understand where I am making mistake . It should in esq query formation.

Please help,

Many thanks,

Akshit.

3 comments

Dennis Hudson

18:51 Jul 08, 2020

Dear Akshit,

Here is an example of the query that you want to build (instead of your object and columns I used OOB Order columns for testing):

var esq = Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Order"
});
esq.addColumn("DeliveryType");
esq.addColumn("Status.Name");
esq.addColumn("PaymentType");
esq.addColumn("Amount");
 
esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
 
var esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "DeliveryType", "50DF77D0-7B1F-4DBC-A02D-7B6EBB95DFD0");
var esqSecondFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "PaymentType", "0026BDE9-932B-4BAA-BA30-FFA521A255AB");
var esqThirdFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.NOT_EQUAL, "Status.Name", "2. Confirmation");
 
esq.filters.add("esqFirstFilter", esqFirstFilter);
esq.filters.add("esqSecondFilter", esqSecondFilter);
esq.filters.add("esqThirdFilter", esqThirdFilter);
 
esq.addAggregationSchemaColumn("Amount", Terrasoft.AggregationType.SUM, "AmountSum");
 
esq.getEntityCollection(function (result) {
    if (!result.success) {
        console.log("Data query error");
        return;
    }
	if (result.collection.getByIndex(0)){
		console.log(result.collection.getByIndex(0).get("AmountSum"));
	}
	else {
		console.log("0");
	}
}, this);