web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Computed column showin...
Finance | Project Operations, Human Resources, ...
Answered

Computed column showing NULL values

(0) ShareShare
ReportReport
Posted on by Parag Chapre 12,254 Most Valuable Professional

Hello,

I have created a View with two tables. One is the Date table and another with EmployeeSickTable. I want to fetch data from another table in my view but that table has one to N relation.

My Date table has the below data.

From Date
01/10/2020
02/10/2020
03/10/2020
04/10/2020
05/10/2020
06/10/2020
07/10/2020
08/10/2020
09/10/2020
10/10/2020

EmployeeSickTable

         Employee Id                   SickId                   Sick%
1 1001 100
2 1002 60

SicknessDetails

SickId Sick% Valid from Valid to
1001 100 01/10/2020 05/10/2020
1001 60 06/10/2020 10/10/2020

My View Structure

DateTable >> EmployeeSickTable ( There is no join here because I want to get Employee sickness data per day)

If I add join with EmployeeSickTable >>SicknessDetails then It shows multiple lines which I want to avoid.

I want to see the below data with two table structure like DateTable >> EmployeeSickTable

My view should be like this

From Date Employee SickId Valid From Valid to Sick%
01/10/2020 1 1001 01/10/2020 10/10/2020 100
02/10/2020 1 1001 01/10/2020 10/10/2020 100
03/10/2020 1 1001 01/10/2020 10/10/2020 100
04/10/2020 1 1001 01/10/2020 10/10/2020 100
05/10/2020 1 1001 01/10/2020 10/10/2020 100
06/10/2020 1 1001 01/10/2020 10/10/2020 60
07/10/2020 1 1001 01/10/2020 10/10/2020 60
08/10/2020 1 1001 01/10/2020 10/10/2020 60
09/10/2020 1 1001 01/10/2020 10/10/2020 60
10/10/2020 1 1001 01/10/2020 10/10/2020 60

I have created a computed column like this

private static str computeSickPercent()
{

SicknessDetails sicknessDetails;

str trendDateStr = SysComputedColumn::returnField(
tableStr(MyView),
tableStr(DateTable),
fieldStr(DateTable, TrendDate)) ;

str SickIdHeader = SysComputedColumn::returnField(
tableStr(MyView),
tableStr(EmployeeSickTable),
fieldStr(EmployeeSickTable, SickId)) ;

TransDate   trendDate= str2date(trendDateStr,213);

EmployeeSickTable employeeSickTable = EmployeeSickTable::find(SickIdHeader);

select validtimestate(trendDate) sicknessDetails
where sicknessDetails.SickId == employeeSickTable.sickId;

return num2Str(sicknessDetails.Sick%,-1,-1,2,2);

}

I want to calculate Sickness % per day based on a date table but it is showing null values.

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Please use Insert > Insert Code (in the rich-formatting view) to paste source code. Like this:

    private static str computeSickPercent()
{
	SicknessDetails sicknessDetails;

	str trendDateStr = SysComputedColumn::returnField(
		tableStr(MyView),
		tableStr(DateTable),
		fieldStr(DateTable, TrendDate));

	str sickIdHeader = SysComputedColumn::returnField(
		tableStr(MyView),
		tableStr(EmployeeSickTable),
		fieldStr(EmployeeSickTable, SickId));

	TransDate trendDate = str2date(trendDateStr,213);

	EmployeeSickTable employeeSickTable = EmployeeSickTable::find(sickIdHeader);

	select validtimestate(trendDate) sicknessDetails
		where sicknessDetails.SickId == employeeSickTable.sickId;

	return num2Str(sicknessDetails.Sick%, -1, -1, 2, 2);
}

    Your code is indeed wrong. It shows that you don't fully understand the purpose of computed columns.

    Computed columns are used to generate T-SQL code, which is inserted to the view definition. This happens when you synchronize the database. When you execute the view, it doesn't call the method; it just executes the view.

    According to your code, you believe that it'll call your method when fetching the value, but it's not the case.

    Another wrong expectation is that returnField() returns a value of the field. It doesn't. What we're doing ism generating T-SQL code and returnField() will give you a field identifier (such as T1.SICKID) that you can use in T-SQL code.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Do you need a computed column at all? Wouldn't it be sufficient to outer-join SicknessDetails table?

    For example:

    select dateTable
	join employeeSickTable
		// no condition -> Cartesian product
	outer join sicknessDetails
		where sicknessDetails.SickId == employeeSickTable.SickId
		   && sicknessDetails.ValidFrom >= dateTable.DateValue
		   && sicknessDetails.ValidTo   <= dateTable.DateValue;

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 611 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 529 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 285 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans