web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / I am getting no result...
Power Apps - Microsoft Dataverse
Suggested answer

I am getting no results from a Filter() on a Dataverse table based on properties of a related table (one-to-many relationship).

(0) ShareShare
ReportReport
Posted on by JC-04041807-0 11

Hi folks,
 
I'm having a search challenge that I want to solve in a delegable way and I'm hoping someone has figured this out already or can point out my error.
 
I have two tables:
PR_MockNRAs
and
PR_MockNLIs
 
There is a one-to-many relationship between them (one NRA to many NLIs).  Therefore, the PR_MockNLIs table has a lookup column called "relatedNra".
 
I want to display all of the NRAs who have at least one related NLI that meets certain conditions.
 
My problem is that my formula does not return any results.
 
To simplify the problem, I have reduced my filter such that it should show any NRA which has at least one related NRI which has a "relatedNra" code that matches the NRA code.
 
Here is my formula:
 
Filter(
    PR_MockNRAs As NRA,
    CountRows(
        Filter(
            PR_MockNLIs As NLI,
            NLI.relatedNra.Code = NRA.Code)
        ) > 0
    )
 
There are no errors or warnings in the editor but the results count is 0.  I have made double sure that I created some test content where there are some NRAs with related NLIs.
 
Any ideas?  I've been searching online and trying a few options but can't get any to work.  We do have a way to do this today using local collections (ClearCollect()) but that is not delegable and won't work for a large dataset.  I would like to find a better solution.
 
Thanks
 

Categories:
Microsoft Dataverse
Tables
I have the same question (0)
  • WarrenBelz Profile Picture
    WarrenBelz 150,276 Most Valuable Professional on at
    I am getting no results from a Filter() on a Dataverse table based on properties of a related table (one-to-many relationship).
    Yes - it should be Delegable according to to Dataverse specs. Also CountRows() is not going to help you as it is a "local" function and will only act on record numbers up to your Data Row Limit. How many records are in each list and also does this work ? 
    Filter(
   PR_MockNRAs,
   Code in PR_MockNLIs.relatedNra.Code
)
     
    Please ✅ Does this answer your question 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 a Like♥.
    Visit my blog     Practical Power Apps    LinkedIn   
     
  • JC-04041807-0 Profile Picture
    JC-04041807-0 11 on at
    I am getting no results from a Filter() on a Dataverse table based on properties of a related table (one-to-many relationship).
    Thanks WarrenBelz for responding so quickly!

    Your suggestion actually did return results as expected.  Unfortunately, the editor is claiming that the NOT operator (!) is not delegable for some reason.  I tried using the NOT() formula and had the same warning.  This is strange because if I'm reading the docs right, the NOT operator should delegate fine.
     
     
    Ideas?
  • Suggested answer
    WarrenBelz Profile Picture
    WarrenBelz 150,276 Most Valuable Professional on at
    I am getting no results from a Filter() on a Dataverse table based on properties of a related table (one-to-many relationship).
    Maybe something like this 
    Filter(
   PR_MockNRAs As NRA,
   !IsBlank(
      LookUp(
         PR_MockNLIs,
         relatedNra.Code = NRA.Code
      )
   )
)
     
    Please ✅ Does this answer your question 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 a Like♥.
    Visit my blog     Practical Power Apps    LinkedIn   

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

MS.Ragavendar – Community Spotlight

We are honored to recognize Ragavendar Swaminatha Subramanian as our September…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 1,043 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 348 Super User 2025 Season 2

#3
Power Apps 1919 Profile Picture

Power Apps 1919 345

Last 30 days Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans