Only display the earliest date not using groups

[djieon]

Hi,

I have a table that has records with dates in it, example:

Item 1 - 2012/05/01
Item 1 - 2018/05/01
Item 1 - 2022/05/01
Item 2 - 2012/05/01
Item 2 - 2018/05/01
Item 2 - 2022/05/01

I only want each item with the next available date from today, so in the example above it would be item 1 would show the record with 2018/05/01 and item 2 would show 2018/05/01

Ideally I would like this to happen in the details area, without using groups if possible, but if not would use groups at a push...

any ideas?

Thanks

David.

 

[lbass]

What kind of database are you using? You could set up a SQL expression that created a faux group on item ID and which looks for the minimum date > than today per Item ID. Then set the Item ID in the main report equal to the SQL expression.

-LB

 

[IanWaterman]

I would use a command assuming using ANSI syntax it would be something like

select itemnumberfield, Min(availabledatefield) AvailableDate
from yourtable
where cast(availabledatefield as date) >= cast(getdate() as date)
group by itemnumberfield

You can replace cast(getdate() as date) with a Command parameter should you choose to

Ian

 

[Charliy]

Sort by Item then date.

Create a formula:
// @supform
if onlastrecord then 0
else
if {item} <> next({item) then 0
else 1

make the suppression formula for your detail: {@supform} = 1

 

[pmax9999]

The Command or SQL Expression options would be the most efficient. The simplest alternative (without using groups) would be to include a line in the record selection to include only those records where the {RelevantDate} > CurrentDate, sort records by the {RelevantDate} field, and use a Running Total to number every record, reset on change of Item#.

Then use conditional suppression at the detail level to suppress all those where the Running Total Value > 1.



Cheers
Pete