Find Oldest Invoice

[swhaman]

OK, this should be very simple and I am embarrassed that it is giving me such a problem. I am setting up a find function to find customers with invoices 30, 60, 90 and 120+ days past due. I have a form letter set up for each age range which automatically fills in all relevant data (which is working great. The issue I have is in getting the find function to narrow down the invoices in the ranges that I specified above so I can send either a 30, 60, 90 or 120 days collection letter.

I have a field set up (Oldest Invoice) to display the oldest invoice for each customer ........Max ( Invoice Age )

I then set up buttons to perform a find in the Oldest Invoice field. For example, to find customers in the 60 to 89 day range the find criteria specified was: Oldest Invoice: [>60 AND <90]

For some reason, the find search is returning all kinds of records with invoice ages from like 20 to 300 or more. I apologize for being a moron but I have been banging my head against a wall for two days trying to figure this out. Any help would be greatly appreciated!

Thanks!

Steve

[tgilders]

Hello swhaman, Welcome to the forum!

I've created and attached a very basic example to search on an aged invoice. You would then just script your finds to search on the appropriate range. To find the oldest invoice in the range, you would just sort the invoices. For my attached example, just enter find mode and search on ranges in the "InvoiceAge" field such as 30...60

If you're building something more complicated than this (like a portal to view these, or to always show the oldest invoice outstanding via a relationship) then obviously something more sophisticated than this is needed. But perhaps this helps.

Invoices.zip

Edited October 30, 2008 by Guest

[swhaman]

Thanks for your quick reply. I think what I am trying to do may a little more complicated than I described in my initial post. Here is a better description of the project - I apologize in advance if I make this more complicated than it should be:

The ultimate goal is to click a button and print a collection letter for all customers in a certain aged invoice category. I have four buttons set up for each of the 30, 60, 90 and 120+ finds which will run a script to look up and print a "form letter" to each customer including a display of all their overdue invoices. (The content of the letter for each category is different based on age of the delinquency)

I have completed the form letter with all name and address, etc. fields and portal displaying invoices from the related table all works great. It is just the initial find portion that is giving me trouble. This is what I need to do:

- Find all customers with the oldest invoice in one of the categories above. (Some customers have 30, 60, 90 and 120 day old invoices, that is why I created an "oldest invoice" field to perform the search from so even though they have one invoice in each category, they will appear only in the 120 day old list of customers who get the sternest letter - does that make sense?

The problem I am having is that in using both the range method (60...90) or (>60<90) it is finding records from the "oldest invoice" field who's oldest invoice does not match the search criteria specified or the information contained in that field. Hope this makes sense

Thanks!

Steve

[comment]

">60<90" or ">60 AND <90" are not valid search criteria (or better put, they do not search for what you think they are). "60...90" should work, provided the field is defined to have a result of type Number.

[swhaman]

Thanks so much, will give it a try!