Slice filter condition for Parent table based on a field value in the Child table

[candicepelser]

Hey All,

I’d like to add a filter condition to an existing slice.
It should filter the Parent table (that I’m slicing), based on a value in a field from the Child table.

The relationship: There can be Zero or One Child per Parent.
The tables are:
Parent: turtle_interaction (with PK - interaction_id)
Child: turtle_admission (with FK = interaction_id, and filter condition field = trc_outcome)

Requirement: The Slice should provide only the turtle_interaction records that have associated turtle_admission records with trc_outcome = NULL (blank)

In the App, the Referenced Child Table List is called:
Related dbo.turtle_admissions

Here’s my expression attempt, that isn’t quite working:

AND([interaction_date_time] > “01/06/2020 00:00:00” ,([interaction_outcome] = 2), (ISBLANK(‘Related dbo.turtle_admissions’.[trc_outcome])))

Thanks!

[Steve]

Your expression should probably be:

AND(
  ([interaction_date_time] > “01/06/2020 00:00:00”),
  ([interaction_outcome] = 2),
  ISBLANK([Related dbo.turtle_admissions][trc_outcome] - LIST(""))
)

[Related dbo.turtle_admissions][trc_outcome] is a list dereference. See Constructing a List from a List Dereference here:

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

A list dereference produces a list. - LIST("") removes any blank elements from the list. See Constructing a List with Subtraction also in the above doc.

[candicepelser]

Thank you so much @Steve.

Unfortunately, this doesn’t work either. The filter condition seems to check if there’s ANY child record. Not only for a child record with NULL value for a specific field.

Any other thoughts?

[candicepelser]

Hi @Steve, I celebrated too early. Could you please take another look at the formula and help me understand why it might not be working?

[Steve]

The expression I gave you should meet your needs as I understand them from your original description. Unfortunately, your use of database terms instead of AppSheet terms may be confusing me. Could you restate the problem in AppSheet terms or in plain language, rather than database terms?