PAGE BREAKS IN PDFS

[SANDRALAICHE]

How do make sure a start statement always begins on the next page?  I am okay if second page is mostly blank, but its important that next statement creates a new page for each row. See example that I have now, I need the Project Name and Jobline name to appear on same page as image:

[WillowMobileSys]

You can insert a Page Break kinto the template just like you would in a normal document.  the template will reflect the Page Break and it will appear in the generated PDF.

In your case you probably want to insert it in between the template table and the <<END>> so the first page doesn't look weird.  Note that this will result in a blank page at the end of the document.

[SANDRALAICHE]

I have another question, I am trying to have the ticket info "group by" Project name, then by jobline, then list the ticket info, I thought I had it figured out but it so happened that the invoice I was testing only had one ticket per project and jobline, so it didn't look right when there was multiple tickets per jobline.  Here is what i want it to look like:

[SANDRALAICHE]

or this

[WillowMobileSys]

You can incorporate Grouping like you have illustrated.  There is no AppSheet included function to help so you will need to build it yourself.  Not difficult to do but there some "gotcha's" to explain.

You will need to create nested START/END blocks for each level of detail you need.  There are a couple of tricks:

1) You will need to be able to implement the expressions to return a list of the Grouping items such that you can iterate over the Group list, display a Group Label and then use that value to select the list of values in the nested START/END.

2)  The column names between the outer START/END and the inner START/END must be different to prevent ambiguity.  There is no [_THISROW] type reference that allows an inner expression to explicitly reference a column from an outer expression.  The template relies solely, in this case, on context to identify columns.

Put simply, what these two items above mean is either:

• you cannot use expressions against the same datasource

OR

• you will need to duplicate values into columns with different names to avoid the ambiguity

What I typically do as a practice is create small helper tables for these grouping categories.  Then use the tables for the dropdown values when entering data rows.  I then have a different datasource I can use for Grouping but also a way to easily update the grouping list in the app, if ever needed.

Here is an example of what I mean.

Taking into account your example, I would create a table named maybe "Work Types" and list each of {"hourly work", "unloading barges", "dirt haul off", etc} as rows in this table in a column named "Work Type" along with any other supporting details that would be helpful.

Then in the Work Order table (I don't know your table name), I would maybe have the column named "Order Work Type" as a REF column to the "Work Types" table.

In the template...

You can then create your grouping like this (this is all fictional. you will need to change the details to fit your data) :

<<START: Work Types[Work Type]>>

<<[Work Type]>>
    <<START:Work Orders[ID], [Order Work Type] = [Work Type]>>

          ...

    <<END>>
<<END>>

Note that [Work Type] is first being used to print out the Group Heading then used to filter the Work Orders table.

I hope this helps!

[SkrOYC]

I'd love to help you make all of this, it's not that hard and I've written some posts about using HTML tables where page breaks and more is supported easily.

About the "Group by" kind of behaviour, take a look at this comment by @Steve:
Re: Single category headers in workflow templates ... - Google Cloud Community

You don't need to create any table, the idea is quite ingenious and I use it extensively after his suggestion in another post.
Here is an explanation of why it works:
You take any row from the current table that just serves the purpose to make Start: add n number of rows used as categories, basically if you use MINROW or MAXROW doesn't matter, because we need exactly one from all of the categories you need. This can be the [Project name] column in your case. Then you add another Start: to just return the rows that has the same [Project name] as the row taken from the outer Start:

You can do this a lot of times to keep nesting, just don't forget that each time you go to another nested Start: you need to query [_THISROW-N].[Category] where N is how deep the Start: is related to the first one.

Please, read this description as much times as you need to get it. As an example:
If you have a table ("person") like this one:

id_person	name	gender	age	age_range
blabla11	One	Male	23	20+
blabla12	Two	Male	28	20+
blabla13	Three	Female	24	20+
blabla14	Four	Female	30	30+
blabla15	Five	Male	35	30+
blabla16	Six	Female	39	30+

You can display that data grouping by gender and then age_range for example, doing something like this inside your template:

---

<<Start:FILTER(
 "person",
 [id_person]=MAXROW(
 "person",
 "_RowNumber",
 [gender]=[_THISROW-1].[gender]
 )
)>><<[gender]>>

This would return "blabla15" and "blabla16", because are the maximum rows based on rownumber (the latest ones) that match the criteria. Since it's evaluated for blabla11, blabla12, blabla13 and so on, the first gender taken is Male and the latest Male was blabla15 so it is not blabla11, the formula keeps working with the next until it finds the latest row with that specific gender.

<<Start:FILTER(
 "person",
 [id_person]=MAXROW(
 "person",
 "_RowNumber",
 AND(
   [gender]=[_THISROW-2].[gender],
   [age_range]=[_THISROW-1].[age_range]
  )
 )
)>>

<<[age_range]>>

This would return "blabla13" and blabla16" for the "Female" category and "blabla12" and "blabla15" for the "Male" category, the latest that comply with both conditions

Name	Exact age
<<Start:FILTER(  "person",  AND(    [gender]=[_THISROW-2].[gender],    [age_range]=[_THISROW-1].[age_range]  ) ) >><<[name]>>	<<age>> <<End>>

<<End>>
<<End>>

---

The result would be:

Male

20+

Name	Exact age
One	23
Two	28

30+

Name	Exact age
Five	35

Female

20+

Name	Exact age
Three	24

30+

Name	Exact age
Four	30
Six	39

---

I know that this was a little too much but I hope it also helps other
BTW: Always test the expressions, I can make mistakes as anyone else

[Juanjoge]

Hi, I managed to do the division following your recommendations but I am stuck on two issues
1.- Getting the sum of each group of data. I am not sure what I am doing wrong 😖, but it does not sum what it has to sum. this statement is the one I put in the group total <<SUM( SELECT(Notes[Total service],[Payment_type]=[_THISROW-1].[Payment_type]))>>
 🤔

I attach how my report is conformed for better understanding of what I am doing.

[SkrOYC]

I suggest you to use the same expression as your Start: but with a twist, change it to SELECT(Notas[Total servicio]) instead of FILTER("Notas"), ORDERBY() is not needed. But the filter condition should be exactly the same as your Start:

What's the second issue? I'm guessing it's related to having the table cutted in half, isn't it? I don't think you will be able to modify any behaviour related to that without using an advance template like HTML/CSS

[Juanjoge]

let me try it!!! This will help me to get the subtotal I am looking for, because this is for obtaining the data from the table, isn't it?

The second I erase because was how to filter by date but I was able to implement that part, the order by statement I put it to present the invoices in order by their number. Many thanks!!!

[SANDRALAICHE]

This looks exactly, what I need, one question, can the table in the filtered expression be the [Related TICKETs], or should I just add the additional condition in first filter statement to only select the [TICKET_ID] that are in the [Related TICKETs] ?? The information I need to "group" is all in the "Ticket" Table, but I only need the rows that are related to the current invoice I am generating.

[SkrOYC]

Without knowing more about your schema, you can replace FILTER("NameOfTable" with START([Related NameOfTables][id_column]

[SANDRALAICHE]

What about the MAXROW expression?

[SANDRALAICHE]

Thank you so much, with your explanation, I was finally able to interpret  @Steve:
Re: Single category headers in workflow templates ... - Google Cloud Community, and I have finally figured it out!!

[SANDRALAICHE]

OMG, I spoke too soon, can you take a look at this and tell me what I am doing wrong, its so close, but yet so far away, lol:

[SkrOYC · Accepted solution]

Since it's already inside another Start

[SANDRALAICHE]

THANKS!!!