Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to create a calcul...
Small and medium business | Business Central, N...
Answered

How to create a calculated column in a Query?

(1) ShareShare
ReportReport
Posted on by JB-10121651-0 12
I would like to create a calculated column in a query object.  More specifically I am looking to create a new column called "TotalPlannedTime" from the "Prod. Order Routing Line" table. 
 
This column will be a combination of (3) columns: "Setup Time", "Run Time", and "Input Quantity".
 
The following is the function for calculating "TotalPlannedTime"
 
"Setup Time" + "Run Time" * "Input Quantity"
 
The following is my initial code sans "TotalPlannedTime"
query 50103 "Rel Prod Ord Line Data"
{
    elements
    {
        dataitem(ProdRtgLineExt; "Prod. Order Routing Line")
        {
            DataItemTableFilter = status = filter(<> "Finished");
            column(SystemRowVersion; SystemRowVersion) { }
            column(Status; Status) { }
            column(Prod__Order_No_; "Prod. Order No.") { }
            column(Operation_No_; "Operation No.") { }
            column(No_; "No.") { }
            column(Description; Description) { }
            column(Setup_Time; "Setup Time") { }
            column(Run_Time; "Run Time") { }
            column(Input_Quantity; "Input Quantity") { }
        }
    }
}
 
Is it possible to create a calculated column in a Query?  If so, how is this accomplished?
Categories:
Dynamics 365 Business Central
  • JB-10121651-0 Profile Picture
    JB-10121651-0 12 on at
    How to create a calculated column in a Query?
    Hi, Zhu.  I followed your tutorial exactly and the result works perfectly.
     
    I used the following trigger code in my page "RtgTimeQueryPage"
      
        trigger OnOpenPage()
    var
        RtgTimeQry: Query "Fin Prod Ord Line Query";
    begin
        if RtgTimeQry.Open() then begin
            while RtgTimeQry.Read() do begin
                Rec.Init();
                Rec.RowNo := Rec.RowNo + 1;
                Rec."Prod. Order No." := RtgTimeQry.Prod__Order_No_;
                REc."Operation No." := RtgTimeQry.Operation_No_;
                Rec."No." := RtgTimeQry.No_;
                Rec."Work Center No." := RtgTimeQry.Work_Center_No_;
                Rec.Description := RtgTimeQry.Description;
                Rec."Setup Time" := RtgTimeQry.Setup_Time;
                Rec."Run Time" := RtgTimeQry.Run_Time;
                Rec."Input Quantity" := RtgTimeQry.Input_Quantity;
                Rec."Actual Setup Time" := RtgTimeQry.Actual_Setup_Time;
                Rec."Actual Run Time" := RtgTimeQry.Actual_Run_Time;
                Rec."Total Planned Time" := RtgTimeQry.Setup_Time + RtgTimeQry.Run_Time * RtgTimeQry.Input_Quantity;
                Rec."Total Actual Time" := RtgTimeQry.Actual_Setup_Time + RtgTimeQry.Actual_Run_Time;
                Rec.Insert();

            end;
                RtgTimeQry.Close();
        end;
    end;
    I do have a follow on question, however, and I will follow up in a separate post.
     
    Many thanks!
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 5,947 Moderator on at
    How to create a calculated column in a Query?
    You can’t create calculated columns directly in a Query.
  • Verified answer
    YUN ZHU Profile Picture
    YUN ZHU 75,673 Super User 2024 Season 2 on at
    How to create a calculated column in a Query?
    There is no way, because the query cannot contain any calculations. You need to use page.
    For example, 
    Dynamics 365 Business Central: Using the query as a data source for a page (Query.Open Method)
     
    Thanks.
    ZHU

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,661 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,379 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans