Inability to query non ACO groups using the SalesLogix SData system groups endpoint

I recently ran into an issue when trying to use SData to retrieve group information for groups other than Account, Contact, or Opportunity groups.

SalesLogix stores non ACO groups as a different plugin type in the SLX Plugin table.  They are a type 23 rather that type 8 of the ACO groups.  This is the result of back when the support client used to be a separate animal than the sales client.

The
problem is that the group endpoint is supposed to allow querying syntax
to find a group by family and name.  It works OK for ACO groups but not
for any other groups.  So something like this works:

http://sdatahost/sdata/slx/system/-/groups/$queries/execute?_family=Account&_name=All%20Accounts
 
The underlying query for this is 
SELECT
PLUGINID FROM PLUGIN WHERE TYPE = 8 AND NAME = ‘All Accounts’ AND
FAMILY = ‘Account’ AND BASEDON IS NULL ORDER BY DEV DESC, MODIFYDATE
DESC
 
However, a similar query but to Defects does not work:
http://sdatahost/sdata/slx/system/-/groups/$queries/execute?_family=Defect&_name=My%20Defects
 
The underlying query:
SELECT
PLUGINID FROM PLUGIN WHERE TYPE = 8 AND NAME = ‘My Defects’ AND FAMILY =
‘Defect’ AND BASEDON IS NULL ORDER BY DEV DESC, MODIFYDATE DESC
 
Notice it is only looking at type 8 and not 23.
 
However a query on ID, like this, does work:
http://sdatahost/sdata/slx/system/-/groups(‘p6UJ9A0003H2’)
 
The underlying query:
SELECT
this_.PLUGINID as PLUGINID5_0_, this_.NAME as NAME5_0_, this_.FAMILY as
FAMILY5_0_, this_.TYPE as TYPE5_0_, this_.USERID as USERID5_0_,
this_.CREATEDATE as CREATEDATE5_0_, this_.LOCKED as LOCKED5_0_,
this_.LOCKEDID as LOCKEDID5_0_, this_.VERSION as VERSION5_0_,
this_.SYSTEM as SYSTEM5_0_, this_.ISPUBLIC as ISPUBLIC5_0_,
this_.DESCRIPTION as DESCRIP12_5_0_, this_.DATACODE as DATACODE5_0_,
this_.BASEDON as BASEDON5_0_, this_.TEMPLATE as TEMPLATE5_0_,
this_.AUTHOR as AUTHOR5_0_, this_.COMPANY as COMPANY5_0_,
this_.COMPANYVERSION as COMPANY18_5_0_, this_.BASEDONCOMPANY as
BASEDON19_5_0_, this_.BASEDONCOMPANYVERSION as BASEDON20_5_0_,
this_.RELEASED as RELEASED5_0_, this_.DEV as DEV5_0_, this_.READONLY as
READONLY5_0_, this_.INSTALLATIONDATE as INSTALL24_5_0_,
this_.RELEASEDDATE as RELEASE25_5_0_, this_.DISPLAYNAME as
DISPLAY26_5_0_, this_.MODIFYDATE as MODIFYDATE5_0_, this_.MODIFYUSER as
MODIFYUSER5_0_, this_.CREATEUSER as CREATEUSER5_0_ 
FROM PLUGIN this_  
WHERE
this_.PLUGINID = ‘p6UJ9A0003H2’ and this_.TYPE in (8, 23) and
this_.BASEDON is null ORDER BY this_.DEV desc, this_.MODIFYDATE desc
 
Notice it looks at both type 23 and 8.
 
Similarly, this query of the entire groups list returns everything:
http://sdatahost/sdata/slx/system/-/groups?format=html
 
Underlying query:
SELECT
* FROM (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as
SLXRN,  this_.PLUGINID as PLUGINID5_0_, this_.NAME as NAME5_0_,
this_.FAMILY as FAMILY5_0_, this_.TYPE as TYPE5_0_, this_.USERID as
USERID5_0_, this_.CREATEDATE as CREATEDATE5_0_, this_.LOCKED as
LOCKED5_0_, this_.LOCKEDID as LOCKEDID5_0_, this_.VERSION as
VERSION5_0_, this_.SYSTEM as SYSTEM5_0_, this_.ISPUBLIC as ISPUBLIC5_0_,
this_.DESCRIPTION as DESCRIP12_5_0_, this_.DATACODE as DATACODE5_0_,
this_.BASEDON as BASEDON5_0_, this_.TEMPLATE as TEMPLATE5_0_,
this_.AUTHOR as AUTHOR5_0_, this_.COMPANY as COMPANY5_0_,
this_.COMPANYVERSION as COMPANY18_5_0_, this_.BASEDONCOMPANY as
BASEDON19_5_0_, this_.BASEDONCOMPANYVERSION as BASEDON20_5_0_,
this_.RELEASED as RELEASED5_0_, this_.DEV as DEV5_0_, this_.READONLY as
READONLY5_0_, this_.INSTALLATIONDATE as INSTALL24_5_0_,
this_.RELEASEDDATE as RELEASE25_5_0_, this_.DISPLAYNAME as
DISPLAY26_5_0_, this_.MODIFYDATE as MODIFYDATE5_0_, this_.MODIFYUSER as
MODIFYUSER5_0_, this_.CREATEUSER as CREATEUSER5_0_ FROM PLUGIN this_ 
WHERE this_.TYPE in (8, 23) and this_.BASEDON is null) query WHERE SLXRN
>= 1 AND SLXRN < 101 ORDER BY SLXRN

Sage has acknowledged this as a defect but unfortunately they will not address until some point in version 8.  No existing versions will receive the fix.  So if you need to get the group information through SData you need to do it in 2 parts:

1a) Add the Plugin table as an entity in the SLX entity model and expose it for SData feeds.

1b) Query the Plugin sdata feed in the dynamic endpoint by name, family, and type.  From this get the plugin ID.

2) Use the retrieved plugin ID in the system group endpoint like so: http://sdatahost/sdata/slx/system/-/groups(‘p6UJ9A0003H2’)

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!