Return most current line of data 1

[YANKRAY]

I am creating a report that will allow the user to enter a date and get a list of all tools requiring calibration on or before the date entered.

I only want the tool to be listed if it requires calibration, but I am getting the item on the list because the database has an entry that meets the search date criteria.

For example, tool #1234 was due calibration on July 10, 2004. There was an entry in the database with July 10, 2004 as the next calibration due date. The tool was calibrated and the next calibration due date was changed to July 10, 2005. Now the tool has two entries in the database.

A tool could have many entries, and I only want the tool listed (with the most current information) if the most recent next calibration due date meets the criteria of being due on or before the selected date.

Is there a solution? Thanks.

 

[hilfy]

Assuming Group By on tool. Put the tool info and the max of the next calibration date in the group footer. Conditionally suppress the group footer if the max of the calibration date for the tool is greater than the data you've entered. The suppression formula looks something like this:

Maximum({calibration date field}, {tool id}) > {?date param}

-Dell

 

[YANKRAY]

I grouped by tool, put the tool info in the group footer and the following

Created the formula:
Maximum ({LS_LIFE_LOG.NEXT_CAL_DATE}, {LS_LIFE_LOG.ITEM_NO})
Put this formula in the Group Footer.

Right-clicked on the Group Footer and selected Section Expert.
In the Section Expert, Selected the Suppress (No Drill Down) box and for the X-2 put the following formula.
Maximum ({LS_LIFE_LOG.NEXT_CAL_DATE},{LS_LIFE_LOG.ITEM_NO}) > {?Date}

I am still getting the same result.

 

[hilfy]

Try unselecting the Suppress box.

 

[lbass]

Instead of suppression, go to report->edit selection formula->GROUP and enter:

{LS_LIFE_LOG.NEXT_CAL_DATE} =
maximum({LS_LIFE_LOG.NEXT_CAL_DATE}, {LS_LIFE_LOG.ITEM_NO}) and
maximum({LS_LIFE_LOG.NEXT_CAL_DATE},{LS_LIFE_LOG.ITEM_NO}) <= {?date}

This will return only the maximum record per group and only if that maximum is earlier than the parameter date.

OR, instead, if you have the capacity to use a SQL expression, then create a SQL expression {%maxdate}:

(select max(AKA.`NEXT_CAL_DATE`) from LS_LIFE AKA where AKA.`ITEM_NO` = LS_LIFE_LOG.`ITEM_NO`)

Then go to edit selection formula->RECORD and enter:

{LS_LIFE_LOG.NEXT_CAL_DATE} = {%maxdate} and
{%maxdate} <= {?date}

This would be the most efficient approach.

-LB

 

[YANKRAY]

What I have done is started over with my report and I am pulling a complete Calibrated Tools list. I was having the same problem with this listing also and I was able to the latest Group selection you sent minus the <={?date} which I will incorporate after I get a good total list.

I wanted to use the SQL expression, but got an error. I will work with this and try and get it to work.

One problem I am having with the Group selection is handling a null value. Some of the calibrated tools have a null value in {LS_LIFE_LOG.NEXT_CAL_DATE} because they are inactive tools.

Is there a way to get the blank entry using the Group selection?

 

[lbass]

If you want to show all groups with null dates as well as those that meet the date criterion, then try the following group selection statement:

isnull({LS_LIFE_LOG.NEXT_CAL_DATE}) or
(
{LS_LIFE_LOG.NEXT_CAL_DATE} =
maximum({LS_LIFE_LOG.NEXT_CAL_DATE}, {LS_LIFE_LOG.ITEM_NO})
and
maximum({LS_LIFE_LOG.NEXT_CAL_DATE},{LS_LIFE_LOG.ITEM_NO}) <= {?date}
)

-LB

 

[YANKRAY]

This worked for the way you said it would, but I got duplicate entries. One with the maximum date and one with the null entry. In my case, the null entry is the maximum date, so if there is a null entry, that should be the only one in the result. Any suggestions?

 

[lbass]

Create a formula {@date}:

if isnull({LS_LIFE_LOG.NEXT_CAL_DATE}) then
date(9999,09,09) else {LS_LIFE_LOG.NEXT_CAL_DATE}

Use this instead of your date field in the group selection formula, as in:

{@date} = maximum({@date}, {LS_LIFE_LOG.ITEM_NO}) and
(
maximum({@date},{LS_LIFE_LOG.ITEM_NO}) <= {?date} or
maximum({@date},{LS_LIFE_LOG.ITEM_NO}) = date(9999,09,09)
)

-LB