cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power_Robert
Level: Powered On

Filter DropDown excluding existing items.

Hi,

 

I would like to filter a collection that is used to populate a DropDown (Items). The collection should contain all items, except the items that exist in an other collection. Example: List1 [1,2,3,4,5], List2 [2,4], Items shown in the DropDown [1,3,5].

 

To test this scenario I created 2 SharePoint lists:

1.png

And created this and works as intended:

2.png

 

In the actual app, there are two lists; Planning_Line and Time_Option. In Planning_Line, there is a LookUp column to Time_Option. This LookUp column in Planning_Line is called Tijd. The column with the time value in Time_Option, is called Title.

 

Now I try to do almost the same as the example above with the collections in the actual app, but I cant get it to work. (I don't think I fully understand the syntax of this filter...):

3.png

 

I also have tried to create an extra connection to the Time_Option list and use that as source collection in the function above, to be able to try different syntax in the Not() function but that also didn't work. I clearly do not fully understand the syntax here so any help is welcome.

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Level 8

Re: Filter DropDown excluding existing items.

@Power_Robert  last night when I was researching your question I thought Planning_Line and Time options were collection variables but this morning when I re-read your post I believe these are actually Sharepoint Lists instead, correct?  In that case there's a different answer and I'll try to explain it with a small demo app I wrote.  You might find re-creating this demo app will help you figure out what to do for your own specific situation.

My 1st step was to create a blank canvas app and make 2 connections to my Sharepoint Lists called Assignments and Time Options. Then I add btn called "btn_collect" and set OnSelect property to the following code to bring them into collection variables called List1 and List2.


ClearCollect(List1,ForAll(TimeOptions, {Tijd: Tijd}));ClearCollect(List2,ForAll(Appointments,{Tijd: Tijd.Value}));

You can see the contents of List1 and List2 in the image of my app below.  This is just a nice visual for the purpose of explaining.  You don't need to do this in your app.


Finally, I created a dropdown control called "drp_list" and used this code in the Items property.


Filter(List1,Not(Tijd in List2.Tijd))

I believe the main issue we had was with the lookup value in the Appointments table.  Powerapps reconigizes this as an object data type but we want a number instead.  That's why I used the code {Tijd: Tijd.Value}.  We didn't use the Choices function here at all.  You would use Choices when referencing a single column in a datasource (i.e. Sharepoint) but when we are combining multiple tables its not necessary.


DemoPanel1.png

View solution in original post

10 REPLIES 10
mdevaney
Level 8

Re: Filter DropDown excluding existing items.

Hello there Robert,

My suggestion would be to remove the Choices function so the Items property of your drop-down control looks like the formula below. Depending on your schema you might have to swap the position of Planning_Line and Time_Option.  Give it a try and let me know how it goes.

 

Filter(Planning_Line.Tijd,Not(Tijd in Time_Option.Tijd))

 

---

Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly.  If your thought the post was helpful please give it a "Thumbs Up."

 

Power_Robert
Level: Powered On

Re: Filter DropDown excluding existing items.

Hi @mdevaney,

 

Thanks, but it doesn't work.  ".Value" can't be added when removing the Choices() function and without ".Value" it doesn't work either.

mdevaney
Level 8

Re: Filter DropDown excluding existing items.

@Power_Robert when researching your issue last night I thought that Planning_Line and Time_Options were names of collections but they must be Sharepoint List names instead.  In that case, the 1st step is to create single column collections having Tijd for both Planning_Line and Time_Options.

 

Here's what I did to reproduce your issue.  I created an app with 2 Sharepoint list data sources: Appointments and TimeOptions.  Next I created a button called "btn_collect" and put the following code in the OnSelect property:

 

ClearCollect(List1,ForAll(TimeOptions, {Tijd: Tijd}));ClearCollect(List2,ForAll(Appointments,{Tijd: Tijd.Value}));

 

Now once the button is clicked the collections appear in the two data tables below.  This is only to show a nice visual of what the collection looks like but its not necessary to do in your app.

 

Next I created a drop-down control and input the following codes into the Items property:

 

Filter(List1,Not(Tijd in List2.Tijd))

 

When the drop down is clicked I was able to achieve the desired result.  (See image below).

 

I think one of the pain points we both had while troubleshooting this issue was the lookup column.  A lookup column is an object data type but we need a number instead.  That's why I had to loop over Tidj.Value instead of just Tijd.

 

DemoPanel1.png

Let me know if you are able to get the same result

mdevaney
Level 8

Re: Filter DropDown excluding existing items.

@Power_Robert  last night when I was researching your question I thought Planning_Line and Time options were collection variables but this morning when I re-read your post I believe these are actually Sharepoint Lists instead, correct?  In that case there's a different answer and I'll try to explain it with a small demo app I wrote.  You might find re-creating this demo app will help you figure out what to do for your own specific situation.

My 1st step was to create a blank canvas app and make 2 connections to my Sharepoint Lists called Assignments and Time Options. Then I add btn called "btn_collect" and set OnSelect property to the following code to bring them into collection variables called List1 and List2.


ClearCollect(List1,ForAll(TimeOptions, {Tijd: Tijd}));ClearCollect(List2,ForAll(Appointments,{Tijd: Tijd.Value}));

You can see the contents of List1 and List2 in the image of my app below.  This is just a nice visual for the purpose of explaining.  You don't need to do this in your app.


Finally, I created a dropdown control called "drp_list" and used this code in the Items property.


Filter(List1,Not(Tijd in List2.Tijd))

I believe the main issue we had was with the lookup value in the Appointments table.  Powerapps reconigizes this as an object data type but we want a number instead.  That's why I used the code {Tijd: Tijd.Value}.  We didn't use the Choices function here at all.  You would use Choices when referencing a single column in a datasource (i.e. Sharepoint) but when we are combining multiple tables its not necessary.


DemoPanel1.png

View solution in original post

Power_Robert
Level: Powered On

Re: Filter DropDown excluding existing items.

Hi @mdevaney ,

 

Thanks a lot!

 

Now this is working, the situation is getting even more difficult. The Time_Option list also has a LookUp column Reserved_For. I'am trying to add this to the collection, but I have no option to do so.

 

Example of what I was expecting to work:

ClearCollect(TimeOption_Col,ForAll(Time_Option, { Id: ID, TimeOption: Title, Customer: Reserved_For}));

Or

ClearCollect(TimeOption_Col,ForAll(Time_Option, { Id: ID, TimeOption: Title, Customer: Reserved_For.Title }));

Do you know if it possible to add the lookup object, id or title to the collection?

mdevaney
Level 8

Re: Filter DropDown excluding existing items.

@Power_Robert 

 

Have you tried the Value property of the Reserved_For lookup column?

 

ClearCollect(TimeOption_Col,ForAll(Time_Option, { Id: ID, TimeOption: Title, Customer: Reserved_For.Value }));

In your original question I was actually using a LookUp column and getting the Value.  Here's the previous example.

 

ClearCollect(List2,ForAll(Appointments,{Tijd: Tijd.Value}));

 

Power_Robert
Level: Powered On

Re: Filter DropDown excluding existing items.


@mdevaney wrote:

In your original question I was actually using a LookUp column and getting the Value.  Here's the previous example.

 

ClearCollect(List2,ForAll(Appointments,{Tijd: Tijd.Value}));

 


Hi @mdevaney ,

 

Yes, that is true. But for some reason Gereserveerd voor is not available in the "intellisense" underneath the function input:
1.png

 

I've also done a refresh on the connection a few times. But that didn't help either.

mdevaney
Level 8

Re: Filter DropDown excluding existing items.

@Power_Robert 

I noticed your Sharepoint column Gereserveerd voor is two words separated by a space  Intellisense did not find it because you have to reference it with single quotes like this:

 

Customer: 'Gereserveerd voor'

 

If its a LookUp column you'll need to go one step further

 

Customer: 'Gereserveerd voor'.Value

 

For this reason, when I build a Sharepoint List for myself I always make the columns one word  (e.g. GereserveerdVoor)

Power_Robert
Level: Powered On

Re: Filter DropDown excluding existing items.


@mdevaney wrote:

@Power_Robert 

I noticed your Sharepoint column Gereserveerd voor is two words separated by a space  Intellisense did not find it because you have to reference it with single quotes like this:

 

Customer: 'Gereserveerd voor'

 

If its a LookUp column you'll need to go one step further

 

Customer: 'Gereserveerd voor'.Value

 

For this reason, when I build a Sharepoint List for myself I always make the columns one word  (e.g. GereserveerdVoor)


I do agree that it is better to name the columns without a space. I'll rename them as I see them. I removed the space from this one as well and refreshed the data source, but still no solution. I tried the single quote as well. The column just don't show up...

 

---- Edit ----
I just tried the same in a sandbox and it worked just fine. I think I'll remove and recreate the connection to the data source and see what happened. To be continued...

 

--- Solution ---

When comparing the two SP Lists and lookup columns, I found a difference. When looking at the column settings, there is a field "In this column". The name of the column I wanted, was there in 2 versions; as "ColumnName" and "ColumnName (linked to item)". I used the last one, which was causing the issue:

0.PNG


Unfortunately I can't tell why this is causing an issue, but hopefully this info will help others as well.

 

@mdevaney Thank you for your time and effort!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 119 members 3,193 guests
Please welcome our newest community members: