Building Lists without reference to Metadata in the ASP COM API

2 minute read time.
Custom Build List
Above is a picture of a new list added into the My CRM area of Sage CRM.

The code below shows how a list can be created using the ASP COM API.

var intRecordId = CRM.GetContextInfo("user","user_userid");
var Arg = "oppo_assigneduserid="+intRecordId;

var myBlock = CRM.GetBlock("List");
with (myBlock)
//Only use the myBlock.ViewName property with a list block created dynamcially.
//ViewName = "vListCommunication";
SelectSQL = "select * from opportunity";

var oppo_stageBlock = myBlock.AddGridCol('oppo_stage');
var oppo_statusBlock = myBlock.AddGridCol('oppo_status');
var oppo_primarycompanyidBlock = myBlock.AddGridCol('oppo_primarycompanyid');
var oppo_primarypersonidBlock = myBlock.AddGridCol('oppo_primarypersonid');
var oppo_assigneduseridBlock = myBlock.AddGridCol('oppo_assigneduserid');
var oppo_descriptionBlock = myBlock.AddGridCol('oppo_description');

with (oppo_statusBlock)
ShowSelectAsGif = true;
JumpEntity= "Opportunity";

with (oppo_primarycompanyidBlock)
AllowOrderBy = true;
JumpEntity= "Company";


First we can see that we can create lists without an underlying meta data definition. Secondly we can see that we can set either the data to come from a View or from an SQL statement.

The columns are then added into the List (and therefore must exist in the underlying SQL statement).

The Developers Guide lists the properties that you can set for any of the columns.

The default ordering of the list (before any properties are set) as shown by the SQL in the log is

select TOP 11 * from opportunity WHERE ((oppo_secterr is null OR (oppo_secterr>=-1610612729 AND oppo_secterr=-2147483639 AND oppo_secterr
The default ORDER BY is oppo_stage which is the first column in the list.
Following the setting of the Order By using the properties of the oppo_primarycompanyid field with the code:
with (oppo_primarycompanyidBlock)
AllowOrderBy = true;
JumpEntity= "Company";

The ORDER BY changes to referencing the oppo_primarycompanyid.

select TOP 11 * from opportunity WHERE ((oppo_secterr is null OR (oppo_secterr>=-1610612729 AND oppo_secterr=-2147483639 AND oppo_secterr
  • XXXX_secterr
  • XXXX_assigneduserid (or XXXX_primaryuserid)
  • XXXX_channelid
  • XXXX_createdby

XXXX stands for the column prefix for any given table.

When using the List Block, it is NOT going to be possible to either avoid the addition of security checks or the reordering of the SQL as typified by the changing of the ORDER BY statement.

If you wish to construct a list of records that does not either use the security checks or that dynamically changes the underlying SQL, then you will need to access the data using the SQLQuery Object and assemble the HTML yourself. But that is for another blog.