Selecting the last record

[dannyb29]

Good morning
I am trying to select the last entry in the detail section and then display it within a group heading. Can anyone help?

 

[lbass]

What makes it the "last" entry in the detail section? Is there a date field? Some other field that is sequential? How are records being sorted--on what field?

-LB

 

[dannyb29]

Hi,
I have a list of sequential numbers detailing stock checks over a date range. I need to capture the last stock check entry and then display that number in the group heading.
This group is sorted by product ID/Decsription.

I hope this makes sense.

 

[lbass]

What field are you grouping on? If you are able to sort on the sequential number field in descending order, you can then just place the check entry in the group header and the most recent one will be displayed.

-LB

 

[dannyb29]

No Items Value Group: 10011 Diet Cola 1000 £1000

1:00 50 £500
1:45 25 £250
2:30 50 £500
4:00 1000 £1000


Here is an example of what I am trying to achieve. I need to capture the last record of each group and display it in the group header.
Cheers!

 

[lbass]

You could either save the report under a different name and then insert it as a subreport into the group header (linked on the groupheader), and with a group selection formula in the sub:

{table.sequentialno} = maximum({table.sequentialno},{table.groupfield})

You said you had a sequential number per record, but didn't show it.

Another method would be to create a SQL expression {%maxno} like this:

(
select max(`sequentialno`)
from table A
where A.`groupfield` = table.`groupfield`
)

...although you might have to build in selection criteria to match your main report if there can be sequential numbers later than those returned by your report date range.

Then you could create a formula like this:

if {table.sequentialno} = {%maxno} then
totext({table.noitems},0,"")+" "+totext({table.currencyamt},0,"")

Place this in the detail section and insert a maximum on it at the group level, and then drag the result into the group header.

-LB