filter some items from data source

(0) Share

Report

Posted on by mrQ

482

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

v-albai-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-albai-msft on at

Like (1)

Report

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

Was this reply helpful? Yes No
mrQ 482 on at

Like (0)

Report

Sounds to me like a bug... 😕

Was this reply helpful? Yes No