Simulate DEX_ROW_ID in a SQL View Using ROW_NUMBER() – #MSDYNGP

I have a requirement in which I have to access a SQL view from within my customisation dictionary, in order to create a custom lookup for users to select a value based on an Extender form and an Extender lookup. Easiest option is to create an Extender view (which in turn creates a SQL view for us).

Now, this is the view that I am suppose to refer to from my custom dictionary. Dexterity allows us to refer to any SQL object by simply create a TABLE definition and mention the SQL object (table or view) name as the physical name.

Dex View

Everything looks perfect till you actually see below error messages at runtime:

Dex View - Error 1

Dex View - Error 2

Error message is quite obvious; you do not have DEX_ROW_ID in that SQL view that you are referring to. Every single Dexterity table must have DEX_ROW_ID at the backend. It cannot afford to not have one.

So how am I going to resolve this? By simply adding a record number dynamically to the SQL view created by Extender. How to do that? By adding the T-SQL function ROW_NUMBER(). This is how I achieved it:



Definition of ROW_NUMBER() can be found here: ROW_NUMBER (Transact-SQL).

A simple yet powerful SQL function has given me the power to do what I wanted in no time. Oh, and my custom lookup referring to this view is working like a charm. Users are happy and so am I.


Perils of Using of DEX_ROW_ID on Customization / Integration – David

This one is certainly a nasty issue, if we are going to use DEX_ROW_ID value on customization or integration.

I have personally fallen into a trap by using DEX_ROW_ID on my customization. But now, it’s all wisdom out of that bad experience.

Only time when I normally depend on a DEX_ROW_ID is to sort records to understand which record was entered last in the table. That itself may not be always correct. So we may have to add other logical fields that are relevant to a particular record.

For instance, if I have to list out transactions entered on a particular day and check in the order in which the records are created, I would use the Transaction Date and DEX_ROW_ID as SORT ORDER.

There are many other reasons, but restrict yourself to use DEX_ROW_ID only for selecting records and analyze it.