LinkedIn
Twitter
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:
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:
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
