IF statement not evaluating first record...

[nikkiwe]

This statement is not evaluating the first record. Any help is appreciated.

Thanks!


Whileprintingrecords;


IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="DAY" AND Next({@Shift})="Evening")Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="DAY") Then "Day"

//Else IF (Next({sheet1$.ID}) = Previous({sheet1$.ID})) AND (Next({@Shift})="Evening" AND Previous ({@Shift})="DAY") Then "Day"


Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Day") Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening") Then "Day"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Night") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Night" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evengin" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} Next({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"
Else IF ({sheet1$.ID} Previous({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"
Else {@Shift}

 

[IanWaterman]

Need to add a condition to tell it what to do with first and last records, as your next will not evaluate on last

Add

If onfirstrecord then .....

Or you can add (not firstrecord) to one of your conditions

Same applies to last

If onlastrecord then ...

Ian

 

[nikkiwe]

Thanks for the information Ian. Worked like a charm on the first record however, the last record is dependent on the previous record....

eg if an employee is coded with two shifts (day and evening) on the same day, then group the evening shift into days.

I am not sure how to tell it to force the last record.

Sorry for all the questions...

Thank you
Nicole

 

[IanWaterman]

Which part of your formual do you think is failing?

Can't work it out from your description.

Ian

 

[nikkiwe]

I know it's not correct but here is the formula. The onlastrecord part is not working. The last record is blank..

Whileprintingrecords;

IF onfirstrecord then {@Shift}
ELSE IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="DAY" AND Next({@Shift})="Evening")Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="DAY") Then "Day"

//Else IF (Next({sheet1$.ID}) = Previous({sheet1$.ID})) AND (Next({@Shift})="Evening" AND Previous ({@Shift})="DAY") Then "Day"


Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Day") Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening") Then "Day"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Night") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Night" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evengin" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} <> Next({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"
Else IF ({sheet1$.ID} <> Previous({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"

ELSE IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening")) Then "Day"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening")) Then "Night"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night")) Then "Night"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening")) Then "Night"
Else {@Shift}

 

[IanWaterman]

YOu do not appear to have any ands after onlastrecord

ELSE IF onlastrecord and
(({sheet1$.ID} = Previous({sheet1$.ID})) AND
({@Shift}="Day" AND Previous ({@Shift})="Evening")) Then "Day"
Else IF onlastrecord and
(({sheet1$.ID} = Previous({sheet1$.ID})) AND
({@Shift}="Night" AND Previous ({@Shift})="Evening")) Then "Night"
Else IF onlastrecord and
(({sheet1$.ID} = Previous({sheet1$.ID})) AND
({@Shift}="Evening" AND
Previous ({@Shift})="Night")) Then "Night"
Else IF onlastrecord and
(({sheet1$.ID} = Previous({sheet1$.ID})) AND
({@Shift}="Evening" AND
Previous ({@Shift})="Evening")) Then "Night"
Else {@Shift}

 

[nikkiwe]

no luck...

 

[IanWaterman]

Create some formula for

Previous({sheet1$.ID}) and Previous(@shift)

Place these on report and make sure they are evaluating as you would expect.


What does @shift do?

Ian

 

[nikkiwe]

@shift interprets a time reporting code. The code determins which shift it is day, evening or night. That part is working and so is the above formual with the exception of the last records....grrr

 

[IanWaterman]

But are they working as expected on last record. Thats why I have suggested you create two formula to display previous.

It would appear they are returning nulls and the main formula is failing on that last part.

Ian

 

[nikkiwe]

nope, the @shift is returning a value...

 

[IanWaterman]

Create a formula usinjust the last part and see what happens

IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening")) Then "Day"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening")) Then "Night"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night")) Then "Night"
Else IF onlastrecord (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening")) Then "Night"
Else {@Shift}

 

[nikkiwe]

Sorry it has taken me so long to get back to you.

I tried as you suggested but the forumual error during the onlastrecord with "too many arguments". I have tried changing the number of brackets... no luck....

Thanks again

 

[CR85user]

This may read a little better:

IF onlastrecord then
if (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening"))
Then "Day" Else
if
(({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening"))
or
(({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night"))
or
(({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening"))
Then "Night"
Else {@Shift}

'J

CR8.5 / CRXI - Discovering the impossible

 

[nikkiwe]

Got it! It was the placement of the onlastrecord statement. It needed/wanted to be placed at the beginning of the Formula - after the onfirstrecord.

Here is the complete functioning forumal:

hileprintingrecords;

EvaluateAfter ({@Shift});

If onfirstrecord then ({@Shift})


Else IF onlastrecord AND (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening")) Then "Day"
Else IF onlastrecord AND (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening")) Then "Night"
Else IF onlastrecord AND (({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night")) Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="DAY" AND Next({@Shift})="Evening")Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="DAY") Then "Day"

//Else IF (Next({sheet1$.ID}) = Previous({sheet1$.ID})) AND (Next({@Shift})="Evening" AND Previous ({@Shift})="DAY") Then "Day"


Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Day") Then "Day"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Day" AND Previous ({@Shift})="Evening") Then "Day"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evening" AND Next({@Shift})="Night") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Night" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Night" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Night") Then "Night"

Else IF ({sheet1$.ID} = Next({sheet1$.ID})) AND ({@Shift}="Evengin" AND Next({@Shift})="Evening") Then "Night"
Else IF ({sheet1$.ID} = Previous({sheet1$.ID})) AND ({@Shift}="Evening" AND Previous ({@Shift})="Evening") Then "Night"

Else IF ({sheet1$.ID} <> Next({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"
Else IF ({sheet1$.ID} <> Previous({sheet1$.ID})) AND ({@Shift}="Evening") Then "Night"



Else {@Shift}