Filter DropDown excluding existing items.

[Power_Robert]

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:

And created this and works as intended:

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...):

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.

[mdevaney]

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]

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]

@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.

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

[mdevaney · Accepted solution]

@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.

[Power_Robert]

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]

@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]

---

@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:

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

[mdevaney]

@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]

---

@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:

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!