Filtering the correct data from a field

[cunner26]

Hello,

I am trying to create a formula that will show me the correct award value. For instance, everyone has an award entitled 02A1, but some employees receive an award half way through the year and it is coded 02M1. What I am looking for is for a formula to show me that if an employee has a code of 02M1 then show that value over the 02A1 value.

I have tried the following

if {eeAwards.idAward}="02M1" then {eeAwards.AwardValue} else {eeAwards.AwardValue}

I have placed this formula in its own grouping but it still takes the 02A1 value if present.

Thanks for your help,

 

[synapsevampire]

What I believe you want is to show ONLY the 02M1's if they exist, otherwise show the 02A1, correct?

Create a counting running total which counts for (evaluate use a formula):

I'll call it O2M1s
{eeAwards.idAward}="02M1"

And reset it at the employee level

(this means that you have a group by employee)

Now in the group footer for the employee, place a formula like:

If {#02M1s} > 0 then
"02M1"
else
"02A1"

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com