Re: Check if there is one child record satisfying ...

mercmobily2 · 11-29-2022 01:16 AM

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.

scott192

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?

mercmobily2

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?!? 😄

Aurelien

hahaha

Here is more food for AppSheet builder's mind:

List expressions - AppSheet Help

Build list dereferences - AppSheet Help

SELECT() - AppSheet Help

mercmobily2

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...?!?

Aurelien

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

scott192

Ha! 😀

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 ...

mercmobily

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?

scott192

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

Aurelien

Hi @mercmobily2

@mercmobily2 wrote:

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".

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

@mercmobily2 wrote:

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

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

@mercmobily2 wrote:

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.

 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

mercmobily

Hi,

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

Merc.

Aurelien

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 🙂

@Marc_Dillon is more experienced about this I think

Marc_Dillon

Yes, you can do SELECT( [Related xxx][column] , condition ).

mercmobily2

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(""))

Aurelien

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

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

scott192

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?

mercmobily2

@Aurelien wrote:
@Marc_Dillon is more experienced about this I think

Well as far as I can tell is like "God" around here 😄

Aurelien

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

@scott192 wrote:

I believe the LIST("") could be substituted with just LIST() to mean the same thing...an empty list.

Yes, indeed.

@mercmobily wrote:

Something like a "console" to actually run expressions and see their results?

@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 !

Steve

@mercmobily2 wrote:

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".

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

@mercmobily2 wrote:

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

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

@mercmobily2 wrote:

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.

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?).

mercmobily2

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?

SkrOYC

@Aurelien wrote:

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

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

mercmobily2

I am getting a warning from AppSheet, saying that my queries might affect performance...

When you write "Related Tablenames", do you mean just the table names? Or do you actually mean the word "related"?

SPecifically, what would this one translate into?

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

Steve

@mercmobily2 wrote:

What does SELECT actually expect as first argument?

SELECT() - AppSheet Help

@mercmobily2 wrote:

why that [_ROWNUMBER]?

Because a specific column must be identified for the return value list.

@mercmobily2 wrote:

what does [Grants Submitted][_ROWNUMBER] actually mean, syntactically

Build list dereferences - AppSheet Help

@mercmobily2 wrote:

and in terms of the result?

The row numbers of the rows matched by the selection.

mercmobily2

Thanks Steve. I was confused by this: https://support.google.com/appsheet/answer/10108207?hl=en since they never, in that page, mention that dataset-name can be a list, rather than a table...!

scott192

LOL I was just reading another topic when I saw @Suvrutt_Gurjar reply. Looking at his use of INTERSECT() I wonder whether this would work as well as all the other methods.

INTERSECT([Grants Submitted][Submitted On],LIST())>0

Where TRUE indicates there are blank dates and FALSE indicates no blank dates.

Does INTERSECT() work with blank lists?

Aurelien

This is @Steve 's magic, guys.

mercmobily2

Only one quick note Steve.

I asked this:

@mercmobily2 wrote:what does [Grants Submitted][_ROWNUMBER] actually mean, syntactically

You answered with a link to "Build list dereferences - AppSheet Help"

But! Reading the docs, I fund this. Note that "Although identical in appearance to a column list expression, this argument is not an expression."

dataset-column - The specification of the table or slice (the "data set") to search and the column from which values are to be gathered, in the form: dataset-name[column-name]. For example, Orders[Order ID]. Although identical in appearance to a column list expression, this argument is not an expression.

Does that meant that that "[Grants Submitted][_ROWNUMBER]" is kind of special syntax for the Select =() function, rather than a Dereferencing expression? Please note that I am not trying to diminish or criticise your AMAZING response, for which I am immensely grateful!

Also, did you put in _ROWNUMBER just to satisfy what Select() is expecting, effectively? Meaning, it's not really used for all intents and purposes... right?

Steve

@mercmobily2 wrote:

Does that meant that that "[Grants Submitted][_ROWNUMBER]" is kind of special syntax for the Select =() function, rather than a Dereferencing expression?

Correct. The first argument of SELECT() identifies the rows in which to search and the column of the matching rows to return. The first argument of SELECT() is specified using familiar syntax available elsewhere in expressions, but the first argument of SELECT() is not itself interpreted as an expression.

@mercmobily2 wrote:

Also, did you put in _ROWNUMBER just to satisfy what Select() is expecting, effectively? Meaning, it's not really used for all intents and purposes... right?

Correct, in this case. You have to identify both a row set and a column for SELECT(). The _ROWNUMBER column is common to all row sets, and will always contain a value (a distinct value no less!), making it a good choice for examples and in practice.

mercmobily

@Steve wrote:
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.
SELECT([Grants Submitted][key-column], ISBLANK([Submitted On]))
Replace key-column with the name of the key column of the Grants Submitted table.

Hi Steve,

Am I right in saying that if the selected column is a ref (in this case the ID), then the result is not just a list of fields, but a list of full records?

Merc.

SkrOYC

@mercmobily wrote:

selected column is a ref (in this case the ID), then the result is not just a list of fields, but a list of full records?

Is a list of keys, that can be used to access the whole record's data

mercmobily

How? As in, what would be a practical example?

SkrOYC

Check if there is one child record satisfying a requirement

Dereference expressions - AppSheet Help