cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrQ
Helper V
Helper V

filter some items from data source

Dear community

 

I have a dataverse for teams table called Registered Users. It has a column called Azure AD Object ID

Based on another table called Not Valid, I created a collection colNotValid. It has a lookup to the Registered Users table.

 

In my gallery, I would like to show all items in Registered Users WITHOUT the ones that are in the colNotValid collection (column 'cr466_User2').

 

 

Filter(
    'Registered Users', 
    Not(
        'Azure AD Object ID' in colNotValid.cr466_User2.'Azure AD Object ID'
    )
)

// tried the following alternative, but didn't work either (Registered Users is the unique identifier of 'Registered Users'

Filter(
    'Registered Users', 
    Not(
        'Registered Users' in colNotValid.cr466_User2.'Registered Users'
    )
)

 

 

 

Above code doesn't do that and I don't know why. It simply shows ALL users from Registered Users. If I remove the Not() it won't show any users. What am I doing wrong?

 

Thanks in advance for your help.

 

**edit**

This feature is activated but the behavior is the same with collections directly.

feature-active.png

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Microsoft
Microsoft

Hi @mrQ ,

How do you create the collection colNotValid?  Using formula like below(may should be “Not Valids”)?

Collect(colNotValid, 'Not Valid')

If yes, try this to create this collection(save the lookup column into a new column called "test2"):

Collect(colNotValid, AddColumns('Not Valid',"test2",cr466_User2.'Azure AD Object ID'))

Then in your gallery, try this:

Filter(
    'Registered Users',
    Not(
       'Azure AD Object ID' in colNotValid.test2
    )
)

Best regards,

Allen

View solution in original post

5 REPLIES 5
dpoggemann
Solution Sage
Solution Sage

Hi @mrQ,

 

Can you try the following for the filter:

Filter( 'Registered Users', !('Azure AD Object ID' in 'Not Valid'.cr466_User2))

 

I am doing this from memory so hopefully I got it right 😀

 

Hope this helps.

 

Thanks,


Drew

v-albai-msft
Microsoft
Microsoft

Hi @mrQ ,

How do you create the collection colNotValid?  Using formula like below(may should be “Not Valids”)?

Collect(colNotValid, 'Not Valid')

If yes, try this to create this collection(save the lookup column into a new column called "test2"):

Collect(colNotValid, AddColumns('Not Valid',"test2",cr466_User2.'Azure AD Object ID'))

Then in your gallery, try this:

Filter(
    'Registered Users',
    Not(
       'Azure AD Object ID' in colNotValid.test2
    )
)

Best regards,

Allen

View solution in original post

Thanks! That worked!

I just don't get why I need to save it to a new column when this information is already there in a relational database...?

Hi @mrQ ,

Glad you have solved your issue.

Actually, I cannot find an official document mentioned that why we cannot directly use formula like "Table2.lookup.Name". So each time I have your issue, I will keep the lookup result into a new column. And this did work😁

Best regards,

Allen

Sounds to me like a bug... 😕

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Users online (37,097)