FIND FIRST RECORD WHERE CONDITION IS MET 1

[MochaLatte]

I have a report that is grouping records by USERS. I need to display the following info on each user

SELECT ONLY RECORDS WITH ALPHA OR GAMMA TEST TYPE ONLY
AFTER SELECTING ALPHA OR GAMMA DISPLAY THE FIRST TEST TAKEN DATE AND TIME and TEST TYPE

USER TEST TAKEN DATE TEST TYPE
ROSIE APPLE 8/20/2011 8:00 BETA
ROSIE APPLE 8/21/2011 10:00 ALPHA
ROSIE APPLE 8/22/2011 11:00 BETA
ROSIE APPLE 8/23/2011 14:00 GAMMA
ROSIE APPLE 8/24/2011 16:00 ALPHA

ON THE FOOTER, I WOULD LIKE TO DISPLAY:
ROSIE APPLE 8/21/2011 10:00 ALPHA

ANY HELP IS GREATLY APPRECIATED!!!!

 

[briangriffin]

Do you need to see the tests that are not Alpha or Gamma? I'll assume you don't, so in the Select Expert put

{TestType} in ["alpha", "gamma"]

Sort your report by test date descending and put this formula in the details section:

whileprintingrecords;
stringvar v_test;
datetimevar v_testtime;

if ({YourTable.User} = previous({YourTable.User}) or onfirstrecord)
then
(v_test := {YourTable.Test};
v_testtime := {YourTable.Date});

The display formula goes in the report footer:

whileprintingrecords;
stringvar v_test;
datetimevar v_testtime;

GroupName ({YourTable.User}) + v_test + "      " + totext(v_testtime,"M/d/yyyy HH:mm")

Then suppress either the formula in the detail section, or the entire detail section.

 

[lbass]

As Brian suggested, use a record selection formula like this:

{TestType} in ["alpha", "gamma"]

Be sure to match the case (upper/lower) to what is displayed in the database.

Then group by user and sort by date descending. Place the desired fields in the group footer and suppress the header and detail section.

-LB

 

[MochaLatte]

I should have mentioned that I cannot use the Record Selector because there are users who do not meet any of the two conditions and they need to show up in the report.
Any suggestions?

 

[briangriffin]

Add the condition to the formula:

whileprintingrecords;
stringvar v_test;
datetimevar v_testtime;

if ({YourTable.User} = previous({YourTable.User}) or onfirstrecord)
and {YourTable.Test} in ["alpha","gamma"]
then
(v_test := {YourTable.Test};
v_testtime := {YourTable.Date});

 

[MochaLatte]

It seems to be partially working, after your formula, it returns:
ROSIE APPLE 8/23/2011 14:00 GAMMA
However, I need it to return the first record according to the time:
ROSIE APPLE 8/21/2011 10:00 ALPHA
I appreciate all the help you are giving me!!!

 

[briangriffin]

Just to be sure, your records are sorted descending by date/time? If not, it would explain the behavior you're seeing.

When you click the Sort Expert, it should look like this:

Group #1 - YourTable.User - A
D - YourTable.TestDate

 

[MochaLatte]

The sort Expert looks exactly like you are describing it.
Group #1 - YourTable.User - A
D - YourTable.TestDate

 

[briangriffin]

First, this formula wouldn't work if a user had only one test so please change it to this - we'll also eliminate the case sensitivity that LB alluded to.

whileprintingrecords;
stringvar v_test;
datetimevar v_testtime;

if ({Sheet1_.User} = previous({Sheet1_.User}) or onfirstrecord or Count ({Sheet1_.Date}, {Sheet1_.User}) = 1)
and upper({Sheet1_.Test} in ["ALPHA","GAMMA"])
then
(v_test := {Sheet1_.Test};
v_testtime := {Sheet1_.Date});

Next, please unsuppress the formula in the details section - you should have a datetime visible for only the alpha and gamma tests, unless that test is the first record for the user (the most recent test). If that is not the case then there is a problem with the logic and we can go from there.

 

[MochaLatte]

Still not working. This is my criteria
Whileprintingrecords;
stringvar mo_name;

YOUR FORMULA HERE

and
({table.NAME} like '*ALPHA G*'
and ({table.STRENGTH} like'*5 million*'
or {table.STRENGTH} like '*2.5 million*'))
OR
{tablemo.NAME} like'*GAMMA*'
OR
{table.NAME} like'*DELTA*'
OR
{table.NAME} like'*EPSILON*'
then mo_name:=({table.NAME});
mo_name

I have to use like because there are many strings like 'ALPHA G' and the user does not want to provide every single name on each test.

 

[briangriffin]

This appears to be selection criteria - is it? It shouldn't have anything to do with the formulas I provided.

({table.NAME} like '*ALPHA G*'
and ({table.STRENGTH} like'*5 million*'
or {table.STRENGTH} like '*2.5 million*'))
OR
{tablemo.NAME} like'*GAMMA*'
OR
{table.NAME} like'*DELTA*'
OR
{table.NAME} like'*EPSILON*'


I"m not sure what the mo_name variable is doing for you. Try using my formulas without any modifications - they work for me as expected with your sample data. It seems like you're combining apples with oranges with peaches.

 

[MochaLatte]

Brian G, I implemented your formula exactly as you suggested and it worked only when I sorted "Test Taken Date" on "Descending" order. It does not make any sense to me, why only works on the date field sorting descending order.
Anyhow, it is working and I do appreciate your help!!!
Many thanks!!!!!!
MochaLatte

 

[briangriffin]

I was hoping you had gotten this to work. They have to be sorted descending because you are looking at each record in order and asking "Is this an alpha or gamma test? If so, this is the date I want." You want the last record where that is true to be the earliest date for the user.

If they weren't sorted this way you'd have to ask "Is this an alpha or gamma test, and is the date earlier than the last alpha/gamma test date?" That is certainly doable, but makes the formula more complex. It's easier to just force the sort order.