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

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Issue / Bug filtering ...
Power Apps
Unanswered

Issue / Bug filtering One-to-many relationship

(0) ShareShare
ReportReport
Posted on by RemiChambard2 166

Hello,

 

I'm playing with Dataverse and filtering data. For my sample, I have two Entities :

  1. Project
  2. Member

They are linked each other with a Many-To-Many relationship (so a One-To-Many relationship both sides).

 

I'm a trying to display all "Opened" Projects for a specific Member. To do so, I first get the Member using a Lookup on the Member table then Filter the "relationship" on the status like that :

 

ClearCollect(
 colProjects;
 Filter(
 myMember.Project;
 ThisRecord.ProjectStatus = "Open"
 )
)

 

My issue is that the query raises an error when looking in the Monitor (no error in the Power Apps Studio designer) with the following error returned by the internal query sent to Dataverse by Power Apps : Expression expected at position 0 in '/pub_projectstatus eq 'Open''.

 

Here is the query that is generated by Power Apps (as seen in the monitor) : https://[org].crm4.dynamics.com/api/data/v9.0/pub_members(0c350c17-5711-ec11-b6e6-000d3ab70dc0)/pub_projects_members?$filter=/pub_projectstatus eq 'Open'

 

The issue is here (the "/" in red in the query) : https://[org].crm4.dynamics.com/api/data/v9.0/pub_members(0c350c17-5711-ec11-b6e6-000d3ab70dc0)/pub_projects_members?$filter=/pub_projectstatus eq 'Open' . Removing the "/" and executing the query in my browser works as expected.

 

Strange isn't it as it seems that there is a bug in Power Apps ?

 

Thanks

Categories:
Microsoft Dataverse with Power Apps
I have the same question (0)
  • Prakash4691 Profile Picture
    Prakash4691 1,332 on at

    @RemiChambard2 

     

    If project status is an optionset, it will thrown an incompatible error for datatype.

     

    FYI, to filter records using option set in canvas app, please find below sample formula.

     

    ThisRecord.Status = 'Status (Test 2S)'.Active. Status is an optionset field present in Test 2 entity.

     

    The underlying operation which tries to execute rest/web api of the datasource for any CRUD operation. If any issue in your formula, you will not find any entries for that formula in monitor.

     

    If it answers your question, kindly give kudo and accept as solution.

     

     

    Regards,

    Prakash

  • RemiChambard2 Profile Picture
    RemiChambard2 166 on at

    Thanks @Prakash4691 but that's not my issue. Currently I'm using a simple text field.

     

    A I mentioned above, I don't have any issue in Power Apps Studio when writing the formula. But when I execute it by clicking on a button, I can see the REST API error in the Monitor as if the generated ODATA query was mis-generated which I don't have any control as it is built in by the Datavers connector.

  • Prakash4691 Profile Picture
    Prakash4691 1,332 on at

    @RemiChambard2 

     

    Oh right, even I tried filter using text field I did not find any issue in request URL.

     

    And also in monitor, request URL is in encoded way. Attached screenshot for your reference.

    Prakash4691_1-1631258440851.png

     

    It's weird, the one you have provided is a decoded URL. Only in a browser or if you try to make any request from other client application you might send request like that.
     
    To identify the root cause, could you please attach any screenshot for the same.
     
     
    Regards,
    Prakash
  • RemiChambard2 Profile Picture
    RemiChambard2 166 on at

    @Prakash4691 I'll do additional testings to provide you more infos. The URL is decoded in my sample as I used a tool to make it more readible in the forum.

    However the main difference between my query and yours is that I perform the filter on the related entity. Not the main entity : Filter(myMember.Projects, ...). myMember being the Member I got previously from a LookUp query.

  • Prakash4691 Profile Picture
    Prakash4691 1,332 on at

    @RemiChambard2 

     

    Yes, I can see that but the main issue is line appearing before filter after decoding the url.

     

    Not sure what kind of tool you might have used to decode the URL.

     

    If you are getting expected result then it is fine. If not, I would suggest you to look into the formula to make necessary amendments.

     

    And kindly do provide additional info. I will have a look and get back to you if I have any info from my side.

     

     

    Regards,

    Prakash

  • RemiChambard2 Profile Picture
    RemiChambard2 166 on at

    Let me give you more info based on a new test with screen captures.

    Here is my Data Model : 2 Tables linked with a N-N relationship

     

    2021-09-10_17h51_24.png

     

    A simple Canvas application with a button with the following formula in the OnSelect property : 

     

    UpdateContext(
 {
 member: LookUp(
 Members;
 Name = "Remi"
 )
 }
);;
ClearCollect(
 colProjects;
 Filter(
 member.Projects;
 ThisRecord.Status = "Open"
 )
)

     

    First, I get a specific Member in the relating Table. Then, I want to filter the Projects of that Member on the Status field to get only Opened projects.

     

    2021-09-10_17h50_11.png

     

    You can notice that there are no error in the formula.

     

    When I click on the button and look into the monitor, here is what I see :

     

    2021-09-10_17h55_40.png

     

    No error during the Lookup (Line 2 in the monitor). But an error during the call to get the related Projects with the filter operation on the Status.

     

    Looking in details in the call, here is what is displayed by the monitor :

     

    2021-09-10_17h57_18.png

    And, here is the query performed against Dataverse :

     

    2021-09-10_17h49_37.png

     

    https://org.crm4.dynamics.com/api/data/v9.0/cr5c8_memberses%28b3fa24ae-4d12-ec11-b6e6-000d3ab70dc0%29/cr5c8_Members_cr5c8_Projects_cr5c8_Projec?%24filter=%2Fcr5c8_status+eq+%27Open%27

     

    And here is the error in the query generated by the Dataverse connector (overlined in yellow) :

     

    2021-09-10_17h49_45.png

  • Prakash4691 Profile Picture
    Prakash4691 1,332 on at

    @RemiChambard2 

     

    Formula looks odd. Try as below,

     

    UpdateContext(
    {
    member: LookUp(
    Members,
    Name = "Remi"
    )
    }
    );
    ClearCollect(
    colProjects,
    Filter(
    member.Projects,
    ThisRecord.Status = "Open"
    )
    )

     

    Inside a function you can't use ; instead it has to be , .

     

     

    Regards,

    Prakash

  • RemiChambard2 Profile Picture
    RemiChambard2 166 on at

    That's normal @Prakash4691 as my Power Apps Studio is set in French and "," are replaced by ";".

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

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the January Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 101 Most Valuable Professional

#2
Haque Profile Picture

Haque 81

#3
VASANTH KUMAR BALMADI Profile Picture

VASANTH KUMAR BALMADI 70

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans