Formula to populate a warning if a pay ID contains Sal and Reg in a period

Lani Vriezema
Lani Vriezema used Ask the Experts™
on
I need a formula to populate a warning if a pay ID contains Sal and Reg in a pay period in crystal report.

The data is grouped by employees and then by pay period

If there are two ID's = to 'Reg' and 'SAL' in a pay period
then
"warning"
Else
" "
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Are these in separate records?

Can you have more than 1 regular or salary record in a pay period?
If not then you can just test for the record count.  If it is > 1 then error

Try this

Create 2 formulas in the report (don't put them on the report.)
Name - IsSalary
If  {YourIDField} = 'Sal' then
    1
Else
    0

Open in new window


Name - IsRegular
If  {YourIDField} = 'Reg' then
    1
Else
    0

Open in new window


In the PayPeriod group Header add a formula

Name - HasBoth
If  Sum({@IsSalary},{PayPeriodField}) >=1 AND Sum({@IsREgular}, {PayPeriodField}) >= 1 then
    'Warning'
Else
    ''

Open in new window


mlmcc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial