Workflow pagination, headers and footers

Hey team,

As of now, AppSheet does not officially support pagination for workflow pdf reports. This means that there is no native ability to include headers and footers, to number pages, or to format tables neatly that may run across multiple sheets.

I spent some time this weekend setting up a system for workflow report pagination for one of my applications and figured I could pass on what I’ve learnt… Note that I have done all my testing with word document templates; I have not tested this with Google Docs. In theory, it should function the same.


Template setup

The first step is to create a basic template, complete with pseudo headers and footers:

  • Set the documents top and bottom margins to 0, and the built-in header and footer heights to 0. This helps us better layout our page
  • Add a 1x1 table with space for a ‘header’ above, and space for a ‘footer’ at the bottom.

The table should look something like this:
image

Note that these headers and footers are really just plain text on the page - not the MS Word inserted headers and footers…

You should mess around with the spacing and dimensions of these elements, until they fit neatly on a page when printed by AppSheet. I used a dummy app and some junk data to run reports until I was happy with how it looked.

Once you have this finished, you can nest additional tables and information within the 1x1 table the same way you would have built workflow templates in the past. The 1x1 table acts to keep the header and footer at the top and bottom of the page. At this point, you will need to determine the maximum number of rows that fit neatly within your report template. This number will be used when populating the pages.

An example template using this method could look like this:

Methodology

You will need to create a readonly ‘helper table’, which consists of a single column of integers. I’m using a SQL server, so I have created a table of tinyints from 0-255, which i’ve called common.tinyint. Admittedly, numbers from 0-255 are overkill for this use-case… You really only need to go as high as the maximum number of pages you would expect to be printing.

As a sidenote, there are tons of uses for helper tables like these in the AppSheet environment. I use a variety of them across many applications, ranging from number ranges to date tables. Helper tables probably warrant their own topic!

In the place of your header, create a <<START:>> expression that does the following:

  • COUNTS() the total number of rows you are wanting to output, based upon the SELECT() statement or equivalent list generating statement you are going to use
  • DIVIDES that number by the maximum number of rows that fit neatly on a page (you determined this earlier). This value is the number of pages you need.
  • SELECT() from your integer helper table all values less than or equal to this.

An expression to print out 20 rows per page would look something like this:

<<START:SELECT(common.tinyint[num],[num]<=(COUNT(SELECT(table[id],CONDITIONS)))/20))>>

In the place of your footer, place a page number expression and the <<End>> statement like this:

Page <<[num]+1>><<End>>

Within the body of your report, your SELECT() statements must bring in 20 rows, and subtract the previous pages rows. An example formula for this looks like:

<<Start:
  TOP(ORDERBY(SELECT(table[id], CONDITIONS,[ORDERCOLUMN]), 20*([num]+1))
  -
  TOP(ORDERBY(SELECT(table[id], CONDITIONS,[ORDERCOLUMN]), 20*[num])
>>

And there are all the pieces. Here is what this template would look like:

image

Once you have verified everything is working as intended, you can turn off the table borders or change their colors to white. You should now have pagination in AppSheet!


There are other ways to get pagination, such as using <<IF>> statements to determine if new pages are required, and inserting page breaks. This method requires your document template to have n number of sheets pre-created, so it ends up being pretty unweildly. I find the helper-table method described above is the easiest to set up and requires the least work to maintain / update when changes are needed.

I hope this post is clear enough for everyone to understand!

Cheers,
Jon

10 Likes

I like it!
Good method to get it done!
Now you’ve got me baited, let’s hear about some of the ways you use helper tables… I have one I use so that I can have dates automatically REF for use in dynamic dashboards…
So the helper table is just dates… And then the task table has completing date field that is automatic, but it’s REF back to that date table…

2 Likes

You nailed it with your dates table example - most of the value comes from using them as REFs for common elements like dates, to take advantage of the REF column type without requiring users/API to create the REF rows.

Another use case for ‘helper’ tables is to mimick simple looping functions within appsheet. Particularly with dashboards, you can use a helper table to automatically group by a dynamic number of bins and run aggregate calculations. Combined with SVG charts this becomes quite powerful, but could also work well with the native charts if the bins could function as REFs.

You can use 1x1 ‘helper’ tables to create detail view dashboards, which are much more mobile friendly than the native dashboards. You can perform more complex calculations, if needed, on these dashboards as the calculations only need to run for a single row.

5 Likes

Hi @Jonathon, Thank you for sharing your knowledge with us.

I am trying to make pagination for my workflow. It works perfectly when i have 20 rows of data. However, the page number will show in the middle of the page when i only have 10 rows of data.

My question is: How do i make sure the page number is always at the bottom of the page? I did follow the instruction that you share or did i miss out anything that can fix the page number at the bottom of the page?

Thank you

Your page number should be outside the table, and your table should be fixed-height so that it does not change based upon the rows of data fetched.

5 Likes

Hi @Jonathon,

There might be a little mistake in this formula. Correct me if my logic were wrong.

<<START:SELECT(common.tinyint[num],[num]<=(COUNT(SELECT(table[id],CONDITIONS)))/20))>>

The above formula will generate 2 pages when the table has 20 rows.
As the [num] will have 2 values which are 0 and 1, so the total number of pages will be 2.

Instead of using [num]<=(COUNT(SELECT(table[id],CONDITIONS)))/20))>>,
It is better to use [num]<(COUNT(SELECT(table[id],CONDITIONS)))/20))>>.

Isn’t it?

Great jog, but what about if i can’t determinate how many lines can i put in a page ? because this lines are coments about products and i don’t know how many lines have each one.

Thanks

You could use a monospace font in your template. with that you could calculate how many lines will a comment need. It’s not 100% accurate but better than a proportional font.
Instead of LongText you could use Text column Type. With that you don’t have to handle Newlines.
You will have fun finding a solution :wink:

1 Like