How to: add TableName/Id to Union View in Dynamics 365 Finance and Operations

Views (167)

vinitgoyal2005

6,332

Like(1)

Report

There is a Union view of CustTrans and VendTrans tables. The requirement is to display which record is coming from CustTrans or VendTrans. The best option would be to simply add the TableId column to Union View. But when you add the TableID, you get the error: "Mapped view field cannot be set to TableId"

This is because, in the SQL table, TableId field doesn't exist on the actual table but rather it is held in SQLDictionary and few other SYS tables.

So the workaround is to create a computed column in View as below:

In Union View, BranchNum gives the table number from the Union query. The corresponding SQL View definition looks like:

SELECT T1.ACCOUNTNUM AS ACCOUNTNUM, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('CustTrans') AS NVARCHAR(10))) AS TABLE_

FROM CUSTTRANS T1

UNION

SELECT T1.ACCOUNTNUM AS ACCOUNTNUM, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('VendTrans') AS NVARCHAR(10))) AS TABLE_

FROM VENDTRANS T1

This was originally posted here.