Show Gallery Items based on Filter from another Source

[jbaldwin]

Hi,

I have Table 1 (SQL) with a list of Items, say A, B, C, D and E. Then Table 2 with users associated with each of these items, e.g:

Item | User

A | Bob

C | Bob

Now in PowerApps I have a gallery showing Items A & C for user Bob, I want to be able to bring up a new gallery of Table 1 that shows the Items that Bob isn't a part of: B, D and E. It would be greatly appreciated if you could help me figure out a way to do this.

[mdevaney]

@jbaldwin 

Suppose your SQL table looks like this

Item | User
A | Bob
B | Billy
C | Bob
D | Joe
E | Joe

And your gallery showing only Bob looks like this.

Item | User
A | Bob
C | Bob

Put this code in the Items property of the other gallery

Filter(your_sql_table, Item <> "Bob")

The result will be this

Item | User
B | Billy
D | Joe
E | Joe

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[jbaldwin]

@mdevaney, Yes one of my galleries looks like:

Item | User
A | Bob
C | Bob

However I want a second gallery to look like:

Item
B
D
E 

Based on the fact that Bob is not associated with these items yet, then I will be able to select one (or more) of these items and add it to Bob's list of Items.

[v-yutliu-msft · Accepted solution]

Hi @jbaldwin ,

Could you show me the formula in your gallery1's Items?

Since gallery2 filters based on gallery1, so I need to know how do you set gallery1.

I assume that:

gallery1 filter based on user to only display "Bob".

gallery2 filter the items that are not in gallery1.

If so, try this:

gallery1's Items

Filter(table2,User="Bob")

gallery2's Items:

Filter(table1,Not(
                  Item in Filter(table2,User="Bob").Item
                  )
      )

Best regards,