Past Due Report

[DigitalDreamer]

I hope i am asking this question in the correct section. If not i apologize. I did some searching on this forum and cannot seem to find what i am looking for. I might not be entrering the correct search terms.

What i have is a membership database that tracks member information. Pretty basic. I do have a field that is called DueDate and we simply use this field to track the date the member has paid there membership dues. I want be able to print a report on what members are past due and then print it out. I tried making the calculation field but no luck. My calc skills are sketchy. Thanks for any help you can give. Thanks and take care.

[jasonwood]

You want to have a script that finds all of the memberships past due and then prints the list.

It would go something like this:

Enter find mode

Set field DueDate, "<" & Status(CurrentDate) ---(Is that right??)

Perform find

Go to layout, list

Print

Go to original layout

Show all records

[DigitalDreamer]

Jason

Thanks for the replying to my post. This is what i get when i run the script. "There are no valid search criteria in this request. Type a valid request before clicking find". Im not sure why it would be saying that. I currently have about 4200 members in the database and that field does contain data.

[jasonwood]

I might have formatted the second line incorrectly...

For troubleshooting, try putting a "Pause" after the second line... now after the script puts the "<" and the date in the field, you can see that it entered the information correctly.

eg: You should have "<10/12/2002" in the DueDate field

[Vaughan]

The problem is with the Set Field step: it can only insert valid data into the field, and "< 1/1/2002" is not a valid date -- the "<" makes it invalid.

To solve the problem you can two ways do. Create an unstored calc field in each record (let's call it PastDue" with the calculation "If (DueDate < Status(CurrentDate), 1, 0)" then simply search the database for records where PastDue = 1.

Alternatively you can use the process you currently have except use the "Insert Calculated Result" script step instead of Set Field. The disadvantage of Insert is that it only works if the field is on the current layout, so you need to change to a layourt that has the dueDate field on it.

Enter find mode

Freeze Window

Go to layout [layout with DueDate on it]

Insert Calculated Result [ DueDate, "<" & Status(CurrentDate)

Perform find

Go to layout [list, refresh window]

Print

Go to original layout

Show all records

[jasonwood]

oohhhhhh!

I should have remembered cause I had this exact problem in the past! Duh!!

Thanks Vaughan!

[DigitalDreamer]

Ok i had a chance to try it. I ended up trying this:

Enter find mode

Freeze Window

Go to layout [layout with DueDate on it]

Insert Calculated Result [ DueDate, "<" & Status(CurrentDate)

Perform find

Go to layout [list, refresh window]

Print

Go to original layout

Show all records

When i run the script i get this error: "The date in this field must be a valid date in the range of years 0001 to 3000 and should look like "12/25/1997" (four digit year)".

It then asks me to revert the field. But all the data in that field is formatted correctly. We have 4000 members in there and we have always typed the date in correctly for example 12/01/2002. So anyway i tell it to revert and then it comes up with "There are no valid criteria in this request. Type a valid request before clicking find."

But i know there are members in there that late but its not catching it. Thanks for the help and take care.

[jasonwood]

Read Vaughan's post above. I forgot that you cannot use "Set field" for a date when you also want symbols (<)... since that's not a valid date.

Use one of the methods Vaughan suggests.

[DigitalDreamer]

I tried the 2nd method he suggested and thats the error im getting above. This is what he had suggested, "Insert Calculated Result [ DueDate, "<" & Status(CurrentDate)".

And i get the error i posted above. Here is exaclty what i have:

Enter Find Mode

Freeze Window

Go to Layout [FOE Membership]

Insert Calculated Result [DueDate,"<" & Status(CurrentDate) ]

[select Entire Contents]

Perfrom Find [Request 1:]

[Restore find requests, Constrain Found Set]

Go to Layout [Late Dues]

[Refresh window]

This exactly what i have. I printed it and posted it for you so you can see what i have.

Thanks for the help

[jasonwood]

Oh sorry about that... I didn't read through the steps...

What is the "restore find requests, constrain found set" all about... I don't think you want that...

[DigitalDreamer]

Im not sure why them are there. It puts them in by default and they cannot be taken out. Thats what it looks like when you print the script.

[jasonwood]

Is the option to "restore find requests" checked in the perform find script step? Uncheck it.

[DigitalDreamer]

Jason that fixed the error. But its listing all the members that have paid ahead and not the ones that are pastdue. I just need it to list the people that have not paid yet. So for example if i run the report today it should list members that have a due date of 12/10/2002 or before. But i tested it and put in a new member with a DueDate of lets say 12/12/2002 and it only printed printer that members and other members that paid ahead. Strange! thanks to your help and the other gentlemen im a little closer.