Condition in FetchXML for Link-Entity

(0) Share

Report

Posted on by EBMRay

905

Hello,

I have a table called job position that has Parent Job position (Lookup to the same table Job position) and Job Position as Single line text field.

What I want to achieve through fetch XML is the below:

When I filter a Job position that has the Parent Job position blank or null (which means it is the parent) I don't want the query to rely on the link-entity. Because the link-entity purpose is that if Job position has a parent job position that means not null and it is not the parent it should return as a result the Parent job position name of the Job position that I am filtering on.

I tried the below fetchXML query:

The above query returns nothing knowing that the above IT manager position is the parent position and the field is null.

I believe this is because the link-entity is getting executed and found that the parent is null so the logic that should be applied is to not execute the link entity if the parent job is null.

What should be the updated query for this scenario? Could you please provide an example?

Any help is highly appreciated.

Best regards,
EBMRay

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Guido Preite 54,086 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

I read your question 3 times but I probably still don't understand exactly your requirements.

If I understood correctly you have a self-table relationship (because in dataverse you can't create 1-1 relationships you have 1-n, meaning you have 1 parent at the top, n children, each child can be a parent to other n children).

this condition here

<condition attribute="cr884_parent_job_position" operator="null" />

will retrieve only the top parent records, I think you should add the outer clause to the link entity, as the default is inner, something like

<link-entity name="cr884_job_position" from="cr884_job_positionid" to="cr884_parent_job_position" link-type="outer">

you can find fetchxml relationships details here if you are interested markcarrington.dev/.../

however because you already set the condition as null the link entity is completely useless, maybe you can use two queries in order to fetch the data?

hope it helps

Was this reply helpful? Yes No
Suggested answer

Leco Lv Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi EBMRay,

I think there may be two problems with your code.

Your GUID value may be wrong, please check it.

from="cr884_job_positionid" to="cr884_parent_job_position" are written backwards.

Here is my modified code：

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello Guido Preite ,

Sorry for not being clear in my requirements.

Yes, the parent job position is a lookup on the same table itself since we cannot create 1:1 relationship.

I added the below condition to try in a way to not execute the link entity if the parent job position is null but I failed in that.

<condition attribute="cr884_parent_job_position" operator="null" />

In case the parent position was not blank the link entity should be used in order to get the responsible person of this position that's the reason why I am using the link entity in the same query. However, if the parent position was blank that means the position itself is the Head (parent).

I tried to add the link-type outer for the link entity:

Unfortunately, the query did not return any data as result based on the changes we applied.

Could you please advise what might be the issue?

Thank you!

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Leco Lv ,

The GUID number is right and below are some screenshots:

I tried the modified query that you provided and it worked only if the parent job position is null, however, if I changed the guid to a record that has a parent job position meaning not null the query returns nothing.

As a result, based on the above scenario, It should return the responsible employee of this position from the same table itself.

What should be the updated query to solve this issue?

Awaiting your response.

Best regards,
EBMRay

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like
a
(1)

Report
Copy link

Link copied!
Hi EBMRay ，

Do you mean that the result can be returned regardless of whether the position is null or not.

If so , just delete the ninth line : <condition attribute="cr884_parent_job_position" operator="null" />

Regards,

Leco Lv1

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!
Hello ,

Thank you for providing the below query.

I already removed the ninth line and executed the query again on a record that has a parent job position assigned and it returned 0 records as a result:

The record that I triggered my query on is mentioned in a screenshot above previous replies.

Please let me know why it does not work and what we still need to change.

Awaiting your response.

Best regards,
EBMRay

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Leco Lv ,

I would highly appreciate your response based on my last reply.

Best regards,
EBMRay

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello ,

I would highly appreciate your support on this case.

Best regards,

EBMRay

Was this reply helpful? Yes No
EBMRay 905 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello ,

Could you please assist me with this issue?

Thank you!

Was this reply helpful? Yes No
Verified answer

Leco Lv Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi  EBMRay,

I'm sorry it took so long to reply to you, I missed your Notifications.

I'm not quite sure why your code isn't working, and I double-checked your code and found no problems. I executed it myself and successfully queried the results.

The screenshot below contains my table relationship and the result of successful execution.

BAD&erere(child)



Results:



     Here's the code I executed:

Was this reply helpful? Yes No