Check if there is one child record satisfying a requirement

AppSheet AppSheet Q&A
1

Hi,

This is so easy it’s embarrassing.

I have a table called “Grants Deadlines”. One of the column is “Grants Submitted” which has REF_ROWS(“Grants Submitted”, “Grant Deadline ID”).

I obviously have a Grants Submitted table, which has a DATE field called “Submitted On”.

All I want to do, and at this point I should be able to, but I am obviously too stupid for this, is this:

I want to add a virtual column to Grants Deadline, which will be TRUE if and only if at least ONE child in Grants Submitted has an EMPTY “Submitted On”.

As a bonus question, I would love a virtual column with the number of children in Grants Submitted where “Submitted On” is empty.

As a bonus, bonus question, I would love even more a virtual column with a LIST with all of the “Grants Submitted” children with “Submitted On” is empty.

I am missing something… big. Please help. I really tried by myself.

2

This is off the top of my head without testing…

Let’s satisfy your first question and see if that works.

Count([Grants Submitted][Submitted On]) <> Count([Grants Submitted][Submitted On] - LIST(“”))

Theoretically, that formula should compare the list of records count in your [Submitted On] field with the same list MINUS any blank value (List(“”)). The value for the formula will be TRUE for records that have missing values and FALSE if there are no missing values.

See if that works?

3 Likes
3

Hi @mercmobily2

ISNOTBLANK(
  FILTER("Grants Submitted",
    AND(
     [Grant Deadline ID]=[_THISROW],
     ISBLANK([Submitted On])
  )
)

COUNT(
  FILTER("Grants Submitted",
    AND(
     [Grant Deadline ID]=[_THISROW],
     ISBLANK([Submitted On])
  )
)

FILTER("Grants Submitted",
    AND(
     [Grant Deadline ID]=[_THISROW],
     ISBLANK([Submitted On])
  )
)

In the end, you may want to proceed in the opposite way:

  • create a virtual column [_emptyGrantSubmitted] with the previous expression

  • then, wrap it in a COUNT() expression on another virtual column

COUNT( [_emptyGrantSubmitted])
  • And eventually, wrap it in a ISNOTBLANK() expression.
ISNOTBLANK( [_emptyGrantSubmitted])

For reference:

Use virtual columns - AppSheet Help

FILTER() - AppSheet Help

ISNOTBLANK() - AppSheet Help

COUNT() - AppSheet Help

3 Likes
4

First of all, thanks a million for your answer.

Oh my it worked but… why?!? I really don’t get how that LIST(“”) does anything!

I mean, I am a software developer, and in my head I had to figure out how to take [Grants Submitted], apply a filter with empty dates, and then count the result. This… this really puzzles me.

My next question (for tomorrow) was to figure out how to apply some more advanced filter (checking if any children rows have date pairs that fall into TODAY() ) – I was keen on working this out on my own, but now… what!? Why?!? :grinning_face_with_smiling_eyes:

2 Likes
5

hahaha

Here is more food for AppSheet builder’s mind:

List expressions - AppSheet Help

Build list dereferences - AppSheet Help

SELECT() - AppSheet Help

4 Likes
6

Hi,

Quick one… Is there a function like Filter, but that applies to lists rather than tables?

Merc.

7

If this is possible, I don’t know it.

However, I know SELECT() expression applies to Ref fields such as SELECT([Related XXX]), so maybe. I never gave it a try :slightly_smiling_face:

@Marc_Dillon is more experienced about this I think

1 Like
8

I will read through those but… really, in a nutshell, how does that LIST(“”) indicate that SPECIFICALLY the records with [Submitted On] set to empty are actually taken off…?!?

9

Well as far as I can tell is like “God” around here :grinning_face_with_smiling_eyes:

1 Like
10

You can check it with another virtual column, then click on “View Data” to monitor each virtual column value:

Aurelien_0-1669717503805.png
Aurelien_0-1669717503805.png919×142 15.1 KB

2 Likes
11

Ha! :grinning_face:

It is mindblowing really isn’t it? I believe the LIST(“”) could be substituted with just LIST() to mean the same thing…an empty list.

Visualising your [Submitted On] column as a list of values, LIST(date,date,date,date,date,date,) then I imagine the empty is referring to the comma separated gaps that occur for the blanks.

I think the list subtraction is a quirk of Appsheet specifically. It would be lovely for some more list manipulation functions like with arrays, but I suppose that would mean breaking things that already work …

12

I “mind of” get it. Is there a way to play with expressions in a context… Something like a “console” to actually run expressions and see their results?

13

Only using the ‘Test’ section on the Expression Editor as far as I know?

1 Like
14

This:

[Grants Submitted][Submitted On] - LIST("")

will remove duplicate as well. So if two submissiont have been on, let’s say, today, you will only see one in the end.

Subtract values from a list - AppSheet Help

Yes, indeed.

@scott192 is correct, and you can also use the “View Data” button.

btw: I didn’t understand why @mercmobily2 were mentionning the LIST() question…I didn’t see @scott192 's contribution, my bad !

2 Likes
15

Is this:

ISNOTBLANK(
  FILTER("Grants Submitted",
    AND(
     [Grant Deadline ID]=[_THISROW],
     ISBLANK([Submitted On])
  )
)

Preferable to the other solution given to me above:

Count([Grants Submitted][Submitted On]) <> Count([Grants Submitted][Submitted On] - LIST(""))
16

In this specific case, I would say it’s equivalent.

EDIT: The COUNT([Related …]) would be more efficient though.

17

I wonder…

My Count([Related…]…expression performs the same count twice, does a list subtraction and does a comparison whereas your ISNOTBLANK(FILTER(… expression performs a single query and checks whether the result is blank.

Yours sounds as if it should be more performant?

18 
ISNOTBLANK(SELECT([Grants Submitted][_ROWNUMBER], ISBLANK([Submitted On])))

COUNT(SELECT([Grants Submitted][_ROWNUMBER], ISBLANK([Submitted On])))

SELECT([Grants Submitted][key-column], ISBLANK([Submitted On]))

Replace key-column with the name of the key column of the Grants Submitted table.

If you have the third (the list of rows), I’d then use it to produce the second (count of rows), and use the second to produce the first (at least one missing?).

4 Likes
19

Thank you!

Only one favour…

SELECT([Grants Submitted][_ROWNUMBER], ISBLANK([Submitted On])

I have two questions about this one:

  1. What does SELECT actually expect as first argument? I would have assumed just [Grants Submitted] – why that [_ROWNUMBER]?

  2. In fact, what does [Grants Submitted][_ROWNUMBER] actually mean, syntactically and in terms of the result?

20

Since a [Related...] is already filtered, using SELECT() with those is more efficient. You just replace the name of the table with the [Related ...] so you end up with a list dereference. Also you don’t need to add another filter condition to make sure you get the rows that are related because they are already filtered…

So this:

SELECT(
  TableName[WantedColumn],
  AND(
    [ForeignKey]=[_THISROW],
    a-filter-expression
  )
)

Equals this:

SELECT(
  [Related TableNames][WantedColumn],
  a-filter-expression
)

But it’s more efficient because the number of records evaluated by SELECT() is less on the second expression

1 Like