How to filter the customer's first sales invoice in Cust.Ledger Entry

vnprocvnproc Member Posts: 41
Hi EveryBody

I am making a report to filter customers with the first and last sales day arising from date to date and calculating the total number of sales invoices.
[b]Cust. Ledger Entry - OnAfterGetRecord()[/b]

"Cust. Ledger Entry".SETRANGE("Posting Date",0D,GETRANGEMIN("Date Filter"));
"Cust. Ledger Entry".SETRANGE("Cust. Ledger Entry"."Document Type","Cust. Ledger Entry"."Document Type"::Invoice);
IF "Cust. Ledger Entry".FINDFIRST THEN
    TotalSettlement += "Cust. Ledger Entry".Amount;

"Cust. Ledger Entry".RESET;
"Cust. Ledger Entry".SETRANGE("Posting Date",GETRANGEMIN("Date Filter"),GETRANGEMAX("Date Filter"));
"Cust. Ledger Entry".SETRANGE("Cust. Ledger Entry"."Document Type","Cust. Ledger Entry"."Document Type"::Invoice);
IF "Cust. Ledger Entry".FINDFIRST THEN
    TotalPayment += "Cust. Ledger Entry".Amount;
But when running report, the Date Filter error

44ey304b57h9.png

Please help me with the solution.

Thanks so much


Comments

  • TallyHoTallyHo Member Posts: 383
    don't put this code onaftergetrecord, put it onpredataitem
    start your code with:
    "Cust. Ledger Entry".setfilter("Date Filter",010120D,311220D);
    end your code with:
    error('Result: %1 %2', TotalSettlement,TotalPayment)
    And see what happens.

  • vnprocvnproc Member Posts: 41
    Thank you. But it's not work
  • DenSterDenSter Member Posts: 8,304
    if you filter the date to 'first to last', won't that include all entries? Might as well leave the date filter out altogether
  • DenSterDenSter Member Posts: 8,304
    edited 2020-05-08
    Just some tips:
    • GETRANGEMIN and GETRANGEMAX gets the minimum and maximum values of a filter range. In your sample, you don't have a value in your date filter, so that's why the system is giving you an error
    • To get the minimum date and the maximum date from a set of records, you need to first sort the records by the date, then do a FINDFIRST to read the first record, and then do a FINDLAST to read the last record. You can then use the date field value to set the filters. I don't understand why you would set that filter though, because with those values you will get all records anyway, so that filter is not effective
    I didn't run this report, but this shows where to put the code and how to use some of the AL commands that you are trying to use
    report 50100 MyReport
    {
        Caption = 'My Sample CLE Report';
        UsageCategory = Lists;
        ApplicationArea = All;
    
        dataset
        {
            dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
            {
                // use the OnPreDataItem trigger because this runs before the
                // records are retrieved from the database
                // The OnAfterGetRecord trigger is too late
                trigger OnPreDataItem()
                var
                    MyCLE: Record "Cust. Ledger Entry";
                    StartDate: Date;
                    EndDate: Date;
                    DateRangeMsg: Label 'Start date is %1 and the end date is %2';
                begin
                    // using a separate variable to leave the dataitem alone
                    MyCLE.Reset();
                    // sorting the table to get them in the right order
                    MyCLE.SetCurrentKey("Posting Date");
    
                    // get the first record for the lowest data
                    if MyCLE.FindFirst() then
                        StartDate := MyCLE."Posting Date"
                    else
                        StartDate := 0D;
    
                    // get the last record for the highest date
                    if MyCLE.FindLast() then
                        EndDate := MyCLE."Posting Date"
                    else
                        EndDate := 99991231D;
    
                    // Now set the date range filter for the dataitem
                    "Cust. Ledger Entry".SetRange("Posting Date", StartDate, EndDate);
    
                    // you could also use SetFilter
                    "Cust. Ledger Entry".SetFilter("Posting Date", '%1..%2', StartDate, EndDate);
    
                    // now you could do GetRangeMin and GetRangeMax
                    Message(DateRangeMsg, "Cust. Ledger Entry".GetRangeMin("Posting Date"),
                                            "Cust. Ledger Entry".GetRangeMax("Posting Date"));
                end;
            }
        }
    }
    
    You should really have a senior that can answer these types of questions. If you don't have a senior, then I would suggest that you get the programming book by David Studebaker.
Sign In or Register to comment.