NULL Values in Data Entity / View

Some days ago a colleague of mine had an interesting question for me. “Clearly there’s data in the underlying table but this Data Entity’s view doesn’t provide any values in several fields. Do you know why?” Here’s what we found.

The example for this is in standard and so I can simply use the Data Entity PaymentTermEntity to showcase it.

Behavior and Observations

If you use the Data Entity to export data the columns CustomerDueDateUpdatePolicy, CutoffDayOfMonth and VendorDueDateUpdatePolicy are always empty.
PaymentTermEntity NULL Value Fields
After checking all the fields’ properties etc. the next thing you want to check is the underlying view with the same name as the Data Entity (every of those has an underlying view with the same name). I use SQL Server Management Studio for that.
PaymentTermEntity View SQL Server Management Studio
After it’s clear that the view actually doesn’t provide the values (there are values in the table PaymTerm) it’s a good idea to check the view’s definition (again using SSMS):

CREATE VIEW [dbo].[PAYMENTTERMENTITY] AS 
SELECT T1.ADDITIONALMONTHS AS ADDITIONALMONTHSFORCUTOFFDATE, 
[...]
T2.RECID AS RECID#2, 
CAST(NULL AS INT) AS VENDORDUEDATEUPDATEPOLICY, 
CAST(NULL AS INT) AS CUTOFFDAYOFMONTH, 
CAST(NULL AS INT) AS CUSTOMERDUEDATEUPDATEPOLICY 
FROM PAYMTERM T1 
LEFT OUTER JOIN DIMENSIONCOMBINATIONENTITY T2 
ON(( T1.CASHLEDGERDIMENSION  =  T2.RECID)  
AND ( T1.PARTITION  =  T2.PARTITION))

I shortened the field list a little. What you can see here is that those three fields aren’t read from the table at all but set to NULL constantly!

Reason

It took us a little while to find out about the source of this. Finally we found that the Configuration Key all three fields carry – PaymCalendar – is turned off.
PaymentTermEntity ConfigKey PaymCalendar
So if you find a view that sets fields to constant NULL values this might be due to a deactivated ConfigKey at table level of the mapped fields.
After figuring this out it seemed very familiar to me. Actually I think I’ve seen it before in AX 2012 and would guess it behaves similar there with views that contain mapped table fields that are deactivated by Configuration Key.
Also I’d like to mention that I like what the framework does here. It simply isn’t necessary to read such fields in views and waste resources. Though I just asked myself one thing. In AX 2012 you are forced to synchronize the database after activation / deactivation of Configuration Keys. I think in AX7 this isn’t the case anymore which might lead to an issue accordingly. So keep that in mind.

Thanks to Marius for the inspiring question 😉

Leave a Comment