ClearCollect multiple people columns

(0) Share

Report

Posted on by BHaapi

134

I have an approval app that i want to be able to run a clearcollect to filter a gallery.

There are 4 approval levels in this, and i would like to do something like this:

i only want them to see items they are mentioned in. is there a way for a clearcollect to look through ALL the columns listed below and only grab the things that the current User().Email is associated with?

I was able to build one where it grabbed by creator already, but now i need to seek from many columns and im LOST.

I have a gallery like this already, but i think it would need to be a collection. im trying to avoid direct permissions or passwords

If(

varStatus = 1,

ApprovalCollection,

varStatus = 2,

Filter(

ApprovalCollection,

User().Email in 'First Approver'.Email ||

User().Email in 'Second Approver'.Email ||

User().Email in 'Specialty Approver'.Email ||

User().Email in 'Final Approver'.Email

varStatus = 3,

ColApproved,

varStatus = 4,

colDeclined

)

this one will work for the 4 level:

ClearCollect(

ApprovalCollection,

Filter(

'Purchase Approvals',

"Pending" in Status.Value

)

Thank you in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

Hi @BHaapi

You have three potential data sources there ApprovalCollection, colApproved, and colDeclined. You cannot use more than one for the potential outcomes of a filter. What are the "rules" for the four outcomes of varStatus, what is the common data source you can filter them all from and what are the "rules" for filtering on each.

Was this reply helpful? Yes No
BHaapi 134 on at

Like (0)

Report

@WarrenBelz

they are all coming from one SP source: 'Purchase Approvals'. we know that the list will get bigger than 500 so to avoid the stress on the system i split it into several smaller collections based on a Status.Value column: Status approved is colApproved, status decline is colDeclined and everything else is within the variety of "pending" (pending first approval, second approval, special approval, final approval) and that is the one i want to actually split to only grab things that they are mentioned in the aforementioned people columns. right now it grabs all pending but i want it to only grab status pending AND that the User().Email is mentioned in one or more of these columns:

User().Email in 'First Approver'.Email ||
  User().Email in 'Second Approver'.Email ||
  User().Email in 'Specialty Approver'.Email ||
  User().Email in 'Final Approver'.Email

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@BHaapi ,

OK - disregard Delegation for the moment - we can probably create a Delegable filter. If you were constructing directly on the List 'Purchase Approvals', what filters do you need to the varStatus options (1,2,3,4)

Was this reply helpful? Yes No
BHaapi 134 on at

Like (0)

Report
Hi @WarrenBelz,
I need a filter for
All submissions (varStatus 1),
My Pending submissions (this is a variety of "pending first approval/second approval/specialty approval/final approval" which I currently use the "Pending" in Status.Value equation) (2),
Approved submissions (3)
Declined submissions (4)

Was this reply helpful? Yes No
Verified answer

BHaapi 134 on at

Like (0)

Report

I was able to get the clear collect to work! i used the below and it seems to be working.

ClearCollect(
ApprovalCollection,
Filter(
PA2,
User().Email in 'Authorization L1'.Email && "Pending" in Status.Value || User().Email in 'Authorization L2'.Email && "Pending" in Status.Value || User().Email in 'Specialty Authorization'.Email && "Pending" in Status.Value || User().Email in 'Final Authorization'.Email && "Pending" in Status.Value
)
);

Now, however, im having issues with the varStatus 2 showing just things they are currently assigned to do. they could be mentioned in any of the 4 above levels, and im trying to filter this new clearcollect to only show if they are mentioned and they HAVE NOT submitted a response to that level.

i have the filter as such:
SortByColumns(
If(
varStatus = 1,
ApprovalCollection,
varStatus = 2,
Filter(
ApprovalCollection,
User().Email in 'Authorization L1'.Email && IsBlank('L1 Details') Or User().Email in 'Authorization L2'.Email && IsBlank('L2 Details') Or User().Email in 'Specialty Authorization'.Email && IsBlank('Spec Details') Or User().Email in 'Final Authorization'.Email && IsBlank('Final Details')
),
varStatus = 3,
ColApproved,
varStatus = 4,
colDeclined
),
"Urgent",
SortOrder.Descending
)

but its only showing items that qualify for the first statement (i highlighted the statement). I made sure there are items in the list that would work for the other statements, but they arent filtering in. I tried both Or and || and neither seem to work.

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
@BHaapi ,

You still need to filter everything from a single data source - I will also take a guess here that none of your data source fields are multi-select, so you do not need the in filter - does this suit your needs ?

SortByColumns( Filter( 'Purchase Approvals', ( varStatus = 1 || (varStatus = 2 && Status.Value = "Pending") || (varStatus = 3 && Status.Value = "Approved") || (varStatus = 4 && Status.Value = "Declined") ) && ('Authorization L1'.Email = User().Email && IsBlank('L1 Details')) || ('Authorization L2'.Email = User().Email && IsBlank('L2 Details')) || ('Specialty Authorization'.Email = User().Email && IsBlank('Spec Details')) || ('Final Authorization'.Email = User().Email && IsBlank('Final Details')) ), "Urgent", SortOrder.Descending )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
BHaapi 134 on at

Like (0)

Report

@WarrenBelz
Status.Value is a multiple choice column. I did try this and it didnt seem to work. i also swapped around the top half for the "Pending" in Status.Value and that didnt work either unfortunately.
i get this, saying that it doesnt seem to find the people columns anymore

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

@BHaapi ,

How can Status be multiple choice when you have different options for each ? However you need to address the person column issue, which is not making a lot of sense if they are in your data source.

Was this reply helpful? Yes No
BHaapi 134 on at

Like (0)

Report

There are 3 with the choice of pending.
I'm not sure why it didn't pick up those people columns when i put it in, it picked them up in the other equation. I think i resolved the filters within the app for now though, thank you so much for your assistance @WarrenBelz !

Was this reply helpful? Yes No