Columns on report template

Hello, I have a report template that I need to save as PDF with 2 columns after the title.
I’m using MS Word and tried to just click columns in the ribbon of the template document but no luck.
here’s what I have so far:

                        ` <<TODAY()>>`
                 Pelham Thursday Commissary Order
                        Friday Delivery

<<Start: Select(Thursday[_RowNumber], AND(ISNOTBLANK([Order]), [Order] > 0))>>
<<[Product]>>: <<[Order]>>
<<End>>

Example needed:
Product 1 : Order Product 3 : Order
Product 2 : Order Product 4 : Order

I’m not sure what this means and how it relates to AppSheet?

As for the template, it seems to look good except you don’t need the tick marks around the TODAY() function. It should just be <<TODAY()>>. Maybe that was just how you typed it into the post?

If you are trying to run this from a Workflow and its not generating, try looking at the Log entries for any errors. You can get the Log by expanding the Workflow and clicking on the Log button. Then scroll through the entries to find where your Workflow tried to execute. If there was an error, it will be colored in red. Click on the binoculars to see details.

1 Like

Workflow is working correctly with no errors.
The issue is with the layout of the template.
I have 56 products with order quantities. So the saved PDF is 2 pages long. I get 36 products on 1st page and 20 products on 2nd page. I would like to condense the saved PDF report to only one page with 2 columns. Without changing the font size to super small.

Got it! I know this has come up before and I’m pretty sure the answer was it couldn’t be done without some kind of data manipulation.

For example, introducing a table where the rows are simply used to help organize the report. In your case, the rows could have columns such as “Product A”, “Order A”, “Product B”, “Order B”. Then you can write two items with a single row.

Its cheesy I know. The issue is getting the template to process blocks (i.e. Start/End blocks) vertically rather than horizontally. Not to many vendors do it because of its complexity.

This was bothering me so I decided to play around a bit with templates in Google Docs.

Google Docs do have the concept of text columns but I haven’t figured out yet if I can get a list in a Start/End block to spread across the columns - like normal typing does.

However, I have successfully created a two column table and was able to use two separate Start/End blocks - one for each side of the table. Below is a sample of the template and sample result in a report.

The thought here is that maybe you can break your Start/End rows into sections - one for rows 30 and less, a second for rows 31 to x, a third for rows x+1 to y, etc. Not ideal but the end report would be nice.

Sample from Template

Sample result from Report

2 Likes

As @WillowMobileSystems described, yes you can use nested tables with gDoc. You can write your Start: expression like <<Start: SELECT(Table[KeyColumn],MOD([NumberColumn],2)=1)>>. I have used this method and it works just fine.

2 Likes

I suggest it is prudent that this tips should be documented somewhere in Appsheet knowledge base and/or support page.

Thanks for expression. Just to clarify, is [NumberColumn] a sequentially numbered column?

Also, the ideal would be NOT to have to break up the list into table at all. Google docs do support up to 3 text columns. So, for everyones understanding, if I define two columns and type into the left hand side/first column, entering lines of text, once at the end of the page the text entry will automatically shift to the top of the second column. Continuing entering lines of text and at the end of the second column, text entry will shift to the second PAGE, first column. (see sample image below)

Do you know why when a doc is generated from a template in AppSheet, it doesn’t honor the document defined columns? Is it removed somehow during preprocessing?

Sample of 2 column doc after entering 2 columns fist page and moved to second page

Yes, I had planned to add a Tip article once I have all the ducks in a row! :slight_smile:

2 Likes

Well, it doesn’t need to be sequential number… but if you have just numbers like 1,3,5,9,13,17 etc, it will add values only to left column. Though _RowNumber is one option.

@WillowMobileSystems
@Suvrutt_Gurjar

Now we found a way to dynamically to place the sequential number to the LIST. See the post above.

Based on the tricks and tips here, we are now able to create the multi column email template.
I m wondering now we should be able to create the two (or three or more?) column template out of SINGLE Table.
I m not testing this idea yet, not sure if it would work or not.

I received some claims and request from my client to split the list on the template into two column.
For instance, I do have table contains bunch of images. Now what i can do is to render each image one by one to row in table style in template. This sometime kills the space of the final output PDF, so it is quite understandable that my users of the app wish to see the table with two column style.

Using the trick i leaned from @Suvrutt_Gurjar, we are now able to split the single list or table or slice into two. For the select expression, just place the expression to differentiate the sequential number which is dynamically created by the expression and then assess if the value is even or odd.
If off, then place to the right handsite of the email template. IF not, place to the left (second column).

When i could get a chance and time, i will test this idea, to see if it would work, i.e to display the single list and split into two to get the two column style output on the template/output.

3 Likes

I don’t want to stray to far off the original poster’s topic.

@billjohnson Is any of what has been discussed in this thread helping you at all?

Apologies. I’ve been out of the office over the weekend. I will test first thing Monday. Thanks for all the input.

I have finally had time to circle back on this. I could not get it to work.
using: <<Start: SELECT(Table[KeyColumn],MOD([NumberColumn],2)=1)>>
I’m OK with introducing table… actually would prefer it…as John Baer suggested but not sure how.
I’m using MS Excel not google sheets
If I could could split all products in the appsheet column into subgroups i.e. Dairy, Poultry, Beef, Seafood, Produce,…
In Sample template: John has [Related Products] and [Related Materials]. How do I split my Products into these type of groups?

Just want to be clear what you have tried. The expression above was an example with the intention to replace the Table and Column names with those pertinent to your use case. I’ll presume you knew that. It might help to show what your expression was you tried so we can look for any syntax or logic errors.

The table I suggested to help with reporting order, was meant to be a last resort as it would take a bit of coding to pull off. Since that idea, there have been other simpler methods suggested/discovered. In the end, it is what you are most comfortable with.

Creating Child Lists

In my examples, “Related Products” and “Related Materials” are lists of children records to the parent “Orders”. So I have tables “Orders”, “Products” and “Materials”. To create the parent/child relationship, I would add a parent id column in the child tables. In this case, I add the column id “Order ID” in both “Products” and “Materials” tables. In the editor I mark those columns as Ref type and go into the column and “turn on” the “Is part of” property. This links the table in a parent/child relationship AND AppSheet automatically creates Virtual columns for the child lists on the parent record. So, on my “Orders” table I was automatically given the “Related Products” and the “Related Materials” Virtual columns.

You can read more about this relationship in this article (scroll down to " Expressing Ownership Between Tables"):

1 Like