Report most recent date Customer Ledger Entry

andy76

Hello,

I don't remember how to obtain the most recent record (Posting Date) in a table such as Customer Ledger Entry, after sorting it.

Thank you

mohana_cse06

Open Customer Card-->Customer-->Entry Statistics..
Check how Date field is calculating..

andy76

Which Date field do you mean, on code or on the screen.
On screen I don't see date fields.

Thank you

Savatage

What are you looking for? a function?

Like "FindLast"?
http://msdn.microsoft.com/en-us/library/dd355375

faizguy84

Do the sorting on posting date and use FindLast in customer ledger entry for each customer.

Regards,
Faisal Bukhari

andy76

Last requirement (changed respect previous one) is to have, on Customer Card, the date o last Posted Invoices and number of days between this date and actual day.
Do you have some suggestion to calculate that?

Thank you

faizguy84

"number of days between this date and actual day"

What does this day and actual date means????

_____________
Regards,
Faisal Bukhari

mohana_cse06

andy76 wrote:

Last requirement (changed respect previous one) is to have, on Customer Card, the date o last Posted Invoices and number of days between this date and actual day.
Do you have some suggestion to calculate that?

Thank you

And also let us know what did you try and what is not working..

andy76

I have to begin the development.
I think to create a new report that pass along Customer and Customer Ledger Entries.
Is that ok or can I find these info in other parts?

Thank you

mohana_cse06

faizguy84 wrote:

"number of days between this date and actual day"

What does this day and actual date means????

[-o< [-o<

andy76

no. of days between last invoice and today

mohana_cse06

NoofDays := TODAY - LastInvoicedDate;

:thumbsup:

Savatage

andy76 wrote:

, on Customer Card, the date of the last Posted Invoices

Create A flowfield in the Customer Table

50050 -> Last Invoice Date -> Datatype=Date
FieldClass->Flowfield
CalcFormula->Max("Cust. Ledger Entry"."Posting Date" WHERE (Document Type=FILTER(Invoice),Customer No.=FIELD(No.)))

Add the new field to customer card.

Save->Compile->Enjoy!

andy76

Very good, I made that and seems ok.

I don't understand when the field is exactly calculated.
I see that all records have this field populated also if not scrolled in the form...
When I make a new invoice for customer X, is this new field refreshed automatically.?

I ask you that because we should extract this data of Customer table via a query.

Savatage

If you're going to use it in a report you need to use the command CALCFIELDS to get it's current value.
Just like any other flowfield. ie/ Balance field, etc.
Customer.CALCFIELDS("Last Invoice Date"); needs to be added to your report to populate the field with it's current value.

http://msdn.microsoft.com/en-us/library/dd338766.aspx

If you view the field in form or table - it will be correct & updated.
It finds the largest(newest) posting date of an "Invoice" type ledger entry. Therefore giving you the last invoice date for that customer.

andy76

Ok, this means that should be better make a report of NAV than to create a SQL Query to extract data.

But if I would also want to calculate the no. of days betweend that date and today, automatically updated for all customer?
I would like to see that updated correctly in Customer List form.

Savatage

andy76 wrote:

But if I would also want to calculate the no. of days betweend that date and today.

mohana_cse06 wrote:

NoofDays := TODAY - "Last Invoice Date";

:thumbsup:

andy76

I know how to calculate it but not where to write the code to calculate it for all Customers and save it in the table...in which trigger?

Savatage

I don't know if it's a "Save to table" type of value since it constantly needs recalculating. For me the date would be enough. and the #ofdays seems like overkill. It something you can easily calc for reports but you also wanty it to show on a form.

I don;t like putting code on forms - but if it's just for a visual effect on the customer list then you have to calc it on the form.

Add a global var to the form "NoOfDays" type integer.
OnAfterGetRecord() of the form
CALCFIELDS("Last Invoice Date");
NoofDays := TODAY -"Last Invoice Date";

Drop an integer type field onto the list & change the sourceexp to "NoOfDays" or whatever name you give it.