cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter list item not in another list via lookup

I have a combo box and I want here to display the items in Purchase Order that are not associated to any documents yet.

 

My List:

Purchase Orders

Text: Purchase Order Number

 

Documents
Lookup: Purchase Order Number

I have tried Purchase Order Number, ID but not luck.

Lym_0-1592797725308.png


Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filter list item not in another list via lookup

Hi @Lym ,

You will also get a delegation warning on this - your current code looks OK, but this is another angle.

 

Filter(
   'Purchase Orders’,
   IsBlank(
      Lookup(
         'Documents xx'
         'Purchase Order Number' = 'Purchase Orders'.[@'Purchase Order Number']
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

6 REPLIES 6
Highlighted
Super User
Super User

Re: Filter list item not in another list via lookup

Hi @Lym ,

You will also get a delegation warning on this - your current code looks OK, but this is another angle.

 

Filter(
   'Purchase Orders’,
   IsBlank(
      Lookup(
         'Documents xx'
         'Purchase Order Number' = 'Purchase Orders'.[@'Purchase Order Number']
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

Highlighted
Community Support
Community Support

Re: Filter list item not in another list via lookup

Hi @Lym :

Could you tell me:

What are your date source?Are they SharePoint lists?

Does the lookup filed allow multiple selections?

I assume they are sharepoint lists,and the lookup field does not allow mutiple selections.

I've made test for your reference.

My data source:

'Purchase Orders'

1.JPG

'TheDocuments'

2.JPG3.JPG

1\Add a gallery and set it's Items property to:

 

Filter(
    'Purchase Orders',
    Not(
        'Purchase Order Number' in ForAll(
            TheDocuments.'Purchase Order Number',
            'Purchase Order Number'.Value
        )
    )
)

 

4.JPG

Best Regards,

Bof

Highlighted
Frequent Visitor

Re: Filter list item not in another list via lookup

@WarrenBelz  @v-bofeng-msft,

 

Thank you so much for the response, I appreciate it.
Both of the code are working and displaying the Purchase Order that is not associated to any documents but both also are showing delegation warning - The filter part of this formula might now work on large data sets.

Another issue I encounter is when I set the combo box searchable property to true it does not show any data. Why is it not showing any data and how do I fix this? 

Thank you again!

Highlighted
Super User
Super User

Re: Filter list item not in another list via lookup

Hi @Lym ,

I mentioned you would get that as IsBlank is not Delegable (nor is Not() in @v-bofeng-msft 's solution.

How many Purchase Orders are there in your Documentsxxx and 'Purchase Orders' Lists? If under 2000, this can be done with a collection.

If the relevant records are always in the newest 2000 records of both Documentsxxx and 'Purchase Orders', then (after setting your limit to 2000 in Advanced Settings) you could run this first

ClearCollect(
   colDocs,
   Sort(
      Documentsxxx,
      ID,
      Descending
   ),
);
ClearCollect(
   colPurchase,
   Sort(
      'Purchase Orders',
      ID,
      Descending
   )
)

then your filter

Filter(
   colPurchase,
   IsBlank(
      Lookup(
         colDocuments
         'Purchase Order Number' = colPurchase.[@'Purchase Order Number']
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Super User
Super User

Re: Filter list item not in another list via lookup

Hi @Lym ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Frequent Visitor

Re: Filter list item not in another list via lookup

Hi @WarrenBelz  yes. Thank you so much for the help.

@v-bofeng-msft  thank you too for the help.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,490)