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.

I have the same question (0)

All responses (13)

Answers (1)

Leco Lv on at

Like (0)

Report

Hi partner,

You need to change "search for Contacts" to "search for Devices". Devices are your custom entities.

What you want to know here is all related devices with a specific device status. Not related to contacts.

If you want to filter two entities at the same time, you can add related entities below. Here I use accounts instead of contacts for demonstration.

Hope it helps!

Was this reply helpful? Yes No
gbev 5 on at

Like (1)

Report
Hi Leco LV,

very useful, thank you. Now I understand that my question was wrongly asked. My final goal is to find all Active Social Profiles related to Contacts that have a Device Broken or Under Repair. As you can imagine, I have 3 different entities:

Contacts

Social Profiles: 1:N Relationship with Contacts

Devices: 1:N Relationship with Contacts

Starting from Devices I can't go up in the search for 2 levels: Search for Devices Broken or Under Repair, with related Contacts that have the Social Profile Active. This is my real issue. Sorry for not being clear from the beginning.

Thanks.

Was this reply helpful? Yes No
Leco Lv on at

Like (0)

Report

Hi partner,

try the following.

Accounts ->Social Profile

contacts->contacts

Leads->Devices

Was this reply helpful? Yes No
Leco Lv on at

Like (0)

Report

Hi partner,

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

Was this reply helpful? Yes No
gbev 5 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Leco Lv on at

Like (1)

Report

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.

Was this reply helpful? Yes No
gbev 5 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Leco Lv on at

Like (1)

Report

Hi partner,

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

Was this reply helpful? Yes No
gbev 5 on at

Like (0)

Report

Hi Leco Lv,

thanks, you answers have been very helpful.

Best regards

Was this reply helpful? Yes No
Verified answer

Leco Lv on at

Like (0)

Report

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:

Was this reply helpful? Yes No