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

vnproc

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



Please help me with the solution.

Thanks so much

TallyHo

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.

vnproc

Thank you. But it's not work

DenSter

if you filter the date to 'first to last', won't that include all entries? Might as well leave the date filter out altogether

DenSter

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.