Filtering Issue related to custom objects

[milanjain2208]

We've established a custom data structure involving two distinct custom objects: "Application" and "Owner". The "Application" custom object is associated with the standard "Lead/Person" object in a one-to-many manner, indicating that a single lead can have multiple applications.

Similarly, the intention was for the "Owner" object to connect to the "Application" object in a one-to-many manner, signifying that a single application can have multiple owners. However, due to limitations in Marketo, which does not support a second-level custom object relationship (such as Lead <- Parent <- Child), we've linked the "Owner" custom object to the "Person" object.

To simulate the "Applcation <- Owner" connection, we've introduced an "applicationID" field in "Owner" custom object to correlate owners with corresponding applications.

Nonetheless, a challenge arises with our current approach when attempting to filter leads based on specific criteria. For instance, we want to filter leads who have applications with a "completed" status (application.status = "Completed"), and the corresponding application owners should have a score greater than 500 (owner.score > 500). We created a filter in this manner =>

However, this approach yields leads with any application that has a "Completed" status and any owner with a score over 500, regardless of whether the owner's application status is "Completed".

Given this situation, the question arises: Is there a feasible method to achieve this type of filtering? Alternatively, should we consider abandoning the use of using custom objects altogether and consolidate all the data within the standard lead object? It's important to note that we're not inclined toward the latter approach as it would lead to a lot of data duplicacy.

Any insights or suggestions regarding this matter would be greatly appreciated.

[Darshil_Shah1]

Yeah, not a lot of users realize this until it's too late/not while creating the CO. You could set up an aux. field on your primary CO linked to the Person object to also store the owner score value, so you're able to pinpoint the exact CO records that have both the criteria in place in the same CO record i.e., Application Status = Completed, and Owner Score > 500 (not just both the criteria in place across multiple CO records). With the as-is approach, Marketo would qualify all the people who have at least one record on the Application CO such that the Status is Completed and Owner CO such that the Owner Score is > 500, and this need not be associated together. Also, Marketo does support accessing data from velocity 2 level deep and second-level custom object relationship though.

[milanjain2208]

Hi @Darshil_Shah1 ,
Thanks for the reply.
Regarding this =>

---

You could set up an aux. field on your primary CO linked to the Person object to also store the owner score value, so you're able to pinpoint the exact CO records that have both the criteria in place in the same CO record i.e., Application Status = Completed, and Owner Score > 500 (not just both the criteria in place across multiple CO records). 

---

I have multiple owners for this application ( status == "Completed"). Should I understand your suggestion as advising me to include all owner scores within application CO itself? I'd like to mention that the count of owners might differ between applications, and the situation we're discussing is just one example among numerous scenarios.

Regarding this =>

---

Also, Marketo does support accessing data from velocity 2 level deep and second-level custom object relationship though.

---

Accessing second-level custom object in email script is fine but how do I create second-level custom object relationship in marketo ?

[Darshil_Shah1]

Could there be a case where a single CO record could have multiple Owners (and, if yes, along the same lines, is the number of owners that could be tied to a single Application CO record not fixed)? I understand you have more than possible Owners, but if a single Application CO record could at a single time have just 1 Owner (or fixed number of owners, but if there's a case where you could have multiple owners, you can always export the CO record data, and filter people who meet your criteria instead of using SL), you can create a couple of fields on the Application object to store the owner name/ID and score. This would allow you to add constraints for the Owner's name and/or his/her score on the same Application CO itself.

[milanjain2208]

Hi @Darshil_Shah1 ,
Yes, a single CO record can have multiple Owners and the number of owners for applications are not fixed.

[SanfordWhiteman]

I’ll ask again why you didn’t set up the standard 2-level relationship with a junction object, as described above?

[milanjain2208]

Hi @SanfordWhiteman ,

Apologies, but I'm having difficulty understanding your response/suggestion clearly.

I currently have two custom objects (COs) in place: "Application" and "Owners."

My goal is to establish a one-to-many relationship, where "Application" CO connects to "Lead" in a one-to-many manner, and similarly, "Owner" CO connects to an "Application" CO in a one-to-many fashion.

In simpler terms, a single lead should be able to be associated with multiple applications, and a single application should be linkable to multiple owners.

If I interpret your suggestion correctly, you're proposing that I utilize the "Application" custom object as a bridge between "Lead" and "Owner," with "Lead" and "Owner" acting as the edge objects. However, I'm concerned that this approach might restrict my ability to assign multiple owners to a single application.

If your suggestion differs from my understanding, could you kindly guide me to the relevant documentation? Your assistance is greatly appreciated.

[SanfordWhiteman]

I was saying Owner is the junction.

Each Lead can be connected to multiple Owners.

Each Owner is connected to one Application.

Therefore each Lead is connected to multiple Applications, each connection being an Owner.

It just doesn’t make sense for you to describe the COs as interlocked somehow but then not have a relationship between them.

[harsh_rohilla]

Hello @SanfordWhiteman 

Thanks for the response. We tried your suggested approach of using owner as a junction and it worked for solving the filtering problem which we had, but now suppose we want to have another custom object called "Offer" such that "Offer" and "Application" object share one to many relationship i.e. each application can have multiple offers linked to it. 

We tried similar approach of making Offer as another junction between lead and application object but we weren't able to link it to application object through applicationID maybe because there's already owner junction which exists between Lead and Application.

Another naive solution can be incorporating Offer object fields into currently existing junction between Lead and Application i.e owner object but we aren't very keen to do that as it will spoil the way we want to model our data.

[SanfordWhiteman]

---

Similarly, the intention was for the "Owner" object to connect to the "Application" object in a one-to-many manner, signifying that a single application can have multiple owners. However, due to limitations in Marketo, which does not support a second-level custom object relationship (such as Lead <- Parent <- Child),

---

Hmm, this phrasing is misleading and I don’t want anyone reading this post to get bad info.

Marketo does support many-to-one relationships. You could have an Application → Application Xref → Lead relationship. The Application Xref is your junction object. It would allow a Lead to be connected to multiple Applications, and other Lead objects could be connected to those same Applications.

The problem with this setup isn’t on the Smart List side. It’s on the Velocity side. Velocity would not be able to read the properties of the Application via the Xref. It would essentially know that an Application exists and its GUID, but nothing else.

To fully enable this setup to work in both SLs and Velocity, you’d need to have a dictionary of all your Application objects stored somewhere else, outside of the CO relationship. For example, you could pack them all into a text {{my.token}} as a JSON array; whenever there’s a net modification to the Application list, rewrite the token (via API of course). Obviously this won’t work if you have too many distinct Applications, but such tokens can be up to 255KB (not sure if there even is a hard limit) so they can get huge.