Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Past Due Invoice Report 1

Status
Not open for further replies.

davismar

IS-IT--Management
Apr 1, 2010
85
US
I need some help with a Past Due Invoice report.

The current report is based upon any invoices with an AGE greater than 30 days.
Here is the info I have currently:

@AGE FORMULA:
datediff("d", {JrnlHdr.TransactionDate}, CurrentDate)

@AgingBracket2 FORMULA:
if {@Age} > 7 and {@Age} < 20000 then {@AmountRemaining}

{JrnlHdr.TransactionDate} <= CurrentDate and
{@AgingBracket2} > 0.00 and
{@AmountRemaining} > 0.00 and
{@Age} > 30.00

However, now I need to incorporate certain criteria for all invoices based upon if they are PAST DUE or NOT.

Invoice # = JrnlHdr.Reference
Invoice Date = JrnlHdr.TransactionDate
Due Date = JrnlHdr.DueDate
Amount = {@AgingBracket2}
# Days = {@Age}

If {JrnlHdr.TermsDescription} in ["COD", "Due Upon Receipt”, “Net 10”, “ Net 10 Days", "Net 15”, “Net 15 Days", "Net 30", "Net 30 Days"]

Therefore:
COD, Due Upon Receipt - show the Amount if the @ Age is any value.
Net 10 days - show Amount if the @ Age is 11 days to 14 days.
Net 15 days - show Amount if the @ Age is 16 days - 29 days.
Net 30 days - show Amount if the @ Age is 30 days or greater.

Thanks in advance for the help!
 
I am not sure about reporting on {@Age} in 11 to 14 days for accounts whose terms are "Net 10 days". Isn't anything beyond 10 days in arrears, not just 10 to 14 days?

I would use the following Record Selection:
[Code Record_Selection_Formula]
(
{JrnlHdr.TermsDescription} in ["COD", "Due Upon Receipt"]
or
(
{JrnlHdr.TermsDescription} Startswith "Net 10" and
{@Age} > 10
)
or
(
{JrnlHdr.TermsDescription} Startswith "Net 15" and
{@Age} > 15
)
or
(
{JrnlHdr.TermsDescription} Startswith "Net 30" and
{@Age} > 30
)
)

[/Code]

Hope this helps

Cheers
Pete
 
This is a start, but I'm showing all invoices with prior dates. Therefore, it appears the formula is giving me all invoices.

I believe I need to do something with the @AgingBracket2 formula also?


@AgingBracket2 FORMULA:
if {@Age} > 7 and {@Age} < 20000 then {@AmountRemaining}

 
Wow, not even a "Thanks, but ...".

I just had a look at the other threads in this forum that you have started and see you have received a good deal of assistance from some very experienced Tek-Tippers yet you never given anyone a Star, have rarely thanked them for their time and assistance, and in many cases not even bothered to provide feedback on whether the solution offered worked or not.

You may care to give this some thought next time you need help and little is forthcoming.
 
I sincerely apologize....you are right. My work has been stressful and I often get caught up in fixing things...without taking time to thank many of you. Again...I AM VERY SORRY.

I will be more conscious of my actions going forward.
 
Try amending the Record Selection as follows:

[Code Record_Selection]
{@AmountRemaining} > 0 and
(
{JrnlHdr.TermsDescription} in ["COD", "Due Upon Receipt"]
or
(
{JrnlHdr.TermsDescription} Startswith "Net 10" and
{@Age} > 10
)
or
(
{JrnlHdr.TermsDescription} Startswith "Net 15" and
{@Age} > 15
)
or
(
{JrnlHdr.TermsDescription} Startswith "Net 30" and
{@Age} > 30
)
)
[/Code]

Does that fix it?


Cheers
Pete
 
[lipstick]Pete,

YES! That was the fix that I needed. The report works great now.

Thank you VERY much for your help. I really appreciate your taking time to help me out.

I'm giving you a STAR!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top