Job List Page. Sum of 2 Fields (AL Code)

(1) Share

Report

Posted on by LearnBC

224

Dear Experts

Am really new to this BC and coding, really using this forum and online platform for my learning. pls kindly request all for you kind help in my issues and correcting it in the correct way .

Right now am trying to How we can bring the Total of Actual Total cost and Total of Billable Price (from Jobs Planning lines -Job Task table (1001)) on JOB List page. Actually Total can see also at right side from Job Cost Factbox (1030, CardPart) also

am trying like this... pls correct me to get the desired result in attached code

 pageextension 60017 JOBPageExtension50000_50951 extends "Job List"
 {
     layout
     {
     addafter("No.")
     {

    field("Actual Total Cost"; Rec."Actual Total Cost")
    {
                ApplicationArea = All;
                Width = 14;
                Caption = 'Acutal Total Cost';
                Editable = false;

     }

            field ("Billable Total Price";Rec."Billable Total Price")
            {
                ApplicationArea = All;
                Width = 14;
                Editable = false;
            }
        }
        
    }           trigger OnafterGetRecord()
                 var
                    JobSum : Record "Job Task";
                    JobTotal : Record "Job Ledger Entry";
                    TotalCost :  Decimal;
                    TotalBill   : Decimal;

                begin

                    JobSum.SetRange("Job No.");
                    JobTotal.SetRange("Job No.");
                    JobSum.CalcSums(Jobsum.Billable Total Price);
                    JobSum.CalcSums(Jobsum.Actual Total Cost);


                    end;
                    var

                        Jobsum  Decimal;
    }

    
}

I have the same question (0)

All responses (36)

Answers (1)

LearnBC 224 on at

Like (0)

Report

Hi all,

Kindly requesting your valuable help one this

Regards

Was this reply helpful? Yes No
Suggested answer

Alok.kulshreshtha 934 on at

Like (0)

Report

Hi,

In your code please correct setrange. It should be like below

Jobsum.setRange("job no", "job no");

Jobtotal.SetRange("Job no", "Job no");

Please note first Job no colum is from jobsum and Job Total table, so it may possible column name to store job no in this is different. Please check relevant column name and update

Regards

Alok kulshrestha

Please verify my post if you find it is helpful

Was this reply helpful? Yes No
LearnBC 224 on at

Like (0)

Report
Hello Alok

as you said, i tried like that but still error is coming.

pageextension 60017 JOBExtension50000_50951 extends "Job List" { layout { addafter("No.") { field("Actual Total Cost"; Rec."Actual Total Cost") { ApplicationArea = All; Width = 14; Caption = 'Acutal Total Cost'; Editable = false; } field("Billable Total Price"; Rec."Billable Total Price") { ApplicationArea = All; Width = 14; Editable = false; } } } trigger OnafterGetRecord() var JobSum: Record "Job Task"; JobTotal: Record "Job Ledger Entry"; TotalCost: Decimal; TotalBill: Decimal; begin JobSum.SetRange("Job No.","Job No"); JobTotal.SetRange("Job No."); JobSum.CalcSums(Jobsum.Billable Total Price); JobSum.CalcSums(Jobsum.Actual Total Cost); end; var Jobsum Decimal; } }

Was this reply helpful? Yes No
Suggested answer

Alok.kulshreshtha 934 on at

Like (0)

Report

Please make same change for jobtotal.setrange as well.

Also could you add snap for error which you are getting

Regards

Alok

Was this reply helpful? Yes No
LearnBC 224 on at

Like (0)

Report
Dear Alok,

i have changed the code and now the fields are coming on list page. but the values are not coming.

pageextension 60017 JOBExtension50000_50951 extends "Job List" { layout { addafter("No.") { field("No. Series08206"; Rec."No. Series") { ApplicationArea = All; Width = 8; Caption = 'No. Series'; } field("Starting Date72908"; Rec."Starting Date") { ApplicationArea = All; Width = 10; Caption = 'Starting Date'; } field("Ending Date60581"; Rec."Ending Date") { ApplicationArea = All; Width = 14; Caption = 'Ending Date'; } field("Project Cost"; Rec."Project Cost") { ApplicationArea = All; Width = 14; Caption = 'Project Revenue'; } field("Project Expense"; Rec."Project Expense") { ApplicationArea = All; Width = 14; Caption = 'Project Expense'; } field("Creation Date71912"; Rec."Creation Date") { ApplicationArea = All; Width = 14; Caption = 'Creation Date'; } field("Actual Total Cost"; Rec."Actual Total Cost") { ApplicationArea = All; Width = 14; Caption = 'Acutal Total Cost'; Editable = false; } field("Billable Total Price"; Rec."Billable Total Price") { ApplicationArea = All; Width = 14; Editable = false; } } } trigger OnafterGetRecord() var JobSum: Record "Job Task"; JobTotal: Record "Job Ledger Entry"; JobtotalBill: Record "Job Planning Line"; ActualTotalCost: Decimal; BillableTotal: Decimal; begin // JobSum.SetRange("Job No.",JobSum."Job No."); JobTotal.SetRange("Job No.", JobTotal."Job No."); JobtotalBill.SetRange("Job No.", JobtotalBill."Job No."); JobTotal.CalcSums(JobTotal."Unit Cost (LCY)"); JobtotalBill.CalcSums(JobtotalBill."Unit Price"); ActualTotalCost := JobTotal."Unit Cost (LCY)"; BillableTotal := JobtotalBill."Unit Price"; end; }

Was this reply helpful? Yes No
Suggested answer

Alok.kulshreshtha 934 on at

Like (0)

Report

Hi,

SetRange have still some issue.

So in setrange after comma you used jototal.job no which need to replace with rec.no, and same for job total bill.

Also you are using a calcsum, so this will only work if jobtotal.unitcost and jobtotalbilll.unit price is defined as a sumindexfield.

In case not than after setrange you can use if jobtotal.findfirst than repeat

Actualtotalcost+=jobtotal.unit cost(lcy)

until jobtotal.next=0;

And similar for jobtotalbill

Regards,

Alok

Was this reply helpful? Yes No
LearnBC 224 on at

Like (0)

Report
Hello..

After Modifying the Code also, still there is some issues. Kindly help me to fix it

pageextension 60017 JOBExtension50000_50951 extends "Job List" { layout { addafter("No.") { field("No. Series08206"; Rec."No. Series") { ApplicationArea = All; Width = 8; Caption = 'No. Series'; } field("Starting Date72908"; Rec."Starting Date") { ApplicationArea = All; Width = 10; Caption = 'Starting Date'; } field("Ending Date60581"; Rec."Ending Date") { ApplicationArea = All; Width = 14; Caption = 'Ending Date'; } field("Project Cost"; Rec."Project Cost") { ApplicationArea = All; Width = 14; Caption = 'Project Revenue'; } field("Project Expense"; Rec."Project Expense") { ApplicationArea = All; Width = 14; Caption = 'Project Expense'; } field("Creation Date71912"; Rec."Creation Date") { ApplicationArea = All; Width = 14; Caption = 'Creation Date'; } field("Actual Total Cost"; Rec."Actual Total Cost") { ApplicationArea = All; Width = 14; Caption = 'Acutal Total Cost'; Editable = false; } field("Billable Total Price"; Rec."Billable Total Price") { ApplicationArea = All; Width = 14; Editable = false; } } } trigger OnafterGetRecord() var JobSum: Record "Job Task"; JobTotal: Record "Job Ledger Entry"; JobtotalBill: Record "Job Planning Line"; ActualTotalCost: Decimal; BillableTotal: Decimal; LineCount: Integer; begin JobTotal.Reset(); JobtotalBill.Reset(); LineCount := 0; JobTotal.SetRange("Job No.", Rec."No."); JobtotalBill.SetRange("Job No.", Rec."No."); if JobTotal.FindFirst() then repeat LineCount = 1; ActualTotalCost = JobTotal."Unit Cost (LCY)"; Unitl JobTotal.Next() = 0; End; if JobtotalBill.FindFirst() then LineCount = 1; BillableTotal := JobtotalBill."Unit Price"; Until jobTotalBill.Next() = 0; end; end; }

Was this reply helpful? Yes No
Suggested answer

Alok.kulshreshtha 934 on at

Like (0)

Report

Hi,

Could you mention here what issue you are facing now after change in code.

One issue i can see is in line where you written

Billable total := jobtotalbill.unit price

It should be billableTotal += jobtotalbill."unit price"

Regards

Alok

Was this reply helpful? Yes No
LearnBC 224 on at

Like (0)

Report
Hello..

issue means compilation itself error are coming i checked many times may be pls correct me if am doing wrong or not able to identify from my side

pageextension 60017 JOBExtension50000_50951 extends "Job List" { layout { addafter("No.") { field("No. Series08206"; Rec."No. Series") { ApplicationArea = All; Width = 8; Caption = 'No. Series'; } field("Starting Date72908"; Rec."Starting Date") { ApplicationArea = All; Width = 10; Caption = 'Starting Date'; } field("Ending Date60581"; Rec."Ending Date") { ApplicationArea = All; Width = 14; Caption = 'Ending Date'; } field("Project Cost"; Rec."Project Cost") { ApplicationArea = All; Width = 14; Caption = 'Project Revenue'; } field("Project Expense"; Rec."Project Expense") { ApplicationArea = All; Width = 14; Caption = 'Project Expense'; } field("Creation Date71912"; Rec."Creation Date") { ApplicationArea = All; Width = 14; Caption = 'Creation Date'; } field("Actual Total Cost"; Rec."Actual Total Cost") { ApplicationArea = All; Width = 14; Caption = 'Acutal Total Cost'; Editable = false; } field("Billable Total Price"; Rec."Billable Total Price") { ApplicationArea = All; Width = 14; Editable = false; } } } trigger OnafterGetRecord() var JobSum: Record "Job Task"; JobTotal: Record "Job Ledger Entry"; JobtotalBill: Record "Job Planning Line"; ActualTotalCost: Decimal; BillableTotal: Decimal; LineCount: Integer; begin JobTotal.Reset(); JobtotalBill.Reset(); LineCount := 0; JobTotal.SetRange("Job No.", Rec."No."); JobtotalBill.SetRange("Job No.", Rec."No."); if JobTotal.FindFirst() then repeat LineCount = 1; ActualTotalCost = JobTotal."Unit Cost (LCY)"; Unitl JobTotal.Next() = 0; End; if JobtotalBill.FindFirst() then LineCount = 1; BillableTotal = JobtotalBill."Unit Price"; Until jobTotalBill.Next() = 0; end; }

Was this reply helpful? Yes No
Suggested answer

Alok.kulshreshtha 934 on at

Like (0)

Report

Hi,

Sorry I think i have used wrong expression.

Please replace += to =+ for both job total and billable total both and try

Was this reply helpful? Yes No