Report to display records awaiting action by role

Question

I'm sure there's a way to achieve what I'm trying to do here, but me and my basic Quickbase skills are at a loss.&nbsp;
My app includes:

Request records, which need to be approved by multiple users in various roles. There is a separate field for each approval type, the editing of which is restricted to users who have been assigned the approver role for that specific approval.
Approver records - basically a table of all the approvers and which type of approval they can give. There may be more than one person who can give each type of approval. Currently each approver can only give one type of approval, although that may change (and in the past I've needed one person to be able to give more than one approval type).&nbsp;

I'm trying to create a report that will only display records in an 'open' status that do not yet have an approval of the type that the current user is set up for. So for each record in the table, I want to filter to only show records that are open AND where the approval field for the approval type that the current user is set up for is blank.&nbsp;
And I have no idea how to do this. I've got partway through various attempts at setting up reports, filters, 'join' tables, and the like, and am no closer to making it work. Any suggestions much appreciated, and explaining to me like I'm five even more appreciated. Thank you!
------------------------------Michelle L------------------------------

chayceduncan · Answer

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:&nbsp;

Status = "Open" and&nbsp;
(&nbsp;
&nbsp; &nbsp; &nbsp;if( User() = [Approver 1] and [Approval 1]=""), true, false) or&nbsp;
&nbsp; &nbsp; &nbsp;if( User() = [Approver 2] and [Approval 2]=""), true, false) or&nbsp;
&nbsp; &nbsp; &nbsp;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------------------------------Chayce Duncan------------------------------

michellel · Answer

Thank you!&nbsp;
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.------------------------------Michelle L------------------------------

chayceduncan · Answer

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.&nbsp;
The UserRoles though works like:&nbsp;
UserRoles("Name") or UserRoles("ID") - if you wanted to check if someone was lets say a VP - you can either do:&nbsp;

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.&nbsp;------------------------------Chayce Duncan------------------------------

Forum Discussion

Report to display records awaiting action by role

3 Replies