Adding Columns

(0) Share

Report

Posted on by MSlater

Hi,

I am trying to find a way to what would have been a join in SQL between two SharePoint lists.

I have a users list that has a column to say if a user is archived or not and a list that has staff performance reviews.

I would like to be able to filter a summary gallery that I have made of the progress of these performance reviews but I do not want it to include any staff that have been archived since they started their performance review.

At the moment I have tried the following:

ClearCollect(
col_TeachingReviews,
AddColumns(
Filter(
LS_SR_Teaching,
ReviewCycle = v_CurrentCycleT.ReviewCycle
),
"IsArchived",
LookUp(
LS_PA_Users,
Archived="N" And
Bromcom_Staff_ID in LS_SR_Teaching[@RevieweeID],
Archived
)
)
);

However it does not want to work and seems to just add a column called IsArchived with N as the cell value for everyone even the staff that are archived in the LS_PA_Users list.

I am obviously not doing this correctly. Has anyone got any advice or a different way to do this.

Thanks

Mike

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

Hassan_SZ_365 542 on at

Like (0)

Report
Copy link

Link copied!
Hi,
To filter out archived users from your performance review summary, you need to perform operations similar to an SQL join and then filter based on the "Archived" status. Here’s a more human-centric explanation of how you might approach this in PowerApps:
Retrieve the current performance review cycle data.
Loop through each review, checking the archived status of the user from the users list.
Only collect the reviews where the user is not archived.
The code you provided seems to attempt this, but it might be failing due to the filter condition or the logic that adds the "IsArchived" column.
Here’s a simplified step-by-step guide:
Step 1: Get the relevant reviews for the current cycle.
varReviewsCurrentCycle = Filter(LS_SR_Teaching, ReviewCycle = v_CurrentCycleT.ReviewCycle);
Step 2: Collect the non-archived users.
varActiveUsers = Filter(LS_PA_Users, Archived = "N");
Step 3: Loop through the reviews and check against the active users, collecting the necessary data.
ClearCollect( col_TeachingReviews, ForAll( varReviewsCurrentCycle as Review, If( LookUp( varActiveUsers, Bromcom_Staff_ID = Review.RevieweeID ), Review // This adds the current review to the collection if the user is not archived. ) ) );
Step 4: Remove empty values from the collection since ForAll may include them for reviews that don't meet the criteria.
RemoveIf(col_TeachingReviews, IsBlank(RevieweeID));
This pseudo-code is meant to guide you on the logic flow. You will need to adjust the field names and conditions according to your specific SharePoint list columns and app variables. Always make sure to test each step to confirm that the data is being filtered and collected as expected.

Best Regards,
Hassan Raza

Was this reply helpful? Yes No
MSlater 15 on at

Like (0)

Report
Copy link

Link copied!

Thank you for this I will give this a try.

Was this reply helpful? Yes No