Workflow pagination, headers and footers

Jonathon
Participant V

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:
2X_5_511bbde78d3fd4d048d2ffa4c88b9abb233327b7.png

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:

2X_4_4b1e36648f3c834abb6f6fca48b1caa9ca5f2211.png

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

26 42 9,802
42 REPLIES 42

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.

Jin_Tan
Participant V

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?

Doni_Nvrro
Participant II

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

Jonathon
Participant V

Resurrecting this thread to include an example of paginated reports with:

  • Page headers including a unique header on the first page
  • Footers including enumerated page numbers
  • Appendices with dividers


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:

  • Avoid using LongText datatype for columns you want to use this method of pagination for. Alternatively, convert LongText to Text datatypes with a virtual column;
  • Use oversize rows that can accommodate multiple lines in the first place, so rows with little text are treated the same as rows with two or three lines of text; and
  • Use <<IF: LENGTH([comment])>100>> or similar statements to dynamically reduce font size where comments go beyond some certain threshold of length.

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.

Doni_Nvrro
Participant II

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.

LeventK
Participant V

@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.

Bahbus
Participant V

Curiosity peaked.

Doni_Nvrro
Participant II

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

Doni_Nvrro
Participant II

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

Doni_Nvrro
Participant II

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

Top Labels in this Space