How to remove empty rows in filtered pdf report.

[Alexey1]

Hi, there

I'm trying to find a way to remove empty rows from my pdf report, I have several projects that use the same data spreadsheet. When I filter the data to create a PDF report, it keeps empty rows from other projects. Can anyone help me with the correct expression that removes all those rows from the report, and keep the report clear and a nice view of the report that shows only filtered data?

P.S. I reckon when you create a built-in CSV file it works perfectly, possible to see to get the expression for the built-in CSV file?

Thanks in advance 🙏

[Joseph_Seddik · Accepted solution]

Sorry to jump in 🙂 

Your SELECT expression is forcing the selection of all rows empty or not. Instead you should write this:

SELECT( Expense[Key],
   AND( ISNOTBLANK([Project Name]), [Project Name]=[_THISROW].[Project Name] )
)

And I echo the words of @dbaum, you really should eliminate the virtual column and put the expression in the template directly.

[dbaum]

It might be possible to suppress empty columns in a Google Docs template using complex If expressions. More likely, you need to use an HTML template.

It's definitely possible to suppress empty rows by using the right Start expression.

[Alexey1]

Thanks, Dbaum

I do use Start expressions, but the problem is that expression puls data depends on spreadsheet column structure when the empty cells are actually data related to a second project, and it appears as an empty cell for the second project. My main goal is to be able to generate a PDF report Include receipt pictures as you can see on the screenshot, so HTML probably won't work well for me. 

I will be super appreciated. If you can give me an example, of which and how I can use the IF expression In my case?

Sincerely

[dbaum]

---

@Alexey1 wrote:

the problem is that expression puls data depends on spreadsheet column structure when the empty cells are actually data related to a second project, and it appears as an empty cell for the second project

---

I don't understand--in particular whether what you're describing results in empty rows or empty columns.

---

@Alexey1 wrote:

My main goal is to be able to generate a PDF report Include receipt pictures as you can see on the screenshot, so HTML probably won't work well for me.

---

As explained in the tip series I linked to, using HTML to create a template is not incompatible with an automation task that generates a PDF file comprising images.

---

@Alexey1 wrote:

an example, of which and how I can use the IF expression In my case

---

As I noted, if using a Google Doc template it would certainly be complex to account for varying numbers of columns. Here's one brute-force technique to experiment with.

<<If: expression that returns true if only columns A and B are required>>

Column A	Column B
<<Start: expression that returns row keys>><<[Column A]>>	<<[Column B]>><<End>>

<<EndIf>><<If: expression that returns true if only columns A, B, and C are required>>

Column A	Column B	Column C
<<Start: expression that returns row keys>><<[Column A]>>	<<[Column B]>>	<<[Column C]>><<End>>

<<EndIf>>

[Alexey1]

My apologies, I lost it with the translation. 

I don't understand--in particular whether what you're describing results in empty rows or empty columns.

I'm describing the results of empty rows, and not columns.

In the screenshot, you can see blue and red sections each one representing a different project in my main data spreadsheet. When I generate a report for the Blue project, It shows me a proper view from the first row to 26 (Simply because the Blue project was the first in my main data sheet),   Now when I try to generate a report for the Red project it includes the first empty 26 rows  (those rows already have data related to the blue project but shown as empty because of filter)

How can I remove those Blue rows and have only the last two rows in the report?

I tried the IF(ISNOTBLANK expression, but it doesn't work, probably I do something wrong / or use the expression incorrectly.

Sincerely

[dbaum]

Your template's Start expression uses the column [PDF Report], but I don't see that column in your data source. The expression in that location in your template should return a list of key values representing the rows you want included.

[Alexey1]

I use a virtual column with expression SELECT(Expense[Key],IF(ISNOTBLANK([Project Name]),[Project Name]=[_THISROW].[Project Name],true))

[dbaum]

---

@Alexey1 wrote:

I use a virtual column

---

FYI: If you don't need that list of keys for any purpose other than within the report, you could eliminate the virtual column and just use the appropriate expression directly within the template where you currently have [PDF Report].

What is the automation event's trigger?

[Alexey1]

Sorry for the long delay, Im exactly crossing the Atlantic, so Starlink comes and go )). My trigger is adds&updates when my [unique id] is not blank. 

Regarding my virtual column and the pdf table, I need a dynamic table with shows me a filtered view of the data sorted by project name. so I can print a pdf out of it.

If you can share with me the appropriate expression directly within the template I will be appreciated it.

Basically, I think that my problem is not the app expressions, I believe it is a problem with my PDF template with expressions: 

<<Start:[PDF Report]>><<[Project Name]>> <<[Category]>> <<[Cost (EU R)]>> <<[Description]>> <<[Receipt]>> <<[Date / Time]>><<End>> .

I tried to play around and use an If(is not blank or a sort condition, but it still doesn't make any sense, I doesn't wanna sort the empty rows and my data appears above the empty rows. 

I tried to modify expression in my filtered view to sort the empty rows: 

SORT( FILTER( "Filter", ([UNIQUE_ID] = ANY( SELECT( Filter[UNIQUE_ID], [_THISROW].[Project Name] = [Project Name] ) )) ), ISNOTBLANK([Project Name]) )

but the system still returns an error.

Maybe someone from the @appsheet team can help me by login into the app and checking it, please?

or I can give you a login link so you can look into the app and check the problem @dbaum 

I'm getting frustrated with this thing, I believe it should be easy to print the pdf report.

Many thanks in advance 

[dbaum]

---

@Alexey1 wrote:

the pdf table

---

Is this another app table separate from the "Expense" table referenced in your virtual column's SELECT expression?

---

@Alexey1 wrote:

If you can share with me the appropriate expression directly within the template I will be appreciated it.

---

If your virtual column's App formula expression returns the correct list of key values, then that expression wouldn't be any different if you used it in the template. You can use any AppSheet expression within a template.

---

@Alexey1 wrote:

Basically, I think that my problem is not the app expressions, I believe it is a problem with my PDF template with expressions: 

<<Start:[PDF Report]>><<[Project Name]>> <<[Category]>> <<[Cost (EU R)]>> <<[Description]>> <<[Receipt]>> <<[Date / Time]>><<End>> .

---

Confirm that all these columns are from the same table that is selected in the Create a file task's Table property:

---

@Alexey1 wrote:

Maybe someone from the @appsheet team can help me by login into the app and checking it, please?

---

You'd need to make that request via AppSheet Support.

[Joseph_Seddik · Accepted solution]

Sorry to jump in 🙂 

Your SELECT expression is forcing the selection of all rows empty or not. Instead you should write this:

SELECT( Expense[Key],
   AND( ISNOTBLANK([Project Name]), [Project Name]=[_THISROW].[Project Name] )
)

And I echo the words of @dbaum, you really should eliminate the virtual column and put the expression in the template directly.

[Alexey1]

So to make it clear, do you want me to force the app to filter my main data table and pull the filtered report from there?

[Joseph_Seddik · Accepted solution]

No, I'm just telling you that your expression is explicitly selecting the empty rows and that's why you have them in the report, and I gave you a corrected version. 

[Alexey1]

Thank you very much @Joseph_Seddik it definitely solved my problem. 

Much appreciated for your help.