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 / Microsoft Dynamics AX (Archived) / 'Real' compute...
Microsoft Dynamics AX (Archived)

'Real' computed column in view is returning 0.00 for every record

(0) ShareShare
ReportReport
Posted on by b_radlyjames 3,155

I'm pretty sure this is a problem with what I'm returning.  Just not sure how to do this:

ProjCostTransCost projcostTransCost;
TransactionID transId;

transId = SysComputedColumn::returnField(tableStr(ProjCostTransView), identifierStr(ProjCostTrans), fieldStr(ProjCostTrans, TransId));

select sum(CostAmountLedger) from projCostTransCost
where projCostTransCost.TransId == transId;

return num2str(projcostTransCost.costAmountLedger, 0, 2, DecimalSeparator::Dot, ThousandSeparator::Comma);

In SSMS, I can see the t-sql from my view for this field as:

(CAST ((0.00) AS NVARCHAR(15))) AS TOTALCOSTAMOUNTACTUAL  (Amongst the other field selections)

This would explain why I see all 0.00.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi bRradlyJames,

    It should be something like:

    str expression;

expression = ‘select sum(CostAmountLedger) from projCostTransCost where projCostTransCost.TransId = ‘ +

              SysComputedColumn::returnField(tableStr(ProjCostTransView), 
                                                 identifierStr(ProjCostTrans), 
                                                 fieldStr(ProjCostTrans, TransId));

return expression;
  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Computed column should return you SQL statemented that would be built into view and in you case  

    select sum(CostAmountLedger) from projCostTransCost

    where projCostTransCost.TransId == transId;

    returns 0 and num2str(projcostTransCost.costAmountLedger, 0, 2, DecimalSeparator::Dot, ThousandSeparator::Comma); returns 0 again, instead of sub select statement.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,878 Most Valuable Professional on at

    Your code doesn't make much sense.

    You get a field name (as usable in T-SQL code) by returnField(). Then you try to find a sum for transactions with TransID equal to the field name, which happens on synchronization.

    Then you use the number you get at the value of your computed column. Notice that it will always be a static number; even if you used a valid TransId, you would simply get a number such as 50 and your computed column would always return 50 (until the next DB synchronization, when it would be recalculated).

    I think your intention was having a subquery in your computed column returning a sum for a given TransId, but then you can't run the query in X++. X++ is there just to generate SQL code, which will be inserted to the view. Look at my blog post Subqueries in views; I tried to explain it there in detail.

  • b_radlyjames Profile Picture
    b_radlyjames 3,155 on at

    Thanks ievgen.  That is what I was looking for!

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 > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans