Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

(0) Share

Report

Posted on by gbev

Hi,

I have an issue with Advanced Find. I have 2 entities: Contacts (Standard Entity), Devices (Custom Entity).
These entities are connected with a one-to-many relationship (1:N): 1 Contact can have multiple Devices.

The entity "Devices" has a custom option set field which is called "Device Status". It accepts 3 different options: a) Working, b) Under Repair, c) Broken.

I would like to perform an Advanced Find to show all the Contacts that have ALL THE RELATED DEVICES with Device Status equal to Under Repair or Broken.

So, I created this Advanced Find: Search for Contacts where the related entity Devices has the field Device Status equal to Under Repair or Broken.

Unfortunately this query allows me only to find all the Contacts that have AT LEAST ONE RELATED DEVICE with Device Status equal to Under Repair or Broken, but I would like to know ALL THE RELATED DEVICES with a certain Device Status. Is there any way I can do it?

Thank you all.

All responses (13)

Answers (1)

gbev 5 on at

Like (0)

Report
RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Leco Lv,

sorry, I made a typo on my message on 20 Jan 2023 9:43 AM. I really apologize.

The actual relationship between these entities is the one listed on my first message of this thread (on 19 Jan 2023 9:49 AM). To recap:

1 Contact may have 1 ore more associated Devices

1 Contact may have 1 ore more associated Social Profiles

This is the reason why, thanks to your very helpful support, I'm quite sure that it's not possible to have the view I need without xrmtoolbox.

Thanks for any further suggestion and sorry I wasted your time.

Best regards,
Leco Lv on at

Like (0)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Araschi,

The entity relationship you mentioned earlier may have caused our data model to be different. Sorry I keep ignoring this, I think the entity relationship you listed may not be correct, please ignore if it is your typo. You listed earlier 'Assume Paul (Contact) has 3 social profiles (Facebook=active, Instagram=active, LinkedIn=inactive) and 3 devices (iPhone=broken, iPad=under repair, Macbook=working)' '. So I think the relationship between social profiles and contacts should be N:1 instead of 1:N as you listed. The same goes for the relationship between devices and contacts.

BTW, Allison Brown in the example I gave is all three repairs.

I will be on vacation for two days; any progress is welcome to update at any time.

Best regards
gbev 5 on at

Like (0)

Report
RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Leco Lv,

if my understanding is good, I think it's not possible without xrmtoolbox. The reason is the following:

My data model is different than the one you described. My data model is like the following one:

Devices:

Social Profiles:

Therefore, if I perform the query as you suggest, I should:

Look for Social Profiles

Where Status = Active

Where related Contact Contains Data

Where related Devices have:

Status different than Working

But in this way, using the example of the print screen above, unfortunately I will see the Service Profile related to Allison Brown even if he has 3 devices (1 broken, 1 under repair and 1 working). The reason is that the condition "Status different than Working" allows to show the social profiles related to Allison Brown although he doesn't have all the devices broken or under repair.

This is my real issue with the standard Dynamics 365 Advanced Find. I hope to allow you to understand better my use case.
Verified answer

Leco Lv on at

Like (0)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi partner,

I made it so complicated! now I understand. You can query using the same criteria, but this time make social profiles the first criteria.

But you need to add all the columns you need for the final view. No need to use xrmtoolbox.

Result:
gbev 5 on at

Like (0)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Leco Lv,

thanks, you answers have been very helpful.

Best regards
Leco Lv on at

Like (1)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi partner,

If you just rely on advanced Find, it may not be possible, you may need to use XRMToolbox to modify the view.
gbev 5 on at

Like (0)

Report
RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Leco Lv,

thank you, This is a very good suggestion and I might consider it as a workaround if my goal is unattainable.

It allows me to look for all the contacts with all the devices Broken or Under Repair, and with all the Social Profiles active.

Contact 1

Contact 2

Contact ...

Unfortunately, I would like to lighten my colleagues' workload by providing them with a list of all the Social Profiles they need to contact to apologize to our Contacts for the fact that they have all their devices down. With your (very useful) solution, they will only have the list of all Contacts with all devices not working and will then have to manually open each contact to find out which Social Profile to contact to apologize to that Contact.

Social Profile A (of Contact 1)

Social Profile B (of Contact 1)

Social Profile C (of Contact 2)

...

Is there any additional way to look for Active Social Profiles, related to Contacts that have all the related Devices Under Repair or Broken?

Thanks
Suggested answer

Leco Lv on at

Like (1)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi partner,

How about this?

Result:

I'm assuming several scenarios for the device status & Social profiles.

Alexx Wu is 1good, so he will not exist in the result.

1.

2.
gbev 5 on at

Like (0)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi Leco LV,

let's assume that Paul (Contact) has 3 Social Profiles (Facebook = Active, Instagram = Active, Linkedin = Inactive) and 3 Devices (Iphone = Broken, Ipad = Under Repair, Macbook = Working).

I would like to find all Active Social Profiles related to Contacts that have all Devices Broken or Under Repair.

If I perform the search you suggested, I will:

- Look for Social Profiles = Active

- With related Contact that Contains Data

- With related Devices with Status = Broken OR Under Repair

In this way I will get also my contact Paul, but I don't want to find him since Paul has also a Device (the Macbook) which is working fine. I only would like to search for all the contacts with all the devices Broken OR Under Repair.

I hope to have been clear enough and thank you for your much appreciated help.
Leco Lv on at

Like (0)

Report

RE: Advanced find: "All of them" condition and not "At least one of them" condition for entities with 1:N Relationship

Hi partner,

Did my answer help you, or is there anything you don't understand?