Only print first record of formula field 2

[PhilB2]

I would like help to only print the first record that meets the criteria specified in the formula in a formula field. I haven't been able to figure out how to use Count or OnFirstRecord, assuming one or both represent the easiest way to get this done.

Alternatively, is there a formula way of determining the most recent date returned in a date field, in order to select only one record?

 

[lbass]

It would help to see the content of the formula and to know how your data is sorted. If you are able to sort based on the formula so that the first record (I assume you mean within some group) is the one that meets the criteria, then you could simply move the fields to the group header and suppress the detail section.

If by first you are referring to the most recent date, you can go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.group})

-LB

 

[PhilB2]

As of yet, there is no group in the report.

The record selection criteria includes another field besides the date, so I believe I need to keep my field in in the Detail section. More than likely, it is coincidence that the first record that appears in the report is also the one associated with the most recent date. Choosing the most recent date is the better solution.

I'd like to be able to use the maximum formula you suggested, but I don't understand what "{table.group}" refers to.

The formula is below. As of now, the user can enter a date parameter if there are multiple records among which to choose. My objective is to allow the user to ignore the date parameter (defaults to 1/1/2001) if his need is to find the most recent record--so if I succeed, I'd add the condition that unless the default value was changed, get the record with the most recent date.

Detail field Formula:
--------------------

if {VTMTRACK.ISSUEID} = {?Issue_ID}
and ({VSTATEHISTORY.TRANSITION_USERID} = "Phil Bustin"
or {VSTATEHISTORY.TRANSITION_USERID} = "Todd Barbera")
and {?TT_task_To_group} = {VTMTRACK.TO_REGION}
and {VTMTRACK.ITEMTYPE} = "Task"
and (Date({VSTATEHISTORY.TRANSITION_DATE}) = {?TT_task_date}
or {?TT_task_date} = DateValue(2001,01,01))
and ({VTMTRACK.TITLE} = {?Cub_Title}
or ({?Cub_Title} = "Same as BADGIR"
and {VTMTRACK.TITLE} = {UBR_BADGIR.TS_TITLE}))
then
{UBR_BADGIR.TS_TI_PROMOTE}

 

[Charliy]

It would be fairly easy to do with sahred variables, but we need a bit more information on your report. Is this the first one that meets the condition in a group? The first on in the report?

Basically you would initalize a variable in a formula in the report header.

shared stringvar firstone := ' '

Then in the detail section you'd have an evaluation formula

shared stringvar firstone;
if firstone = ' ' and <data meets criteria>
then firstone := {table.field}
else firstone := firstone

And where you want to display it you need a formula:

shared stringvar firstone

The tricky part comes with resetting it if you're doing it by group.

 

[PhilB2]

There is no group in the original report. I'm trying one out now. I didn't know about shared variables; I had tried ordinary ones.

I tried a group based on the date field, and successfully set it to sort descending; however, the report would occasionally have to go through more than just the first record (the one with the highest date value), in order to meet the second criterion (promotion group, as it happens).

The approach I like is the one that selects the maximum date, as in the suggestion by lbass. I'm waiting for a response on whether and how I can use it in the Detail section, or whether and how the report can go through several records to find the right one in a Group section (seems hard to do).

Hope that makes sense.

 

[PhilB2]

Actually, I realize now that the report needs the highest date value among the records that meet other criteria, not simply the maximum date among all the records.

However, if there a maximum date formula would work in concert with other criteria--that is to say, if a maximum date formula would choose the highest date among those in selected records, then it might work.

lbass: Bearing this in mind, does your formula suggestion still hold?

 

[lbass]

Create a formula like this (call it {@yourformula} for this explanation):

if {VTMTRACK.ISSUEID} = {?Issue_ID} and
(
{VSTATEHISTORY.TRANSITION_USERID} = "Phil Bustin" or
{VSTATEHISTORY.TRANSITION_USERID} = "Todd Barbera"
) and
{?TT_task_To_group} = {VTMTRACK.TO_REGION} and
{VTMTRACK.ITEMTYPE} = "Task" and
(
{VTMTRACK.TITLE} = {?Cub_Title} or
(
{?Cub_Title} = "Same as BADGIR" and
{VTMTRACK.TITLE} = {UBR_BADGIR.TS_TITLE}
)
) then
Date({VSTATEHISTORY.TRANSITION_DATE})

Then go to report->selection formula->GROUP and enter:

{@yourformula} = maximum({@yourformula})

This should return one record to your report--representing the most recent date where the other criteria are met.

-LB

 

[PhilB2]

Ausgezeichnet! (Remember the VW ads?) At any rate, in the unlikely event you didn't know, that's the German word for Outstanding (or some synonym).

Translation: Your solution worked.