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