Getting relation items from another sheet as values for web hook

I have a sheet which effectively is for quotations. Each row is a quotation with columns such as:
Row ID (unique)
Quote Items (Relation - Multiple)
Total (Rollup of ‘Quote Items’ prices)

When I try and send the data to a webhook to create a PDF, I’m able to add the details for all the columns including rollup fields, but I need to be able to grab the related Quote items as these will form the items of the quote. It doesn’t look like you can access the related items to send to the service via the webhook. Am I missing something, or is there some other means to get these items from the related sheet, as this is required to form the quotation items list of the PDF.

Many thanks

A relation is a connection to related rows. It doesn’t hold a value. It just provides a link to those related rows.

You can use a Lookup column to retrieve a specific column from a relation. But, if your relation is a multiple relation then the lookup will return an array. Arrays cannot be placed in single value placeholders. The alternative is to use a Joined List column, which will retrieve the entire contents of a column in a relation, but as a delimited single value string instead of an array.

If your relation is a single relation, then a lookup will return a column value as a single value as opposed to an array.

Based on your description, I would recommend using a Joined List column to retrieve multiple values from a column in your relation.


Thank you for your response Jeff.

That’s exactly what I’ve done actually.

They are multiple relationship columns, so you’ll see that I have used join list columns to create csv’s of the related data. But the issue I then have is that PDF monkey requires a json array for the items that need to be iterated. So I need to somehow now merge these join columns into an array so that it ends up something like:

[‘Organic Hoodie’, 25.00, ‘AM01’],
[‘Pro Polo’, 12.00, ‘RX101’]


Thanks for your time and help with this :slight_smile:

You’ll need to use a combination of template columns.

First, create a single-quote-wrapped version of each value using a template column. Then, your join list should point to that template column. Then wrap each of those join lists with a template column. Finally, wrap those template columns with another template column to get the formatting you need.

Yay No Code :wink:


:joy::joy: that made me laugh! When no code hits it’s limits!! Hilarious.
More complex than coding my needs.

But if that’s the way it is, that’s the way it is. Thanks Robert. That makes sense. I’ll try it and report back. :grin: :+1:

1 Like

Yeah, nah, That’s gone completely over my head. I have lost all sense of sanity. Could you possibly break that down into baby steps for me? Sorry. Haha.

1 Like
  • Create a template column that looks like: ['{name}','{price}','{code}']
  • Use replacements for each of {name}/{price}/{code} to include the actual column values in each row
  • Take a joined list of that template column, using the default comma separator
  • Finally, wrap that joined list in another template column: {{joined-list}}, where {joined-list} gets replaced
1 Like

The only problem with that is it joins all of the names first in the array then the prices, then the codes.
So the items info isn’t grouped together. Unless I’m missing something?

{[‘Organic Hoodie’, ‘Pro Polo’], [25, 12], [‘AM01’, ‘RX101’]}

No, no… the first template column should be created in the table that contains the source data. I wasn’t suggesting that you use your existing joined list columns in the template. You don’t need those.


AHHHHHH! That makes sense!!! Thank you. God, I’m having a right moment. Wicked, I’ll try this.

Thank you again

Thanks @Darren_Murphy for the step-by-step … I’m on vacay all this week and typing all that out on my phone would’ve been tedious :wink:


I thank you both. Will report back. Your time is very much appreciated

Teamwork :laughing:

1 Like

This did the trick perfectly thank you again both!! Pair of legends.

Here’s another related question if I could trouble you both for your expertise.

Once I’ve created this template for the related items in the related sheet is there a way in the ‘parent’ sheet to iterate over the items in that template column and if there are two with the same name, code and price to merge or omit duplicates whilst incrementing a number. This would be so that I could have a quantity field and instead of listing each item like so (this might explain it better):

Item Name | SKU | Price | Quantity
Anthem Hoodie | AM01 | 25 | 1
Anthem Hoodie | AM01 | 25 | 1
Anthem Hoodie | AM01 | 25 | 1

It would display as:
Item Name | SKU | Price | Quantity
Anthem Hoodie | AM01 | 25 | 3


Yes, possible.

Just thinking out aloud here, so I may not have the following exactly right. But anyway, you could probably do something like this:

  • Firstly, add a RowID column to your parent table if you don’t already have one.
  • Create a template column similar to that you’ve shown above, ie. {item-name}|{sku}|{price} (leave the quantity off)
  • Now create a multi-relation that links that column to itself.
  • Next, a rollup through that relation that returns a count. This will give you a count for each unique combination of Item/SKU/Price, which you can use in your row template.
  • Now comes the tricky part…
  • Create a Single Value column that takes the first RowID from the above relation column
  • Now create an if-then-else column:
    – If Single Value RowID is actual RowID, then return your template column that includes the quantity

You can then use that if-then-else column in your joined list that feeds the template to create your JSON object.

I haven’t tested the above, but I’m pretty sure it should do the trick for you.