I would suggest taking it out of the report and make a formula checkbox that you can have a lot more creativity with. In this case you'd do a formula checkbox that would do something like the following:
Status = "Open" and
(
if( User() = [Approver 1] and [Approval 1]=""), true, false) or
if( User() = [Approver 2] and [Approval 2]=""), true, false) or
if( User() = [Approver 3] and [Approval 3]=""), true, false)
)
Something like that. The report then is just filtered where this checkbox is true. Its much easier to do the filter as a field where you have the full range of formula logic at your disposal
Thank you!
I like the idea of the formula checkbox. Is there a way to incorporate the UserRoles function into the formula? I only just learned that UserRoles exists, and if I could get the formula to check the user's role directly, that would eliminate the need to maintain a separate table of approvers/approval roles. I've only been able to find one sample formula that includes UserRoles, and my attempts to reapply that example have been a dismal failure so far.
Sure, the UserRoles will only return the role(s) of the person logged in though, so you'll want to test it by testing as other users as well.
The UserRoles though works like:
UserRoles("Name") or UserRoles("ID") - if you wanted to check if someone was lets say a VP - you can either do:
Contains(ToText(UserRoles("Name")),"VP") or if you want to be more aligned with the system ID of the role you could do Contains(ToText(UserRoles("ID")),"15") where 15 in this example was the ID of the VP role.
