Past Due Report

[DigitalDreamer]

I was hoping someone could help. I want to be able to run a report based on a due date field i have. This field is used to keep track of when our members owe for there next membership payment. And i wanna be able to run a report on who is late with there payment. Thanks for any help you can offer.

[RussBaker]

Assuming you have a date field for when the membership payment is due, you need to find all records where this date is less than today. You can script the find by:

Enter Find Mode []

Insert Calculated result [MemberShipPaymentDateDue,"<"&DateToText(Status(CurrentDate))]

Perform Find []

This will return all record who are late. You can modify this script to find members who will fall due in within 7, 30 days etc - if you want to send them reminders. For example:

Enter Find Mode []

Insert Calculated result [MemberShipPaymentDateDue,"<"&DateToText(Status(CurrentDate) + 30)]

Perform Find []

I use this in several solutions as part of a startup script so that the user gets remineded if there are any renewal notices which need to be send out.

[DigitalDreamer]

Russ thanks for the reply. I tried what you recommended but it reports all members are late which are not. I have 2402 members in the database and it says 2397 members are late. It gives no errors when i run the script. Can i send my file to you for review and see if i missed something? Thanks for the reply.

[danjacoby]

I always create a calc field (called "CurrentDate) that returns Status(CurrentDate), then make it unstored.

Then I create a calc field ("Late") as follows:

If(PmtDueDate < CurrentDate, "Late", "")

Now you can run a script that finds all records where "Late" isn't empty.

P.S. It's possible you used ">" instead of "<"; you might want to double-check it.

[RussBaker]

Hmmm...

Works fine for me. Have you:

1. Made sure that the all the restore boxes are un-checked in your enter Find Mode and Perform Find script steps. You should get the empty square brackets at the end of the script step - []

2. Made sure you are on a layout which includes the Membership expiry date field.

See my attached file. If its still not working, send me your file.

Test Member Dates.fp5.zip

[DigitalDreamer]

Russ thanks for your help. I took what i learned from your attachment and it still does the same thing. Its returns users that are not late. I have to be doing something wrong. Can you possibly take a look at my file and tell me i am doing wrong? It would be greatly appriciated.

[DigitalDreamer]

Russ forget my last post i figured out what i did wrong. I was not paying attention like i should I had restore find requests checked just like you told me not to. Thanks again. My database is finally finished for now until i can find new things to add. Take care.

[DigitalDreamer]

I was wondering if i could take this 1 step further and have it prompt the user how many days ahead they would like it to search and see who will be late in x number of days.

[RussBaker]

If you create a new global number field "g_DaySearch" give the user the option to enter a number in this field, then modify the above script to:

Enter Find Mode []

Insert Calculated result [MemberShipPaymentDateDue,"<"&DateToText(Status(CurrentDate) + Case(IsEmpty(g_DaySearch),0,g_DaySearch))]

Perform Find []

This will work for negative numbers in the g_DaySearch field as well.