Starting in version 8.5 of Infor CRM SLX, a new feature was introduced to allow creating custom literals to be used in the group builder’s conditions. CRM already had a variety of standard literals that can be used in groups. Dale wrote a summary of these in this article. With the introduction of custom literals, it presents incredible flexibility to clients, however the implementation of this area is not the most elegant. In fact, there is no interface to manage custom literals. Let’s examine how you go about creating custom literals.
Again, the first thing to note is, as of 9.1, there is no interface for creating these custom literals. This may not be a bad thing since you do need to know what you are doing in order to properly construct the queries that make up these literals. The only way to create these is via a back-end update to the database table where these custom literals are stored. Let’s take a look at this location, a table named CUSTOMQUERYPARAMETER.
This table consists of the following columns:
- CUSTOMQUERYPARAMETERID – the unique primary key ID
- CREATEUSER – CRM user ID
- CREATEDATE – date/time field
- MODIFYUSER – CRM user ID
- MODIFYDATE – date/time field
- NAME – the name of the literal that users will see in the query builder. This cannot contain spaces and should also only be alpha characters.
- PARAMETERVALUE – this is the SQL statement used to determine what records are returned
- SECCODEID – CRM team ID. Used to control which literals are accessible to users
- DATATYPE – defines the data type that this literal can be used against.
- ISSCALAR – T or F. F means that more than one record is returned. T means that a single value is returned.
- DATABASETYPE – What kind of database this query can run on. Can be SQL, Both, or Oracle
- ISHIDDEN – T or F. Set to T if you don’t want this literal to be available. Can be left null.
So how exactly are these used? Well, let’s say you want to find all accounts that have never had an activity or history (that isn’t a DB change). You could create a SQL statement to return all ACCOUNTIDs that meet this criteria. Then in your group builder you can add a condition of “ACCOUNTID” “In” and then pick that custom literal. This would then produce a list of all accounts that have no such activity.
There are many such use scenarios. You can even get complex and utilize other SQL data sources and SQL views to find records.
Let’s take a look at how exactly to go about adding the custom literal.
First we want to construct a SQL statement and validate the query results. In this example we will go the route of finding those accounts that have no activities or history (not including DB changes). TO do that we will utilize left joins from the ACCOUNT table to both the ACTIVITY and HISTORY tables and also filtering out database change history (Type 262156). Then in the where clause we say to only return accounts that do not have activity and history:
select a.ACCOUNTID from ACCOUNT a left join ACTIVITY b on a.ACCOUNTID=b.ACCOUNTID left join HISTORY c on a.ACCOUNTID=c.ACCOUNTID and c.type<>262156 where b.ACTIVITYID is null and c.HISTORYID is null
Once we have validated that the results are correct then we want to create an entry in the CUSTOMQUERYPARAMETER table. We can use a SQL insert statement for this:
insert into
sysdba.CUSTOMQUERYPARAMETER
(CUSTOMQUERYPARAMETERID,
CREATEUSER,
CREATEDATE,
MODIFYUSER,
MODIFYDATE,
NAME,
PARAMETERVALUE,
SECCODEID,
DATATYPE,
ISSCALAR,
DATABASETYPE)
values
('ACUSTOMID001',
'ADMIN',
dateadd(ms,-1*datepart(ms,getutcdate()),getutcdate()),
'ADMIN',
dateadd(ms,-1*datepart(ms,getutcdate()),getutcdate()),
'NOACTIVITYACCOUNTS',
'select a.ACCOUNTID from ACCOUNT a left join ACTIVITY b on a.ACCOUNTID=b.ACCOUNTID left join HISTORY c on a.ACCOUNTID=c.ACCOUNTID and c.type<>262156 where b.ACTIVITYID is null and c.HISTORYID is null',
'SYST00000001',
'String',
'F',
'Both')
Make sure you use a unique primary key and that if your SQL statement contains any single quotes that you double them up within the insert statement.
Once you have run the insert statement then the custom parameter is immediately live and can be picked within the query builder. Both the web and LAN query builders can utilize these literals.
Let’s take a look at actually using the literal now. In my example I created my literal as “NOACTIVITYACCOUNTS”. I can now create a new group and drop into my conditions that the ACCOUNTID is in my literal value. Make sure you check the “Use Value as Literal” checkbox in order to see the list of literal options:
