Trying to select oldest date where... 1

[Sylvor]

Hi,

I have a simple little CR9 report which allows a user to enter customer numbers, the report then produces a list of those accounts and what outstanding invoices are on them.

I have a formula field which should show the oldest outstanding invoice. It does not work, it shows the oldest invoice for the account, outstanding or otherwise!

minimum({SL_TRANSACTIONS.ST_DATE}, {@CustNo})

I need to add a condition which states "where {SL_TRANSACTIONS.ST_UNALLOCATED} <> 0" - but of course this is not allowed. Any ideas how I can get around this?

Thanks in advance!
David

Dave Bennett

 

[beanbrain]

Please try this;

if not isnull({SL_TRANSACTIONS.ST_DATE}) and {SL_TRANSACTIONS.ST_UNALLOCATED} <> 0 then
   minimum((SL_TRANSACTIONS.ST_DATE));

I'm assuming you are putting this formula in a group footer grouped by CustomerNumber?

I think this will work for you.

 

[synapsevampire]

Are you only showing the oldest?

If so, move BB's formula to the group record selection formula as in:

not (isnull({SL_TRANSACTIONS.ST_DATE}))
and
{SL_TRANSACTIONS.ST_UNALLOCATED} <> 0

If you want to identify which is the oldest outstanding of them all, then try a formula containing:

If {SL_TRANSACTIONS.ST_DATE} = minimum({SL_TRANSACTIONS.ST_DATE}, {@CustNo})
and
{SL_TRANSACTIONS.ST_UNALLOCATED} <> 0
then
totext({SL_TRANSACTIONS.ST_DATE})
else
"N/A"

Obviously you need to be grouped by the {@CustNo} (you should post what's in there), and then use the formula at the group header or footer level.

-k

 

[Sylvor]

Hiya Synapse, BB,

Thanks for replying. Yes I am grouping by "Custno" - I am trying to display the oldest outstanding invoice for each customer.

The report allows a user to select multiple custnos, and then displays the date of the oldest outstanding invoice for each custno selected.

If I use the code you have both suggested, I can get the oldest invoice no problem, but I cannot get the oldest outstanding invoice. An invoice is outstanding if it has a value <> 0 in the ST_UNALLOCATED field.

I've been playing around with your suggested code a bit, but cannot seem to get what I need - any suggestions!?

TIA, again!

David

Dave Bennett

 

[lbass]

I think you could use a record selection formula of:

{SL_TRANSACTIONS.ST_UNALLOCATED} <> 0 and
{table.custno} = {?custs}

Assuming you have a group on {table.custno}, go to report->edit selection formula->GROUP and enter:

{table.date} = minimum({table.date},{table.custno})

-LB

 

[Sylvor]

Hiya all,

I ended up using two formulas, the following:

IF {SL_TRANSACTIONS.ST_UNALLOCATED} = 0.0 THEN
 {SL_TRANSACTIONS.ST_COPYCUST}
ELSE
 "0"

And then for the actual field I select the minimum:

MINIMUM({SL_TRANSACTIONS.ST_DATE},{@frmOrderRef})

Thanks for your advice and inspiration!!
David



Dave Bennett