Filtering Lookups in Creatio (formerly bpm’online)

When you add a lookup to an object or page, the default behavior is not filtered, meaning the lookup will search through all records in the lookup object’s table. Often, this isn’t ideal. You can very easily use business rules on a page to set up a simple filter for a lookup, but this might not provide enough flexibility or you have some complex filters you need to apply? Luckily, you can easily filter the lookup via code on the page as well by simply adding an attribute that returns filters for the lookup to use.

To filter a lookup via code, you can add an attribute, with the same name as the lookup column. For example, if I have a lookup column named UsrProduct and I want to filter that lookup, I will add my attribute with the name of UsrProduct. You can think of this attribute as extending that lookup field. The basic structure of the attribute would look like this:

attributes: {
	"UsrProduct": {
		"dataValueType": Terrasoft.DataValueType.LOOKUP,
		"lookupListConfig": {
			"filters": [ /* filters will go here */ ]
		}
	}
}

To add the filters, you’ll add a function in the filters array that returns one or multiple EntitySchemaQuery filters. For example, let’s say I only want to see products in my lookup that are active and not type=Widget. I’d add some filters that look like this:

attributes: {
	"UsrProduct": {
		"dataValueType": Terrasoft.DataValueType.LOOKUP,
		"lookupListConfig": {
			"filters": [function() {
				var filters = Ext.create("Terrasoft.FilterGroup");
				filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
				filters.add("ActiveFilter", 
					Terrasoft.createColumnFilterWithParameter(
						Terrasoft.ComparisonType.EQUAL, "Active", true
					)
				);
				filters.add("NoWidgetFilter", 
					Terrasoft.createColumnFilterWithParameter(
						Terrasoft.ComparisonType.NOT_EQUAL, "Type.Name", "Widget"
					)
				);
				return filters;
			}]
		}
	}
}

Sure, we could likely do the above with a filtration business rule, but the point is that we could add whatever filter we need, not limited to what we can achieve in the business rule designer. Note, you can read values from the entity in this attribute as well. For example, let’s say we have detail on an opportunity named “Price revisions” and at some point, a user will select the price revision to use for the opportunity by using a lookup of the price revision object. We’ll want to filter this lookup to only show price revisions on the current opportunity. Strangely, in the current version of Creatio, it doesn’t allow you to set this in a business rule (the Id column doesn’t display as a column to filter by). However, you can do this using the method above and we’ll read the current opportunity Id to use in the filter:

attributes: {
	"UsrCurrentRevision": {
		"dataValueType": Terrasoft.DataValueType.LOOKUP,
		"lookupListConfig": {
			"filters": [function() {
				var filters = Ext.create("Terrasoft.FilterGroup");
				filters.add("OppFilter", 
					Terrasoft.createColumnFilterWithParameter(
						Terrasoft.ComparisonType.EQUAL, "UsrOpportunity", this.get("Id")
					)
				);
				return filters;
			}]
		}
	}
}