Advanced Marketing List

(0) Share

Report

Posted on by RaulOcana

8,270

Hi guys,

I have a new entity (activities) that has a 1-to-Many relationship against Lead entity. I have used it as a sub-grid in the form.

I need to create a Marketing List that queries all Leads that have X and Y activities, but I still don't know how, because if I try to use AND, it will try to find a Lead with 1 record with both (X and Y) activities and that doesn't exist, what I need, is to find a Lead that have 1 X activity and 1 Y activity, does that make sense?

Regards,

Raúl

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Bruno Lucas 5,421 on at

Like (1)

Report
Copy link

Link copied!

you can combine "AND" and "OR" to design some pretty complex queries. But on your case sound you just need an "OR" rather than a "AND". When you say "I have a new entity (activities)" do you mean you created your own "activities" entity?

Here is an example with the Out of the box activity

When you say "what I need, is to find a Lead that have 1 X activity and 1 Y activity"

that still sounds like an "AND". if you use OR you will get 3 types:

Lead with X

Lead with Y

Lead with Both

If you use "AND" it will only return leads with BOTH Z and Y

if you combine like "AND + an "OR" you could have BOTH and Z only

Was this reply helpful? Yes No
Suggested answer

Bruno Lucas 5,421 on at

Like (0)

Report
Copy link

Link copied!

If you are try to "count", like get me a lead with 1 activity of type X but do not return a lead with 2 or more activity of type "X", than this is not supported

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report
Copy link

Link copied!

Hi Bruno, I need this for example:

I need to find this lead that has these activities: "use of the modeler" and "studio download".

And yes I created a new entity not replacing activites.

Regards,

Raúl

Was this reply helpful? Yes No
Suggested answer

Bruno Lucas 5,421 on at

Like (0)

Report
Copy link

Link copied!

Can you see your "new activity" custom entity under "related" when you search leads? your screen shot shows that there is a relation 1:N, but you need also to make sure the entity is set to be searched, otherwise it wont be available on advanced find

now you need to ad a "where marketing activity value equal "use of the modeler" and another line with "where marketing activity value equal "studio download". if you bind there two with an "AND", the records will look like your screen shot (1 lead with 1 or more activity of each type)

If you use "OR", it will return a mix of

1 lead with 1 or more "use of the modeler"

1 lead with 1 or more "studio download"

1 lead with 1 or more "studio download" and "use of the modeler"

Like I mention, you cannot search for a count, like "show me a lead that has 1 use of the modeler". If you want to count than you will need to customize it further like create a field on the lead called "countuseofthemodeleractivities" and everytime an activity of that type is added you could have an workflow or JS to auto increment it

Do you have this on your new Activities custom entity:

Was this reply helpful? Yes No
Bruno Lucas 5,421 on at

Like (0)

Report
Copy link

Link copied!

I think I see what you mean. the "AND" is returning leads that contain only 1 type of activity. Anyway, those are the operators you can use. you can run SQL profiler to see how that translates into SQL.

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report
Copy link

Link copied!

Hi Bruno,

Yes I can see on the related entities.

But if I put equals to X activity AND equals to Y activity it won't throw me any results. I don't know how to sub-query or something like that to accomplish that.

Also I read that I can add a group of records to a Marketing List and then remove a couple to accomplish this, but I can't get how to do it.

Regards,

Raúl

Was this reply helpful? Yes No
Verified answer

Bruno Lucas 5,421 on at

Like (0)

Report
Copy link

Link copied!

you must highlight the rows you want to group or click "select row"

this video may explain it better

www.youtube.com/watch

Was this reply helpful? Yes No
RaulOcana 8,270 on at

Like (0)

Report
Copy link

Link copied!

Thanks Bruno, that will help!

Was this reply helpful? Yes No