Share
Report
8Background: I needed a way of creating a couple of calculated fields – “Total Planned Time” and “Total Actual Time” in a page for data analysis. The source table in BC is "Prod. Order Routing Line"
Being that a query cannot create a calculated column, I referenced a tutorial to create a temporary table, query, and page which is populated by the query.
Being that a query cannot create a calculated column, I referenced a tutorial to create a temporary table, query, and page which is populated by the query.
Table: “RoutingTimeQueryTable”
Query: "Fin Prod Ord Line Query"
Page: "RtgTimeQueryPage"
The following is my code which works as desired.
table 50103 RoutingTimeQueryTable
{
DataClassification = ToBeClassified;
TableType = Temporary;
fields
{
field(1; RowNo; Integer){Caption = 'Row No.';}
field(2; "Setup Time"; Decimal){Caption = 'Setup Time';}
field(3; "Run Time"; Decimal){Caption = 'Run Time';}
field(4; "Input Quantity"; Decimal){Caption = 'Input Quantity';}
field(5; "Actual Setup Time"; Decimal){Caption = 'Actual Setup Time';}
field(6; "Actual Run Time"; Decimal){Caption = 'Posted Run Time';}
field(7; "Total Planned Time"; Decimal){Caption = 'Total Planned Time';}
field(8; "Total Actual Time"; Decimal){Caption = 'Total Actual Time';}
}
keys
{
key(PK; RowNo)
{
Clustered = true;
}
}
}query 50103 "Fin Prod Ord Line Query"
{
QueryType = Normal;
Caption = 'Finished Prod Ord Rtg Lines Query';
elements
{
dataitem(ProdRtgLineExt; "Prod. Order Routing Line")
{
DataItemTableFilter = status = filter(= "Finished");
column(Setup_Time; "Setup Time"){}
column(Run_Time; "Run Time"){}
column(Input_Quantity; "Input Quantity") {}
column(Actual_Setup_Time; "Actual Setup Time"){}
column(Actual_Run_Time; "SFI Total Posted Run Time"){}
}
}
}page 50120 "RtgTimeQueryPage"
{
Caption = 'Routing Time Query';
PageType = List;
ApplicationArea = All;
UsageCategory = Lists;
SourceTable = RoutingTimeQueryTable;
layout
{
area(Content)
{
repeater(GroupName)
{
field(RowNo; Rec.RowNo){}
field("Setup Time"; Rec."Setup Time"){}
field("Run Time"; Rec."Run Time"){}
field("Input Quantity"; Rec."Input Quantity"){}
field("Actual Setup Time"; Rec."Actual Setup Time")
field("Actual Run Time"; Rec."Actual Run Time"){}
field("Total Planned Time"; Rec."Total Planned Time") { }
field("Total Actual Time"; Rec."Total Actual Time"){}
}
}
}
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."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;
}My question is around filtering on a calculated field. I would like the result to show only "Total Actual Time" values greater than 0.
I cannot place a filter on the query since this is a calculated column.
Where and how would I accomplish this?
I have tried the following to no avail though my implementation may have been incorrect:
FilterGroup()
SetFilter()
SetRange()
ColumnFilter()
Categories:
All responses (
Answers (
