Aged Debtor Sales List

[johnnysalt]

Hello, I hope that somebody can help me. I have produced an accounting system using FM for my small design business. At the moment I produce statements in the invoice database by simply performing a search for 'Unpaid' invoices (this is a particular field in the db) and client name (another field).

 

I trying to set 4 fields that show the following at the end of the layout based on the search above,

 

30 - 60 days outstanding. Total amount.

60 - 90 days outstanding. Total amount.

90 - 120 days outstanding. Total amount.

120 plus outstanding. Total amount.

 

any help will be much appreciated.

 

Johnny

[David Head]

As with most things, there are lots of ways to get to where you want. It depends on what other data might be useful along the way. Here is one method that could be condensed into fewer fields if required.

 

InvOutstanding (calculation, number)

= AmountInvoiced - AmountPaid

 

DaysOverdue (calculation, number, unstored)

= Case(InvOutstanding > 0, Get (CurrentDate) - DateInvoiced, 0)

 

Outstanding3060 (calculation, number)

= Case(DaysOverdue >= 30 and DaysOverdue

 

Outstanding3060.st (summary field)

= Total of Outstanding3060

 

Outstanding6090 (calculation, number)

= Case(DaysOverdue >= 30 and DaysOverdue

 

Outstanding6090.st (summary field)

= Total of Outstanding6090

 

Outstanding90120 (calculation, number)

= Case(DaysOverdue >= 30 and DaysOverdue

 

Outstanding90120.st (summary field)

= Total of Outstanding90120

 

Outstanding120 (calculation, number)

= Case(DaysOverdue >= 120, InvOutstanding)

 

Outstanding120.st (summary field)

= Total of Outstanding120

 

See how you go with that. :cool: