XLSX Email Template - Filtering

[Domearian]

Using an XLSX file as the template in an automated BOT report, I wish to only show rows that have a [Type] of "Menu". How could I do this using this but it shows all items contained within the datasource:

Menu Item

Display Name

Order

Subheading

Type

Local

Admin?

Sites?

Super Admin?

GDPR?

Dashboard?

Further Information.

External Link

Icon

<<Start: Filter(Menu, true)>><<[Menu Item]>>

<<[Display Name]>>

<<[Order]>>

<<[Subheading]>>

<<[Type]>>

<<[Local]>>

<<[Admin?]>>

<<[Sites?]>>

<<[Super Admin?]>>

<<[GDPR?]>>

<<[Dashboard?]>>

<<[Further Information.]>>

<<[External Link]>>

<<[Icon]>><<End>>

I've tried using slices and can't get slices to work. Thank you.

[Suvrutt_Gurjar · Accepted solution]

Please try with a <<START>> expression of 

<<Start: Filter("Menu", [Type]="Menu")>>

[Suvrutt_Gurjar · Accepted solution]

Yes that is possible. 

For example , you could have an expression something like 

<<Start: Filter("Menu", AND ( [Type]="Menu", [Subheading]="Super"))>>

The second argument in FILTER() should evaluate to TRUE or FALSE in terms of a row's columns that are included in the second argument of the FILTER() function to filter in/filter out that row.

FILTER() - AppSheet Help

[Suvrutt_Gurjar · Accepted solution]

Please try with a <<START>> expression of 

<<Start: Filter("Menu", [Type]="Menu")>>

[Domearian]

That worked perfectly. Is it possible to have more than 1 filter expression?

[Suvrutt_Gurjar]

In general yes. Could you elaborate?

[Domearian]

Sorry, to explain better. I can use AND(......) to filter by more than 1 item, is that possible with this expression.

[Domearian]

Say I wanted to filter the Menu as before but then also filter if "Sites?" = Yes.

[Domearian]

Have to say it would be so much easier if I could filter by a sliced Data.

[Suvrutt_Gurjar]

---

@Domearian wrote:

Have to say it would be so much easier if I could filter by a sliced Data.

---

That should also be possible. You should be able to use slice in the <<START>> expressions.

[Domearian]

It what way? <<Start: Filter(SLICE HERE?"Menu", [Type]="Menu")>>

[Suvrutt_Gurjar · Accepted solution]

If you have already applied necessary filtering in the slice, then slice based start expression need not have filtering argument.

For example if you have already created a slice called "Menu_Slice" on  the "Menu" table with a slice filter expression such as AND( [Type]="Menu", [Sites?]), then the slice has already filtered in rows with the necessary conditions.

In such a case , the start expression can be simply

<<START: Menu_Slice[Key column of Menu Table]>>

This will create a list of keys from the slice " Menu_Slice". Other filtering is already done at the slice level itself.

[Domearian]

Makes perfect sense. Thank you for explaining it. Thank you for you help. Much appreciated.

[Suvrutt_Gurjar · Accepted solution]

Yes that is possible. 

For example , you could have an expression something like 

<<Start: Filter("Menu", AND ( [Type]="Menu", [Subheading]="Super"))>>

The second argument in FILTER() should evaluate to TRUE or FALSE in terms of a row's columns that are included in the second argument of the FILTER() function to filter in/filter out that row.

FILTER() - AppSheet Help

[Domearian]

That is perfect. Thank you so much for the solution. I shall make a note!

[Domearian]

Just as a final question, I see that you cannot add "custom text" in any cells. Is that still the case? For instance: "Menu is:" <<Menu Item>>

[Suvrutt_Gurjar]

Please try

<< CONCATENATE("Menu is: ", [Menu Item])>>