I have a sheet which effectively is for quotations. Each row is a quotation with columns such as:
Row ID (unique)
Company
Description
Quote Items (Relation - Multiple)
Date
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.
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.
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:
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.
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.
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?
e.g.
{[‘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.
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):
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.
