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

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
Community Champion
Community Champion

Hi @Lars1990 

 

Using With to avoid delegation issue

 

StalinPonnusamy_0-1634310560183.png

 

View solution in original post

12 REPLIES 12
StalinPonnusamy
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

Hi @Lars1990 

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

Hi @Lars1990 

 

Using With to avoid delegation issue

 

StalinPonnusamy_0-1634310560183.png

 

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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