Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
Hi experts, how to show the last invoice posting date from the cust. ledger entry. I added the posting date on the report and it always shows the first invoice posting date. Please advise! Thank yoU!
Can you share us how you add the code in the report?
You can set the current key based on posting date and then call FINDLAST. It will get you the last record of the record set.
Here is my code:
IF ShowLastInvoice THEN BEGIN"Cust. Ledger Entry".SETRANGE("Document Type","Document Type"::Invoice);postingdate := "Cust. Ledger Entry"."Posting Date";
- You need a key like "Document Type,Customer No.,Posting Date" for the C/L entry. Posting date must be the last key element.
- You need to perform a findlast (preferrably on a record variabe that is not your data item) to find the last invoice.
You can also create a flowfield on the customer table for this. It would be more "oldschool NAV style"
with best regards
You can use below code sample :
IF ShowLastInvoice THEN BEGIN
CustLedgEntry.SETCURRENTKEY("Document Type",Posting Date");
CustLedgEntry.SETRANGE("Document Type","Document Type"::Invoice);
IF CustLedgEntry.FINDLAST THEN
postingdate := CustLedgEntry."Posting Date";
** Check on the key because it will directly affect the performance of the report.
** Use a separate record variable (not the report data item)
Below is what I have done and there is an error when run the report.
If "cust. ledger entry".FINDLAST THEN ShowLastInvoice := TRUE; IF ShowLastInvoice THEN BEGIN "cust. ledger entry".SETCURRENTKEY("Document Type","Posting Date"); "cust. ledger entry".SETRANGE("Document Type","Document Type"::Invoice); IF "cust. ledger entry".FINDLAST THEN postingdate := "cust. ledger entry"."Posting Date";END;Error: The cust. ledge entry table does not have an active key that starts with the following field: document type, posting date.
Please advise! Thank you!
I also tried to use the variable instead of the dataitem, but the posting date field is blank when run the report.
Hello! You have to add key consisting of 2 fields "Document Type","Document Type" to table "cust. ledger entry" to avoid error
I tried this "Cust. Ledger Entry".SETCURRENTKEY("Document Type","Document Type","Posting Date");
But still not working. Please advise! Thank you!
You must change the table 21 object, not the code
Open the table in Designer
Click View -> Keys
Add corresponding key
Which version of Nav do you use?
Looks like it is working now. But when data are retrieved, if there are lots of posting date related to the customer, it will show only one posting date, and hide the rest of them, but there are lots of blank space and even some of them are page long. How to fix it? Thank you!
Seems your dataset is not getting filtered properly. You can set the visibility settings in the RDLC layout for the rows. (I always recommend to send the proper dataset to report viewer as it will increase report performance)
I added the two rows, show and hide is based on the following expressions:
=Iif (fields!showlast invoice.value, True,False)
The other one is
=Iif (fields!showlast invoice.value, false,True)
But this time, no data are retrieved. Thank you!
At first, you try to display these values on the rows by way of setting the values in Expression, using that you will be able to write the correct logic for hiding and displaying the respective rows.
How to retrieve the correct posting date if the customer has not purchased for 30 days starting from today? Please advise! Thank you!
CustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date"); CustLedgEntry.SETRANGE("Customer No.","No."); CASE HowLong OF HowLong::"30 Days":CustLedgEntry.SETRANGE("Posting Date",TODAY-30,TODAY);
You can check CALCDATE Function in Dynamics NAV. Rather than we giving you the answer, it would be good if you can understand by yourself. Please let me know if you can't get it work. https://msdn.microsoft.com/en-us/library/dd301368.aspx