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

vnproc
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.

Please help me with the solution.
Thanks so much
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
0
Comments
-
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.
1 -
Thank you. But it's not work0
-
if you filter the date to 'first to last', won't that include all entries? Might as well leave the date filter out altogether0
-
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
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions