Velocity script help

[LeaCharnley]

I've been banging my head off this one for months! So any help would be gratefully received.

I've been trying to figure out the best way of recording samples requested by our customers and sending out a confirmation email containing details of what the customer has requested and where they'd like it delivered.

I have created 2 custom objects - one containing the delivery information and another to record each product requested (as there could be multiple) I've also currently got this object linking back to the person record with an intermediate (bridge) object, although I'm not entirely sure why. I therefore can't see the 'edge' object in my email script list.

Getting the customer / delivery info into the email is no issue using the email script tokens. 

How would I get the list of requested samples onto the email as there could be many products on the request. Is there a way I can put all the requested items into one field in a way that will display them nicely? Or can someone help me with velocity script where I can identify products requested against a single sample order ID. The requested products object I mentioned about also has a field for the sample order ID.

[SanfordWhiteman]

---

I've also currently got this object linking back to the person record with an intermediate (bridge) object, although I'm not entirely sure why. I therefore can't see the 'edge' object in my email script list.

---

If all else were equal, from a db perspective this is the correct schema. Products are the leaf objects, Person-Product connections are the intermediate junction objects. Works fine in Smart Lists.

But in Marketo, you can’t access the leaf objects in Velocity, so you have to use a somewhat hacky setup. You have 2 choices:

1. Copy the properties of the related product to a single JSON field (i.e. Textarea) on each junction object. You can parse and output that field any way you want using Velocity. Repetitive but it works.
2. If the list of possible products and their fields isn’t that large (ballpark, let’s say 200 products with 4 string fields each) and you have a reliable way to know when the list is updated, then put the entire product list into a standalone Velocity {{my.token}}. That becomes your “lookup table” of sorts. You read the junction object to get the product ID(s) then get their properties from the table.

Note I wouldn’t rip out the current 2-level junction-leaf schema, but rather complement it with one of the above, because the 2-level schema is still better for Smart List filters. (It’s also possible, however farfetched, that Marketo will someday let you access the leaves directly in tokens.)