Q: Can I sort by CreationTimeUTC using NON-RAW CFQL? I am not able to sort by CreationTimeUTC. I know it can be done using a RAW CFQL query, but is there a way using normal CFQL. Steps I tried are below:


*

*Made CreationTimeUtc show in the entity as a property as shown below (sorry for putting it in a code snippet, stackoverflow is sometimes quirky with XML and this was the only way to get it to show) 




<cf:property name="CreationTimeUtc" set="false" persistent="false" readOnLoad="true" typeName="datetime" dbType="DateTime" dataMember="false" persistenceName="_trackCreationTime" />




*Created the CFQL query as shown below (sorry for using blockquote, stackoverflow is quirky again and it wouldn't format correctly in a codeblock):



LOADONE(Amikids.TimeTracking.PunchCard punchCard) WHERE IsVoid = FALSE
  ORDER BY CreationTimeUtc DESC



*Upon building the Model I get the following error:



Error 52  CF0291: An attempt to create a persistence order by column
  snippet 'CreationTimeUtc' has been detected in procedure
  'CompanyCodeAdjustment_LoadActiveByPunchCard'. Make sure order by
  column identifiers declared in the body are valid in the context. If
  you really want to create a snippet, you should change the project,
  method or view 'checkLevel' attribute, currently set to 'Default'.

Below is the full XML for the entity.
  <cf:entity name="CompanyCodeAdjustment" namespace="Amikids.TimeTracking" categoryPath="/Amikids.TimeTracking" persistenceName="CompanyCodeAdjustment">
    <cf:property name="Id" key="true" />
    <cf:property name="PunchCard" typeName="{0}.PunchCard" relationPropertyName="CompanyCodeAdjustments" />
    <cf:property name="PunchCardAdjustmentStatus" typeName="{0}.PunchCardAdjustmentStatus" />
    <cf:property name="NewValue" />
    <cf:property name="CreationUser" set="false" persistent="false" readOnLoad="true" dataMember="false" persistenceName="_trackCreationUser" />
    <cf:property name="CreationTimeUtc" set="false" persistent="false" readOnLoad="true" typeName="datetime" dbType="DateTime" dataMember="false" persistenceName="_trackCreationTime" />
    <cf:property name="Reason" />
    <cf:property name="ReviewedBy" />
    <cf:property name="IsVoid" typeName="bool" />
    <cf:property name="CompanyName" />
    <cf:method name="LoadActiveByPunchCard" body="LOADONE(Amikids.TimeTracking.PunchCard punchCard) WHERE IsVoid = FALSE ORDER BY CreationTimeUtc DESC" />
  </cf:entity>


A: The CodeFluent Query Language (CFQL) provide a special syntax to access special columns such as the creation time column: $column.creationtime. However this is not available in the ORDER BY statement so you must use inline SQL [$Customer{TrackCreationTimeColumn.FullName}$]:
LOAD(datetime now) 
WHERE $column.creationtime &lt; @now 
ORDER BY [$Customer{TrackCreationTimeColumn.FullName}$]

<cf:entity name="Customer" namespace="Model1">
  <cf:property name="Id" key="true" />
  <cf:method name="LoadOrderedByCreationDate" body="LOAD(datetime now) WHERE $column.creationtime &lt; @now ORDER BY [$Customer{TrackCreationTimeColumn.FullName}$]" checkLevel="None" />
</cf:entity>

