Many-to-many Custom Objects in Tokens

[Anonymous]

Hi,

I have two Custom Objects defining articles I wish to email to each lead, structured as follows:

• Library: a list of articles with unique IDs
• Index: two link fields, one to Lead and one to Library

When I view the lead detail page, I can see the Library items associated with each Lead via Index. However, when I try to create a token to include the articles in an email, I can only see the Index object.

Is there a way for me to reference the Library records in the token?

Thanks in advance

James

[Stephen_Schmuhl]

I've run into this before and we were able to overcome the issue using Apex and sending the email out of Salesforce, rather than Marketo. Flattening the object into a custom object in Marketo might work as well, but if you have a good Salesforce Developer you've got another potential option.

[Alex_Firtl]

Hey Stephen, do you have any more information on how you handled this via Apex? I would be interested in learning more, in case this could help us out with a similar situation.

[Nate_Anderson]

This is pretty disappointing. So outside of the elaborate HashMap scheme you outlined above, we are forced to flatten any custom object data we'd like to inject into emails into contact-keyed structures?

[SanfordWhiteman]

Not any custom object data: 2nd-level relationships via Company/Account are fine (as are Oppties of course). 2nd-level via Lead/Contact, yes, the data needs to be flattened to the 1st level in order to be read from Velocity.

[SanfordWhiteman]

The second-level objects won't be accessible from Velocity, an unfortunate shortcoming that isn't well-publicized.

We engineer around this by replicating all the records in the auxiliary table (your Library items) into their own Velocity token as a HashMap.  Then we join the first-level objects to the HashMap. You can hold hundreds, maybe thousands, of Library records this way, though it's not unlimited.

[simonwha]

Hey Sanford,

Does that mean that the second level object are not available in the side panel of the velocity scripting tool at all and that we can only use it in the smart list filters and triggers within Smart Lists/campaigns? 

[SanfordWhiteman]

That’s right.

But even when they’re cosmetically available in the Velocity field tree (as can happen with some complex CRM-synced object schemas) they still don’t always work, you’d have to test. (In other words, Marketo can accidentally present them in the editor but they don’t work in code.)

[Anonymous]

Thanks Sanford. That's frustrating, I was hoping that this structure would help us get around the API, Batch Size, and Custom Object limits which we're currently coming up against. Back to the drawing board, I guess.

[SanfordWhiteman]

How big is your Library, how wide are the fields (data types and number of fields), and how often does it change?

[Anonymous]

Hey

There are multiple rows per lead, and each item contains ~7 fields, one of which is a lengthy text field, and two more are shorter text fields. It needs to be updated before every send. That data is often duplicated (since many people may receive the same item), so there's unfortunately a lot of duplication which leads to us hitting the limits

G

[SanfordWhiteman]

Hitting what limits?

How many rows are you talking about? With some crafty JSON you can eliminate duplicate text. Or even gzip + base64...

[Anonymous]

In the order of 6 million or so rows, so we're hitting the API request limit. Though you're right, we may be able to compress that down with some manipulation. Does Marketo accept gzip / base64 encoded payloads, or JSON referencing?

[SanfordWhiteman]

6 million articles?

Does Marketo accept gzip / base64 encoded payloads, or JSON referencing?

Velocity can read Base64'd Gzipped binary and/or text, yes.

But if you actually have 6 million articles (e.g. not junction entries), then even with compression it's probably unlikely that you could store that in Velocity (though you could use multiple VTL tokens, it's over-the-top).