Start expression for filtered grandchild records

AppSheet AppSheet Q&A
,
1

I am building a booking app for a youth club. In this app I have created an automation for sending emails to applicants who has sent booking requests. In this email I want to generate a table of records of the rules ([Regler] in Norwegian) that are related to each Rom they have chosen to book.

Header:

Svar på din forespørsel: <<[Status]>>

Tidsmerke: <<[Tidsmerke]>>
E-postadresse: <<[E-postadresse]>>
Navn: <<[Navn]>>
Telefon: <<[Telefon]>>
Start: <<[Start]>>
Slutt: <<[Slutt]>>
Rom: <<[Rom]>>
Beskrivelse: <<[Beskrivelse]>>

Schema: BookingrequestRom ← Rom[Related Reglers] ← Regler[Rom]

I managed to do this with this nested <>-expression.

<<Start: [Rom]>>



Rom

Regel / sjekkpunkt

Beskrivelse

Tegning


<<Start: [Related Reglers]>><<[Rom]>>

<<[Regel / sjekkpunkt]>>

<<[Beskrivelse]>>

<<[Tegning]>><>

However, this gives as result a table for each [Rom], which is ok, but also for the [Rom]s that currently has no [Related Reglers]. Hence, a bunch of empty tables in between. So how can i filter this?

I tried a select expression directly refering to the [Relger]-table with a nested IN-function to check whether a [Regel] is part of the List[Rom]

<<Start: Select(Regler[Regel ID], IN([Rom], LIST([_THISROW].[Rom])))>><<[Rom]>>
<<[Regel / sjekkpunkt]>>
<<[Beskrivelse]>>
<<[Tegning]>><>

<> in first cell, <> in last.

I presume the LIST([_THISROW].[Rom]) part is flawed, since (presuming) [_THISROW] is single value however put?

Anyway, this returns blank table, no error in the monitor.

Appreciate any ideas on this : )

-Bjørnar

2

If [Rom] is an enumlist, you may want to give the following a try

Instead of IN([Rom], LIST([_THISROW].[Rom])), please try

IN([Rom], [_THISROW].[Rom])

3

Howdy, I live in Texas but my ancestors came from Sweden, it is cool to help someone from Norway online. I find it interesting.

With your question I have had some difficulty with templates in the past as well, the challenging part with it is that there is nothing that checks it for you and it is hard to know what is going on in the background with them.

So your “<<Start: [Rom]>>” expression tells AppSheet that you want this table for every Rom that you have.

You could start with a select expression <<Start: SELECT(Table[ROM ID],ISNOTBLANK([Related Reglers]))>>

You could also start with an If: statement that way if there are no applicable rooms that fit your if then the table will be skipped. Just don’t forget the <>

So I would look into changing that <<Start: [ROM]>> expression because it will give you a table for each room no matter what as it currently stands.

I hope this helps. Additionally, when I am setting up templates, I like to test the expressions in AppSheet first. You can add a virtual column to the proper table and can test the expressions you use. Additionally appsheettraining.com has some great resources on their website and youtube channel. I will also link a useful video below going over template expressions.

Sellström

QREW Technologies

Template expression free video

4

Thanx a lot! This worked out perfectly :slightly_smiling_face: