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

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, ... / complicated display me...
Finance | Project Operations, Human Resources, ...
Answered

complicated display method filtering

(3) ShareShare
ReportReport
Posted on by dark_knight
Hello,
 
I have a requirement of such difficult display method to be able to sort on and filter. Standard table ITMLine. method lineStatus()
 
 

   public display ITMLineStatus lineStatus()

 

   {

       ITMLineStatus shipLineStatus;  

       switch (this.RefTableId)

       {

           case tableNum(PurchLine):                

               shipLineStatus = this.lineStatusByPurchLine();

               break;

           case tableNum(InventTransferLine):                

               shipLineStatus = this.lineStatusInventTransferLine();                

               break;

           default:

               exceptionTextFallThrough();

       }

       return shipLineStatus;

   }

Therefore, I'll need view. But since the method is difficult involving if clauses and different tables is it possible to rewrite it using single computed column? As computed column is just SQl query then I guess it's not possible to achieve because the method is complicated? Or what should be the approach? 
 
Thanks.
Categories:
Dynamics 365 Finance
Microsoft Dynamics AX
I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb Most Valuable Professional on at
    Yes, it can be done. You're wrong in thinking that T-SQL language so simple that it can't do things like conditions. It's actually very powerful (although not all features can be used in a column definition). In this case, you can open SysComputedColumn::if() method to see how it can be done in T-SQL.
     
    I reviewed all the code called from the method and there is nothing that can't be rewritten to T-SQL (just note that I didn't look at extensions). It'll require some effort, of course, but it's not extremely complicated.
     
    Note that you can often implement some parts of the logic as views that you'll use in the computed column, rather then generating everything as T-SQL from your code.
  • dark_knight Profile Picture
    dark_knight on at
    thank you Martin
     
    this last part of yours
     

    Note that you can often implement some parts of the logic as views that you'll use in the computed column, rather then generating everything as T-SQL from your code.

     
    what do you mean? could you pls clarify?
     
    As I see now, everything from method can be rewritten using one single computed column, right? for this I need to use SysComputedColumn::if() and other functions, right?
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb Most Valuable Professional on at
    Yes, I already told you that is possible.
     
    But while writing the whole as a single large piece of T-SQL, you may be able to simplify it and write just some part is in SQL. For example, you could design AOT views returning PurchStatus and RemainStatus for each ITMLine, which is easier than designing T-SQL code for it and then writing X++ generating this SQL. But you don't have to - there are simply design decisions to make.
     
    Don't think about X++ functions such as  SysComputedColumn::if() yet. Before writing X++ generating SQL code, you need to know which SQL code you want to get. Design and test SQL code and only then write X++ code generating it.
  • Sohaib Cheema Profile Picture
    Sohaib Cheema User Group Leader on at
    In the last paragraph/lines, Martin is telling you about OOP(object Oriented Programming) approach. Rather than writing one big method (for the computed column), first break your work into multiple parts, for every part, try to make a view (if that is possible). eventually combine those parts/views into one computed column. This will make coding easy for you and it would make it look clean work. This is also sometimes due to the reasons that writing a big computed column with complex t-sql of nested level can be tricky at times, when done in X++. On it's own T-SQL is very easy to do (outside the X++)
     
  • dark_knight Profile Picture
    dark_knight on at
    many thanks for your help!

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

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans