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

[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.

[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).

[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.)