Formatting Workflow Template

Thanks in advance for any help,
I have been working on this problem for a while and cannot seem to find a solution. I have an email template workflow that sends a report from my “Query_Data” table. In my template, I use this formula <<START:ORDERBY(SELECT(Query_Data[_ComputedKey],AND([Year]=[_THISROW].[Year],[Month]=[_THISROW].[Month],[Day]=[_THISROW].[Day],[company]=[_THISROW].[Company],[farm]=[_THISROW].[Farm],[crop]=[_THISROW].[Crop],[Field Type]=[_THISROW].[Field Type])),[crop],true)> I would like to switch the rows and columns, the same way you would use TRANSPOSE in sheets. In the format I have now, I have to use multiple tables to fit all the columns and it causes the PDF to be multiple pages and difficult to scroll.This is how I have it set up now:

I need it to look more like this:
I am also open to any other suggestions on how to organize this data to make it readable and condensed. I hope I explained this well enough…

You can create a vertical layout. To do so you would need to create a series of table “boxes” to house the static row and column header info and another set for the dynamically changing data. The key is to place the <<Start: >> + <<End>> statements OUTSIDE of the dynamic table they are operating on

Below is a very small example. It’s actually is showing a side-by-side column example but also can serve the purpose for your use case. Yours will obviously be a lot more sophisticated!!

Your sample indicates you will need 7 dynamic columns (14th, 15th, 16th, etc) which means 7 separate <<Start: >> + <<End>> statements one for each vertical column.

Note: I have only created this as a proof of concept. I am not sure how the template tables will behave when they cross page breaks. I would recommend testing the concept with just a single column of data first and if all goes well then expand.

You probably have questions, so please ask.

Sample from Template

Sample result from Report


John, Thank you for such a detailed answer and yes I have questions. I understand creating the tables and surrounding them with the Start- End for the rows, but is there a way to have the number of columns dynamic. The name and number of “Blocks” in my example will not be same depending on the client and that weeks visit. Thanks again for the help.

I cannot think of a way to make the number of columns dynamic inside of a template, other than if a SELECT() inside of the Start/End returned no rows. But that would just make the column empty. I don’t know your data, maybe there is a way to organize the columns so that any empty columns are always at the end?

Another thought is if you can detect how many columns you’ll need BEFORE generating the report. Let’s say the max possible is 7 columns, you could produce 7 templates - 1 column, 2 columns, 3 columns and so on. Then have a Workflow for each and only trigger the Workflow that generates the report with the desired number of columns.

1 Like