In my last post I talked about how the lookup control can have multiple conditions added to filter the lookup results. This is done using Prefilters. However, these conditions are appended together with AND logic. Today I want to talk about a method for building OR logic so that a lookup can, for instance, only show those accounts where the type = customer OR the type = prospect.
In 7.5.0 lookup Prefilters had the ability to add a custom calculated entity property as a filter field, however this did not work as the lookup uses the pre filters to build a SQL statement and the custom calculated fields could not be parsed out correctly to form a valid SQL statement. In 7.5.1 the lookup Prefilters were restricted to only allow physical entity properties (those representing actual database fields).
There is one way that can still be used for creating what equates to a quasi-calculated entity property. This approach uses SQL computed fields to act similar to how a custom entity property worked. Lets take a look at how to use this.
I won’t go into a lot of details about SQL computed fields. There are many details about them on the web that can easily be found.
Creating a Computed Field
To add a new computed field, go into the SQL management studio, expanding out the database you want to work with, and then the table you want to add the custom field to. Right click on the Columns and choose New Column. In my example I want to add a custom field to the account so I can test to see if the Account Type is either Customer OR Prospect.
Next I will name the column something. in my case I will just simply call it FXComputedCol.
After naming it I will click below on the Column Properties tab.
One of the properties listed is Computed Column Specification. Expanding this out shows the Formula property. here is where we will insert our property. For my example it would be this:
(case when [type]= ‘customer’ or [type] = ‘prospect’ then ‘Passes’ else ‘Fails’ end)
Once the formula do not set the Is Persisted = True. This property allows the field to be indexed for quicker select queries, however it is not compatible with the SalesLogix provider and any inserts into a table will fail if the table contains a computed column with is persisted = true .
Now that we have the new field added, save the table in the SQL management studio. We can now add this field to the SalesLogix Entity model.
Adding a computed field to the SalesLogix Entity
Now that we have a computed field we can add the field into the corresponding SalesLogix entity. Go into the Application Architect and then expand out the Entity you want, in our case we added this to the Account so we expand out to their. Right click on the Account entity and choose Update properties
On the bottom of the list you should see the new column listed. Click the checkbox to add the column.
Now you can rebuild your interfaces.
Adding the field to the lookup
Now that we have added the field to the Account entity we should be able to add it to a Lookup control’s prefilter collection.
On a lookup control that is looking up the Account entity, click the Lookup Prefilter property ellipse.
Select your new field in the PropertyName.
Enter the correct value to test for in the Filter Value.
Set the operator in the CondOperator and you are done!
This is a big work around for what should be available in the lookup prefilters. Having the ability to join together conditions in either AND or OR relationships, along with ability to bracket conditions together, would be a huge addition to this control. Here’s to hoping that Sage adds this functionality in a future release.