Question Status

Suggested Answer
snoppy asked a question on 15 Jun 2017 5:40 PM

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!

Reply
Suggested Answer
Tharanga Chandrasekara responded on 15 Jun 2017 7:54 PM

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.

Reply
snoppy responded on 16 Jun 2017 9:51 AM

Here is my code:


IF ShowLastInvoice THEN BEGIN
"Cust. Ledger Entry".SETRANGE("Document Type","Document Type"::Invoice);
postingdate := "Cust. Ledger Entry"."Posting Date";

Reply
Suggested Answer
Jens Glathe responded on 16 Jun 2017 12:36 PM

Hi snoppy,

two things:

- 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

Jens

Reply
Suggested Answer
Tharanga Chandrasekara responded on 16 Jun 2017 6:24 PM

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";
END;

** Check on the key because it will directly affect the performance of the report. 

** Use a separate record variable (not the report data item)

Reply
snoppy responded on 19 Jun 2017 11:22 AM

Thank you!

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!

Reply
snoppy responded on 19 Jun 2017 5:14 PM

I also tried to use the variable instead of the dataitem, but the posting date field is blank when run the report.

Reply
Suggested Answer
Nikolay Vityuk responded on 20 Jun 2017 2:31 AM

Hello! You have to add key consisting of 2 fields "Document Type","Document Type" to table "cust. ledger entry" to  avoid error

or

Reply
snoppy responded on 20 Jun 2017 10:15 AM

I tried this  "Cust. Ledger Entry".SETCURRENTKEY("Document Type","Document Type","Posting Date");

But still not working. Please advise! Thank you!

Reply
Suggested Answer
Nikolay Vityuk responded on 20 Jun 2017 10:21 AM

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? 

Reply
snoppy responded on 20 Jun 2017 12:38 PM

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!

Reply
Suggested Answer
Tharanga Chandrasekara responded on 21 Jun 2017 7:44 AM

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)

Reply
snoppy responded on 22 Jun 2017 3:31 PM

I added the two rows, show and hide  is based on the following expressions:

One is

=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!

Reply
Mahesh responded on 23 Jun 2017 2:39 AM

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.

Reply
snoppy responded on 27 Jun 2017 4:17 PM

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);

Reply
Suggested Answer
Tharanga Chandrasekara responded on 28 Jun 2017 7:58 AM

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

Reply
Suggested Answer
Tharanga Chandrasekara responded on 15 Jun 2017 7:54 PM

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.

Reply
Suggested Answer
Jens Glathe responded on 16 Jun 2017 12:36 PM

Hi snoppy,

two things:

- 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

Jens

Reply
Suggested Answer
Tharanga Chandrasekara responded on 16 Jun 2017 6:24 PM

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";
END;

** Check on the key because it will directly affect the performance of the report. 

** Use a separate record variable (not the report data item)

Reply
Suggested Answer
Nikolay Vityuk responded on 20 Jun 2017 2:31 AM

Hello! You have to add key consisting of 2 fields "Document Type","Document Type" to table "cust. ledger entry" to  avoid error

or

Reply
Suggested Answer
Nikolay Vityuk responded on 20 Jun 2017 10:21 AM

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? 

Reply
Suggested Answer
Tharanga Chandrasekara responded on 21 Jun 2017 7:44 AM

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)

Reply
Suggested Answer
Tharanga Chandrasekara responded on 28 Jun 2017 7:58 AM

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

Reply