Join two Sharepoint's lists, retrieve all the products of a user and display the categories with Distinct function

[OmarTheStudent]

Hi all,

I have 2 lists on my SharePoint and I need to retrieve all the products of a single user (depends on the email inserted in the login) and display the categories of those products in a gallery items using Distinct. 

The idea should be (SQL speaking) to DISTINCT SELECT AllProducts.ProductCategory FROM AllProducts,PersonalProducts WHERE AllProducts.ProductID = PersonalProduct.ProductID AND ProductOwner = Username

I tried ClearCollect and AddColumns but the collection let the gallery displays also the empty records.

Thanks for helping,

OmarSaid

[eka24]

To be more precise, can you display sample tables and how you want the output to be?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[OmarTheStudent]

Im sorry,

The output should be a table to be inserted into the Gallery Items containing all the ProductCategories of the products that an User has in PersonalProducts (I hided the emails because they real)

[eka24 · Accepted solution]

Please follow these steps:

1. Onvisible of the Screen

ClearCollect(MyProductsNew,ProductsNew);

ClearCollect(MyOwners,Filter(PersonalProducts,ProductOwner=User().Email))

2. On the Items of the Gallery:

Filter(MyProductsNew ,ProductID in MyOwners.ProductID)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[OmarTheStudent]

Life Saver. Thank you so much.