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.
The first step is to create a basic template, complete with pseudo headers and footers:
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:
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:
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
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โฆ
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.
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.
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
Resurrecting this thread to include an example of paginated reports with:
As a note - I wish we could be more explicit with our image dimensions and/or orientation for reports.
This look so nice. Thank you for sharing this. It shows what can be done when you mix AppSheet with a smart @Jonathon
@Jonathon: as always, the report example you have made is so creative - it demonstrates the full range of reports pagination possibilities. Thank you very much.
Heres a quick sample application with the basic idea for the community to reference:
Here is a link to the workflow template:
You can edit the scheduled report (set it to email you) and mess around with it.
Thank you very much! Appreciate this a lot.
Great job, i save it for furthers projects, but itโs not valid for my project that i need to save invoices containing many relations tables to the invoice table (table invoice lines with long text referenced to invoice, table list items with long text referenced to invoice lines and table hours referenced to invoice lines)
itโs not possible to calculate the number of lines to be listed
Hey Doni;
You are right, this method doesnโt work with inconsistent row heights. In most cases I have found ways to work around the problem of row height. Some examples:
Hopefully this can help you!
Good resurrection. Very good post, will bookmark for later!
Hi,
Iโm just new here in appsheet, is there a sample app for this? Iโm a bit slow understanding when it comes to SQL, as Iโm only using onedrive/google drive databases.
Appreciate anyoneโs response.
Regards,
Ed
Iโll throw together an example app this morning.
Oh Thank you very much! Deeply appreciated.
Hi Jonathon, at the end i solved it creating the reports using html template where i can control and repeat headers and footers in report print
If you like to see how i did it, i can send you a copy of my html template
Thank for all and regards
Toni Conde
Sure Doni - if youโve found a way to solve the issue of variable row heights, iโd be super curious!
Was this done natively with AppSheet?
Same here!
HTML sure allow for some pretty cool things that can often be challenging to print. So I would be interested as well!
Me too please
I would like to see how you did your HTML template as well. Thanks.
@Doni_Nvrro
Provided you can share an open source for your solution under this post, community members might get most benefit out of it. Thanks for sharing.
Curiosity peaked.
yes, i did it with Appsheet and HTML styles. In screen view you can see all records in list with a header and a footer, and in print option repeat the header and footer in every page. it colud not be perfectly depurated, but solved my problem in reports
https://drive.google.com/drive/folders/1HEum-WGRNjRxkMytIllD72c0GnkBpri5?usp=sharing
I upload the template html, css style and header and footer image.
In the template you can see
<<Start: [ID Factura].[Detalle Factura]>>
[Detalle Factura] was a related table of table workflow
[Horas Albaranes] and [Horas Materiales] are related tables of [Detalle Factura]
In expression <<IF(ISNOTBLANK([Cantidad]),[Total],"")>> you can se how i put the formulas in html template. all caracters must be converted to html
< > ( ) " " or caracters like รณ รฉ รญ
You must create a workflow using HTML
Goog Luck
And the output of this is a .pdf, or is it a .html page?
If its possible to style my documents directly with HTML / CSS and output them to .pdf this is a game changer.
How are you uploading the css style and header/footer? Can you explain the steps, please.
I not uploaf to appsheet, i use them from a webserver
Chek de files from the example and you can see in the templare how i use in the htm files
Ask me if you need more help
Regards
iโm not an expert in html, but maybe here could help us to convert html files in pdf directly.
We can do it !!
I think this could be an option:
Save the HTML to Gdrive and send the URL to a microservice, https://github.com/esbenp/pdf-bot, running as a cloud function or on a server
Is there a way for the table to add columns as required just like you have it there for rows? Iโve had it working for rows before but am now stuck as I need to work for columns. See image below
You can create table and columns using << Start >> expression in the table definition
< table >
< tr >
< th > Firstname < /th >
< th > Lastname < /th >
< th > Age < /th >
< /tr >
< /table >
< table >
< tr >
<<Start: โSELECT / FILTER / REF COLUMNโ >>
< th > [Column Name] < /th >
<< End >>
< /tr >
<< Start: โDATA CONTENTโ >>
< tr >
< td > [Data Content].[Value] < /td >
< /tr >
<< End >>
< /table >
You must to encode the FILTER or SELECT formula to HTML
awesome Work Jonathon
Regards
Excellent, thanks for sharing.
There goes my weekend! LOL
Great resource and technique.
But now Google own Appsheet, these essential PDF requirements should be built-in to the platform!
Or somehow, let developers easily intergrate with something like PRIBCE Html
I think you meant
https://www.princexml.com/
Yes, if course.
Typos on my part