Duplicate Rows based on Column in PDF Generation

AppSheet AppSheet Q&A
,
1

Hello, I have a slice and automation that I’m using to generate PDF. Wondering if there is any way to duplicate a row based on column data. For example, if there is the text “AA” in column “Type”, I would like that row to be duplicated in the PDF (but duplicate rows in the actual database/backend is unnecessary).

Slice shows:

Date Type Description
1/1/2026 AA Red Apples
2/1/2026 BB Yellow Bananas
3/1/2026 CC Orange Cantaloupes
4/1/2026 AA Green Grapes

Want the PDF to generate:

Date Type Description
1/1/2026 AA Red Apples
1/1/2026 AA Red Apples
2/1/2026 BB Yellow Bananas
3/1/2026 CC Orange Cantaloupes
4/1/2026 AA Green Grapes
4/1/2026 AA Green Grapes

This is the current expression I have in the PDF template which works fine for listing the related products, but I’m wondering if an IF() statement embedded along with the SELECT() will “break” the way the expression runs.

<<Start: SELECT([Related Products][Row ID], AND(Date([Date & Time]) >= [_THISROW].[Invoice Start Date], Date([Date & Time]) <= [_THISROW].[Invoice End Date]))>><<TEXT([Date & Time],”MM/DD”)>>

And in case the rows cannot be duplicated during PDF generation, what other ways would you suggest for the PDF to produce such?

Appreciate your replies.

1 Like
2

I would argue that if its a requirement to have two rows displayed then it IS necessary to have two rows representing that display in the data. It certainly would make your use cases easier. And there should not be any concern having the extra rows in the data. But we don’t know your use case nor why you want to do this.

So….

You can use IF statements in templates to control the flow. You can get more details at Use If expressions in templates

Basically, you would do something like this:

{{{ Insert table column headers here }}}
<<START….>>
{{{ insert data row here }}}
<<IF [Type] = "AA">>
{{{ insert duplicate data row here }}}
<<ENDIF>>
<<END>>

CAUTION: because this breaks the table into three separate tables, you need to be very diligent about formatting the column widths and the tables themselves precisely the same for each piece. Otherwise, you will end up with mis-aligned columns.

For a more elegant way of producing reports, you might want to learn HTML formatting techniques. In this post there is a reference to 3 Tip articles that give step by step details on how to go about producing such reports. There is a learning curve - one which I have yet to take.

I hope this helps!

3 Likes
3

@WillowMobileSys has given a practical guidance.

Just as an alternative, based on your stated requirement, you may want to try the following << START >> expression variation.

<< Start: 

ORDERBY(

SELECT([Related Products][Row ID], AND(Date([Date & Time]) >= [_THISROW].[Invoice Start Date], Date([Date & Time]) <= [_THISROW].[Invoice End Date])) +  

SELECT([Related Products][Row ID], AND(Date([Date & Time]) >= [_THISROW].[Invoice Start Date], Date([Date & Time]) <= [_THISROW].[Invoice End Date], [Type]="AA"))

, [Type], FALSE) >>


<<TEXT([Date & Time],”MM/DD”)>>

Basically the second SELECT() expression adds the records where [Type] =“AA” to the existing SELECT() statement and then ORDERBY() sorts those records keys by the column [Type] in ascending order.

Not tested, so there could be a few syntactical errors in the suggested expression but should generally work.

5 Likes
4

I have used Suvrutt’s approach here and there, and I can confirm it works.

4 Likes
5

@Suvrutt_Gurjar @AleksiAlkio

Very nice gentlemen! I would have never thought of duplicating the source rows first. It is definitely a better approach to ensure the table rows in the PDF stay properly aligned!!

2 Likes
6

@WillowMobileSys @Suvrutt_Gurjar Your replies are much appreciated! Reason duplicate rows are unnecessary in the database is because internal users know via code column that that row is treated twice, and it was done intentionally so we can limit the number of cells/rows used in the database. The PDF, on the other hand, goes to the customer who visually needs both rows laid out.

3 Likes
7

As we know, there are almost every time more than just one solution :smiley:

3 Likes
8

This should not be a concern these days.

I mentioned it only because I have seen these kind of shortcuts taken many times in my career. Almost always they end up eventually including the representative rows into the data because at some point a differentiating detail is required to be added that makes the duplicate rows unique. Sometimes the work to undo the “virtual” handling of the data becomes cumbersome depending on how deep into the system it is perpetuated.

1 Like
9

unfortunately, it still is a concern. the app has 90+ columns and consistently maintains 70-80K rows which google sheets doesn’t handle very well. it was a choice, not a shortcut, that we made after considering a lot of factors that are unexplained in my original post.

1 Like
10

Understood.

For what it’s worth, with proper data design, AppSheet can easily handle that data size. I regularly build apps with data 6-8 times that size using Google Sheets. If you find performance lagging, reach out to someone for help. That’s why many of us are here in the community!

1 Like
12

Nice try!! :innocent: That limit is across a single file. Apps can source tables from several different Google Sheet files - which I regularly do for larger apps or app suites.

I rarely allow tables to exceed 40-50 columns and try really hard to keep them under 30. Once they reach above this size, there typically is a group of columns that can be broken out into their own table. Leaving them larger many times results in sets of columns being left blank for certain row types. The more blank columns, the less ACTUAL data that can be stored in the sheet and the slower the table loads.

Keeping column sizes small and tables well-defined is key to excellent performance. AppSheet utilizes parallel processing to load tables. Rows can be easily filtered, purged or archived to aid in increasing performance. Large column counts limit what can be done to increase performance.

1 Like
13

Yes, I know that. I’m not here to compete with you. I was genuinely curious. Your apps may be ok to have several separated workbooks with 30 columns, but sometimes it’s better to have one due to the nature of your work/field. I didn’t design my app with 90 columns without knowledge that lesser columns run more efficiently. You may have more experience making apps or you may not, but either way you shouldn’t presume that someone hasn’t thought through their own app design when you haven’t looked under the hood or have any idea what the app is for/what it does.

14

Understood. I was trying to be light-hearted which Is why I added the emoji.

And you’re right, I don’t know your app and I should not have offered my unsolicited experience. I will refrain from doing so in the future. Good luck with your app!!

1 Like
15

Whew! You haven’t participated in this community very long, have you? 95% of the posts here are by app creators who barely understand AppSheet, let alone “app design”. You would do well to get to know your audience before admonishing the senior members. Good luck, pal.

2 Likes
16

@WillowMobileSys Appreciate your understanding. @Steve Perhaps being a senior member made you forget that the other 5% in this community may also want to reach out for thoughts without having to go through an appsheet-101 convo. Good luck to you too.

17

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.