Issue / Bug filtering One-to-many relationship

(0) Share

Report

Posted on by RemiChambard2

166

Hello,

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

Project
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)

All responses (8)

Answers (0)

Prakash4691 1,332 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
RemiChambard2 166 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
RemiChambard2 166 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
RemiChambard2 166 on at

Like (0)

Report
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

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.

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 :

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 :

And, here is the query performed against Dataverse :

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) :

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
RemiChambard2 166 on at

Like (0)

Report

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

Was this reply helpful? Yes No