cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lars1990
Helper II
Helper II

Filter Combobox items with another table LookUp value

Hi all,

 

I am tryting to filter items in a combobox based on a column in a different table. Where the column in table 2 is a lookup value from table 1. I tried to do it like below but it does not work.

 

Any suggestions?

 

Filter(Table1,Product in Table2.Product)

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Super User
Super User

Hi @Lars1990 

 

Using With to avoid delegation issue

 

StalinPonnusamy_0-1634310560183.png

 

View solution in original post

12 REPLIES 12
StalinPonnusamy
Super User
Super User

Hi @Lars1990 

 

SP List

 

Filter(
    Choices(Table1.LookupColumnName),
    Value in Filter(
        Table2,
        Condition
    ).Title
)

 

 

Dataverse (Sample)

    Filter(
        Choices('Car Rentals'.Account),
        Account in Filter(
            Accounts,
            !IsBlank('Address 1: City')
        ).Account
    )

 

  • 'Car Rentals' is a custom table that has a lookup to Account Table
  • Filtering if the city is not blank

 

Sorry I dont get it to work.

 

Here a little more info:

 

Table: Products

Product
605 ERDINGER Weissbier Keg 30L
2435 ZZ_ERDINGER Urweisse Keg 30L
9049 ZZ_AMSTEL RB Crate 18x50cl NL

 

Table: Delistings

Product (Lookup)
201 ZZ_AMSTEL NRB Tray 2(12x25cl) HKBR TC NL
202 ZZ_BRAND Keg 30L NL
206 ZZ_AMSTEL RB Crate 18x50cl NL

 

I want to filter table Products on table Delistings. So in this example only item 206 ZZ_AMSTEL RB Crate 18x50cl NL should be in the filtered items since that product is in both tables.

 

KR Lars

Sorry I still do not understand 😞

 

I copied the code I tried below. 'Product (cr38a_product)' is the column which is both in table products and in table Delistings (as lookup). And the data is in DataVerse.

Lars1990_1-1634306433521.png

 

 

StalinPonnusamy
Super User
Super User

Hi @Lars1990 

 

If the Lookup other 2 items also should be there on the product right?

Where are the other 2 items pointing to? Where does it come from?

 

Hi Stalin,

 

Table: Products

Product
605 ERDINGER Weissbier Keg 30L
2435 ZZ_ERDINGER Urweisse Keg 30L
9049 ZZ_AMSTEL RB Crate 18x50cl NL

Etc.

 

Table: Delistings

Product (Lookup)
201 ZZ_AMSTEL NRB Tray 2(12x25cl) HKBR TC NL
202 ZZ_BRAND Keg 30L NL
206 ZZ_AMSTEL RB Crate 18x50cl NL

Etc.

 

I want to only show products in the form below which are NOT in table Delistings

Lars1990_0-1634307525274.png

StalinPonnusamy
Super User
Super User

Hi @Lars1990 

With(
    {
        _Product: Products,
        _Delistingproduct: Distinct(
            'Delistings',
            Product.Product
        )
    },
    Filter(
        _Product,
        Product in _Delistingproduct
    ).Name
)
StalinPonnusamy
Super User
Super User

Hi @Lars1990 

 

Using With to avoid delegation issue

 

StalinPonnusamy_0-1634310560183.png

 

It works! Thanks, wow!😀

A different issue pops up now, I cannot submit the form due to this message:

Value must be a data entity record

Lars1990_0-1634547867755.png

I solved this by changing the code

With(
    {
        _Product: Sort(Products,'Product (cr38a_product)'),
        _Delistingproduct:Distinct(Delistings,Product.'Product (cr38a_product)')},Filter(_Product,Not('Product (cr38a_product)' in _Delistingproduct)))

 

Unfortunatly it only shows the first 500 items (delegation?), is there a way to solve this?

 

@StalinPonnusamy 

 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (2,934)