I was going to call this a 'warning from history' but as you read the rest of the article you may realise that the 'updateddate' may never have been filled in.

The eWareQuery object is a fantastically useful tool for on-premise developers to use in ASP pages, table level scripts and even Self Service and external scripts. I first wrote an article about the object years ago: "The eWareQuery Object (COM API)".

The object (aka Query Object) allows developers complete freedom to carry out inserts, updates and deletes in any way that is wanted. The object runs the SQL that is passed to it and because of that it means that we are not constrained in the same way that the Record object constrains a developer. For example the Query Object can be used to create "Lookup Fields that Side Step Security".

BUT this power comes at a cost to developers. The pay off for being able to design exactly the query you want to use means that you need to cover all the things that the Record object automatically handles for you.

Consider the following code.

[code language="javascript"]
var intRecordId = CRM.GetContextInfo("opportunity","oppo_opportunityid");
var mySQL = "update opportunity set oppo_description = lower(oppo_description) where oppo_opportunityid = "+intRecordId;
var myQuery = CRM.CreateQueryObj(mySQL,"");
myQuery.ExecSQL();
[/code]

This code when run in an ASP page or Table Level Script would update the description of the opportunity in context.

BUT it would only update the field(s) mentioned in the SQL.

The same task can be written using the Record Object.

[code language="javascript"]
var intRecordId = CRM.GetContextInfo("opportunity","oppo_opportunityid");
var myRecord = CRM.FindRecord("opportunity","oppo_opportunityid="+intRecordId);
myRecord.oppo_description = myRecord.oppo_description.toUpperCase();
myRecord.SaveChanges();
[/code]

In this case because we are using the Record object the 'audit' columns are automatically updated.

These are the columns

• [xxxx_CreatedBy]
• [xxxx_CreatedDate]
• [xxxx_UpdatedBy]
• [xxxx_UpdatedDate]
• [xxxx_TimeStamp]
• [xxxx_Deleted]

The Record object when it is used for insert, update or even delete looks after the update of these column values automatically. When we use the Query object (eWareQuery Object) then we have to remember to update these fields. You can probably understand where problems can occur in security policies when the createdby information is absent and how audit trails are broken when the updatedby data is incorrect.

Note: The .NET specialised classes and the SOAP Web Services all follow the rules.

The moral of this tale is that you should only use the eWareQuery Object in circumstances where the Record Object is unsuitable. And when you do use it, then you must take full responsibility for updating the 'audit' information.