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 / 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. 8,786 Super User 2025 Season 1 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 77,925 Super User 2025 Season 1 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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,307 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans