Displaying Distinct Values from a Sharepoint List in a PowerApp

[User2020]

Hello, I'm a relative beginner with PowerApps and am looking to display a distinct list of values from a Sharepoint List on a screen of an App

My data set looks something like this:

Firm	Product	Order No
Firm A	Widget	6776
Firm A	Software	8735
Firm A	Hardware	5000
Firm B	Widget	9041
Firm B	Software	7110
Firm B	Hardware	4632
Firm C	Widget	5376
Firm C	Software	3389
Firm C	Hardware	6841
Firm D	Widget	8045
Firm D	Software	6841
Firm D	Hardware	2282

Ideally, I would like to make a screen in the app that only displays the distinct Firm values, and if possible, would like it to be able to update so that I do not have to manually add/subtract firms that are new or exiting the data set. So ideally a screen would look something like this

Firm A     >

Firm B     >

Firm C     >

Firm D     >

Then clicking on each firm or arrow would bring you to a more detailed screen that would include the product and order number. Can anyone help with that?

[Drrickryp]

Hi @User2020 

The Distinct() function creates a single column table with the name Result when applied to a datasource. You would put a Gallery on your screen with the the Items property Distinct(SharePointListName , Firm).  When you entered a label in the gallery its Text property would be ThisItem.Result. When you add a new item to your SharePoint list that has a new firm, the gallery would autopopulate with the new firm.  You would not need to do anything further to include it in the gallery.  If you wanted to sort the list you would put Sort(Distinct(SharePointListName , Firm),Result) as the Items property of the gallery.

[eka24 · Accepted solution]

To display the data from your table, Insert 2 Galleries.

In Gallery1 Item property, put:
     Distinct(ListA,Firm)

To show the details after you click on Gallery1, Put the the following formula on
   Gallery2 Items:
   Filter(ListA,Firm=Gallery1.Selected.Result)

Change ListA to your actual datasource

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

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.

[v-qiaqi-msft]

Hi@User2020,

Based on the issue you mentioned, you want to distinct the 'Firm' and bring you to a more detailed screen by clicking an arrow.

You can use GroupBy to achieve this. In general, add 2 Galleries and 1button.

• Create an app connecting to your list

• Add a Gallery, set the Item property like this:

              GroupBy('0423',"Firm","Group1")

• Add a second Gallery, set the Item property like this:

              Gallery1.Selected.Group1

• Add a TextInput and an button into Gallery3, set the OnSelect property like this:

              Patch('0423',LookUp('0423',ID=ThisItem.ID),{'Order No':TextInput2.Text})

Ps:This helps you edit.

Note: '0423' is my data source, and details have been shown below.

Contact me if you have any doubt.

Best Regards,

Qi Qiao

[User2020]

Thanks for the reply, for whatever reason the code is not working in my existing list. I created a new list using the data above as a test and your code worked fine. I'm implementing both the same way, perhaps the only difference is that my actual data has more columns. I still can't seem to reconcile why it won't work on the actual data set, though.

Do you have any ideas or anything I should check for? Perhaps I'm making a simple mistake?

[eka24]

Did you have any issues with the reply i gave

[User2020]

I had the same issue with your reply - the code worked in a new test list and app but not in my current data set. I'm trying to figure out what is not working but I enter in the formulas and nothing happens with regard to displaying the firms. 

[User2020]

I had the same issue here - this does not work with my list but when I created a mock data set it worked perfectly. Is there anything I should look for in how my list was constructed that could be causing an error?

[eka24]

Can you give a screen shot of what is not working

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

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.

[User2020]

Sure, see below. I'm wondering if the issue is that the "Firm" field is a choice? So the user is able to input into that field from a set list.

Here is the code that was originally set in the field if that helps "SortByColumns(Filter([@'Data Source'], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))"