The SalesLogix entity filters offer a nice way of filtering groups. I have written about them before here and here. There are limitations in what can be filtered. One of the areas that you will run into filtering on is a entity property for a related entity. For instance if we add a relationship from Accounts to User to represent the user identified in the ACCOUNT.ENGINEERID field we might create a property called Engineer in the Account entity which is a relationship to the User entity.
You would think we could create a filter like Account.Engineer.UserName. The only problem is that filters do not unwrap the entities to expose these properties. You can construct this filter and you will see it, but the filter will not properly filter the records.
You can get around this, but it requires a bit of a hack. A pretty cool hack though because using the sample it opens up a lot of possibilities – like filtering on a field from an accounting or other external data source. Lets take a look at what we need to do:
Step 1
Create a SQL function to be used in a SQL computed column:
Create function [sysdba].[FXGetUser](@UserID varchar(12))
returns varchar(100) as
begin
Declare @UserName varchar(100)
–String Prep – We want to remove any periods, double spaces and commas
select @UserName = isnull(username,”) from userinfo where userid = @UserID
Return @UserName
end
GO
Step 2
Add a computed column to the table we will filter on:
ALTER TABLE [sysdba].[ACCOUNT]
ADD [FXEngineerName] AS [sysdba].[FXGetUser]([ENGINEERID])
GO
Step 3
Add computed column row to SECTABLEDEFS table (Needed for SalesLogix to be aware of the field)
Insert Into sysdba.sectabledefs
(
[TABLENAME] ,
[FIELDNAME] ,
[FIELDOFFSET] ,
[FIELDINDEX] ,
[SEQCODE] ,
[KEYFLAG] ,
[SYNC] ,
[USERDEF] ,
[DISPLAYNAME] ,
[ISHIDDEN] ,
[DATETIMETYPE] ,
[DESCRIPTION] ,
[AUTOINCREMENT] ,
[MODIFYDATE] ,
[MODIFYUSER] ,
[CREATEDATE] ,
[CREATEUSER]
)
Values
(
‘ACCOUNT’
,’FXENGINEERNAME’
,0
,8787
,2
,0
,’Y’
,’T’
,null
,’F’
,null
,’Computed column’
,’F’
,getdate()
,’ADMIN’
,getdate()
,’ADMIN’
)
GO
Step 4
Add the new computed column as a property to the entity model.
In the Application Architect, right click on the entity you have added the computed field to. Choose to update the entity and then select the new computed column (FXEngineerName in my sample).
Step 5
Delete the XML schema plugin from the SalesLogix plugin table. This is needed because the XML schema is used to generate the fields available in the SalesLogix web client’s group builder.
Delete from plugin where pluginid=’xmlschema’
Step 6
Re-create the XML schema. Once you have deleted the schema in step 5, you can then recreate the schema with the new computed column included.
The way I typically do this is to go into the SalesLogix LAN client and create a new Word merge template. Before Word opens you will see SalesLogix re-generates the XML schema. Once this is complete the new schema entry will exist in the plugin table.
Step 7
Perform an IISReset on the web server.
The reset is needed to ensure the group information is removed from the web cache.
We now have a computed SQL column that acts just like a normal SalesLogix entity property (Account.FXEngineerName in my sample). Now you can create a filter on this new property and have it work properly. As I mentioned earlier, the SQL computed column could actually connect to some other database and return information relating to the the record from there. It really opens up a pretty big world of potential.
