Question

ESQ Query On Empty Lookup Column

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