Published On: February 25, 2022Categories: Innovation, Technical

You might have had a requirement to provide only the last record by CreatedDateTime field from a custom data entity, but you couldn’t accomplish it with what would be obvious solutions to the problem, i.e.:

  • Using query as data source on the data entity:
  • Not possible to use query data source on data entity;
  • Using view built out of query as data entity data source:
  • For some reason the view omits the First Only setting set on the query data source and it returns all the records from the query.

So the solution would be to use view as data entity data source and create a computed column with ranking based on the CreatedDateTime field from the data source from which we want to select the first only record.

Technical blog post 1

Technical blog post 2

Add the following method for the computed column in the code of the View:

 

public class WorkflowTrackingView extends common

{ public static server str compColRankByCreatedDateTime()

{

str ret;

DictView dv =new DictView(tableNum(WorkflowTrackingView)); ret = 'RANK () OVER (ORDER BY '+dv.computedColumnString('WorkflowTrackingTable', 'CreatedDateTime')+' DESC)';

return ret;

}

}

The next step would be to use this new field on the data entity as a range with value of 1 as according to the computed column our latest record would be of rank 1.

Technical blog post 3

Technical blog post 4 1

And voila, you have a first only function on a data entity 😊