Having Trouble Returning Certain Rows

[Garyjr123]

I have a report where I want to return all the rows with audittype = 2 and only return the rows with an audittype = 1 if reschedreason_id <> 1-23. See my record selection below:

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{appt.scheduled_datetime} = {?StartDate} and
{schedlog.audittype_id} in [2, 1] and
{schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9]

This record selection pulls in all the audittype_id 1 with a reschedreason_id <> 1-23 but none of the audittype_id of 2. How can I fix this?

Thanks,

Gary

 

[DJWheezyWeez]

Sorry, I'm confused. You said you want to show audittype 1 and 2 but only show 1 if reschedreason is not in 1-23 but your formula doesn't say "not". I would expect to see:

not({schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9])

Do you want to see 1-23 for audittype 1 or not?


Either way, I think something like this might work:

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{appt.scheduled_datetime} = {?StartDate} and
({schedlog.audittype_id} = 2
or
(if {schedlog.audittype_id} = 1 and not({schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9])))

This record selection formula is for reason ids NOT in 1-23 since I'm not sure if you meant to include them or not but try this. I don't have Crystal in front of me to test this but I'm sure someone can fix my formula if it's not working.

-DJWW

 

[Garyjr123]

DJ,

Sorry for the confusion, I WANT to see all the records for audittype = 1 if reschedreason_id <> 1-23. If audittype = 1 and does not have a reschedreason_id <> 1-23 then I do not want the record returned. Is that more clear?

Gary

 

[Garyjr123]

Sorry I see my error in typing this out (and repeated it). I want to return all rows with audittype_id = 1 and has reschedreason_id between and equal to 1-23.

 

[DJWheezyWeez]

Try this instead then:

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{appt.scheduled_datetime} = {?StartDate} and
({schedlog.audittype_id} = 2
or
(if {schedlog.audittype_id} = 1 and {schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9]))

Again, not sure if it'll work since I don't have Crystal in front of me. Good Luck!

-DJWW

 

[Garyjr123]

When I check for errors it is asking me for the 'then' part of a if, then, else statement. Is there more to that formula? How about this, I want to return all the audittype_id = 1 if reschedreason_id is not null?

 

[Turkbear]

Hi, This part is incomplete

({schedlog.audittype_id} = 2
     or
(if {schedlog.audittype_id} = 1 and {schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9]))

Try

({schedlog.audittype_id} = 2
     or
(if {schedlog.audittype_id} = 1 and {schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9])
Then
TRUE
Else
False
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Or use:

(
{schedlog.audittype_id} = 2 or
(
{schedlog.audittype_id} = 1 and
{schedlog.reschedreason_id} in 1 to 23
)
)

-LB

 

[Garyjr123]

Hello Everybody,

I wanted to add another element to these formulas you are providing.

{res2.restype_id} = 2 and
{res2.resunit_id} in [5, 6] and
{appt.scheduled_datetime} = {?StartDate} and
({schedlog.audittype_id} = 2 or(if {schedlog.audittype_id} = 1 and {schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9]Then TRUE Else False))

This worked well but now I want one more criteria to be filled to pull back data. I want schedlog.audittype_id} = 1 to return data only if the appointment date has changed (not the just the time) for the appointment. Since it is a date/time field (appt.start_datetime) we are putting the criteria on, I DO NOT want to see rows returned if the date did not change but the time did.