Action button display despite a behavior rule returning a negative value

[paullynun]

Hi.

Am I pushing the limit of behavior rules by running a query on children records to determine if an action button should be displayed?

In the example below the action button GA1 Run Start is displayed even if the behavior query returns a negative value.

[WillowMobileSys]

No. But I am wondering about the usage of ISBLANK(INDEX()). Are you trying to determine if a record exists or not?

Also, you can simplify the AND(AND()) into a single AND

[paullynun]

Hi @WillowMobileSystems.

Yes! It sounds like you have something in mind to propose.

[WillowMobileSys · Accepted solution]

It sounds like @Steve got you fixed up. For what its worth, when I am checking for existence of rows I typically use COUNT(). Depending on what I need/want, I use COUNT() = 0 or COUNT() > 0 .

ISBLANK() just never feels right to me when its a list of values being returned. For example what happens if only one item is returned and it is a blank?

EDITED: so I would implement it very close to what @Steve did but using COUNT() like this:

COUNT(
  FILTER(
    "Log bdt",
    AND(
        [Bon de travail ID] = [_THISROW].[Bon de travail ID],
        USEREMAIL() = [Usager],
        ISNOTBLANK([Date debut]),
        ISBLANK([Date fin])
    )
  )
) = 0

[Steve]

WillowMobileSystems:

ISBLANK() just never feels right to me when its a list of values being returned. For example what happens if only one item is returned and it is a blank?

I think there’s no difference in AppSheet between a list with one blank item and a list with no items. If you consider how each of those would be stored in a spreadsheet cell, both would be an empty cell. I haven’t confirmed this, though. Certainly, though, your approach avoids the question altogether, and is perfectly valid.

[WillowMobileSys]

Steve:

I think there’s no difference

A quick test to confirm!

A List with NO items is considered blank

A list with 1 blank item is considered NOT blank

[Steve]

Of course, FILTER() will never return a blank item, since there’s no such thing as a row without a key.

[WillowMobileSys]

Good Point! That’s what happens when you don’t test exactly what’s being used!!

[paullynun]

Hi @WillowMobileSystems.

Thanks for your query with COUNT! It makes a lot of sense. I like it.

[Steve]

Your expression, reformatted for my clarity:

ISBLANK(
  INDEX(
    FILTER(
      "Log bdt",
      AND(
        AND(
          ([Bon de travail ID] = [_THISROW].[Bon de travail ID]),
          ([Usager] = USEREMAIL()),
          ([Date debut] <> "")
        ),
        ([Date fin] = "")
      )
    ),
    1
  )
)

Then with some improvements:

ISBLANK(
  FILTER(
    "Log bdt",
    AND(
      ([Bon de travail ID] = [_THISROW].[Bon de travail ID]),
      (USEREMAIL() = [Usager]),
      ISNOTBLANK([Date debut]),
      ISBLANK([Date fin])
    )
  )
)

Does that fix it?

Also, expanding the dropdown highlighted in green below might shed some light:

[paullynun]

Steve:

ISBLANK( INDEX( FILTER( “Log bdt”, AND( AND( ([Bon de travail ID] = [_THISROW].[Bon de travail ID]), ([Usager] = USEREMAIL()), ([Date debut] <> “”) ), ([Date fin] = “”) ) ), 1 ) )

Hi Steve.

Thanks for taking the time to teach me some SQL and providing my with a solution!
Your query end up with the same negative value as mine but the action button react positively to yours. I know my SQL is just enough to craft something far from perfection or good performance. I am puzzled on what make yours to work.

As requested the information you asked. It looks like the execution steps/plan of the SQL query.

my query results:

Your query results:

[WillowMobileSys]

paullynun:

I am puzzled on what make yours to work.

paullynun:

What is bothering me is that from your images of the test results, it seems to imply that the SELECT() (created from the FILTER() function) is returning 4 rows?

As far as I can tell, it should be returning only the one row that matches [Bon de travail ID] and only if the other details match the criteria. So you only get a single row or no rows. But then again, I am making assumptions. I haven’t seen your data nor how you have defined the columns.

Additionally, when a list of values are returned by any LIST type function, there is never a guarantee of order. So when applying the INDEX() function you cannot assume which row is going to be returned.

If your intention is for the FILTER() function to return a single row or none. Then I would temporarily focus on just this FILTER() expression. Create a VIRTUAL COLUMN and investigate it by itself until you can figure out why its returning more than one ID. This seems critical for the Action to behave correctly.

[Steve]

paullynun:

Thanks for taking the time to teach me some SQL and providing my with a solution!
Your query end up with the same negative value as mine but the action button react positively to yours. I know my SQL is just enough to craft something far from perfection or good performance. I am puzzled on what make yours to work.

Just a point of clarity: the expressions AppSheet uses are not SQL. If you say “SQL”, that means something particular to many people. We call what AppSheet uses “expressions”. I offer this clarification to help you avoid confusion in the future.

As to why my expression works where yours doesn’t… I suspect it’s because of a quirk in the way AppSheet handles the is-equal operator (=) and is-not-equal operator (<>). For some reason, if the left-side operand to = or <> is blank, the result will be TRUE regardless of the right-side operator. For instance, consider this from your expression:

([Usager] = USEREMAIL())

If the row’s Usager column value is blank, that expression is TRUE, even if USEREMAIL() gives a non-blank value!

But this is likely the actual problem:

([Date debut] <> "")

If the Date debut column value is blank, the expression is TRUE, even though your intention is specifically to test that it is not blank!

To work around this quirk, the left-side value should be non-blank. USEREMAIL() will always give a value, so I swapped it around:

(USEREMAIL() = [Usager])

To test whether a value is blank or not, rather than comparing to "", I used the fuctions specifically provided for this purpose:

ISNOTBLANK([Date debut])

and:

ISBLANK([Date fin])

[paullynun]

Hi @Steve.

Thanks for this explanation!

[WillowMobileSys]

@paullynun @Steve

I am still concerned with your test results returning more than one row. It looked like in your images for both yours and Steve’s version of the FILTER() expression, that 4 rows are being returned. Based on my understanding, that should not be happening! I would recommend investigating into it a bit deeper.

[paullynun]

Hi @WillowMobileSystems.

I appreciate your concerns. For now it is a data issue (something that should not happened as far as I can see).

I invite you on this topic How to find a lost child? 🙂 to better understand the big picture and share your valuable knowledge. This could resolve your concerns.