Automation - send an email when three field values are all not null

[JamesWB]

I want to send an email in an automation when the condition is met that three column values are all not null, null being their starting condition and the triggering condition being that all three contain a value.

So the expression is presumably something like this:

([Severity of Injury] AND [Frequency] AND [Possibility of Avoiding]) NOT NULL

Those field names were all selected in the expression builder using Insert, but the error message is "unable to find column". I imagine my syntax is wrong, but how?

thanks for any help

James

[Suvrutt_Gurjar · Accepted solution]

You may want to share the expression you have built so far but in general your condition could be something like below based on the description you have given of all three fields being non blank to trigger the bot.

AND(        OR(ISBLANK([_THISROW_BEFORE].[Severity of Injury]),

                         ISBLANK([_THISROW_BEFORE].[Frequency]),

                          ISBLANK([_THISROW_BEFORE]. [Possibility of Avoiding])

                         ),

               AND(     

                     ISNOTBLANK([_THISROW_AFTER].[Severity of Injury]) ,

                    ISNOTBLANK([_THISROW_AFTER].[Frequency]),

                    ISNOTBLANK([_THISROW_AFTER]. [Possibility of Avoiding])

                     )

          )

Reference articles :

AND() - AppSheet Help

OR() - AppSheet Help

Access column values before and after an update - AppSheet Help

Example automations - AppSheet Help

Edit: added some missing parenthesis to he expression.

[JamesWB]

Oh thank you for your help!

I corrected a few punctuation bits in the code as below.

AND( OR(ISBLANK([_THISROW_BEFORE].[Severity of Injury]) ,

ISBLANK([_THISROW_BEFORE].[Frequency]) ,

ISBLANK([_THISROW_BEFORE].[Possibility of Avoiding])

),

AND(

ISNOTBLANK([_THISROW_AFTER].[Severity of Injury]) ,

ISNOTBLANK([_THISROW_AFTER].[Frequency]) ,

ISNOTBLANK([_THISROW_AFTER]. [Possibility of Avoiding])

)

)

It doesn't work though - no email gets sent when all three fields have something in them as opposed to being empty. Is there something else I have to do in order to get automation email to work?

[Suvrutt_Gurjar]

The emails will get sent when all of the values to non blank from any one of them being blank. Could you update if you are testing it that way. If the fields are already non blank for all the three fields, the automation will nogt trigger.

You may also wish to share screenshots of your bot set up.

[JamesWB]

Ahhh... I changed Event Type from Updates Only to All Changes and it worked. I notice it also works with Adds and Updates, which I think is the one I want.

[Suvrutt_Gurjar]

Great.  "Adds and Updates' means the bot will trigger when one adds the new record or when one updates an existing record.

"All Changes" adds a third operation of record "delete" in addition to "Adds and Updates". So with "All Changes" setting, the bot will also trigger when a record is deleted.

Of course for all these operations, the conditional expression is checked by the bot and bot fires only when the bot condition is also satisfied. So in your case, if any other column in the record changes apart from those three used in condition, the bot will not trigger even though update to the record has taken place.

[JamesWB]

It's pretty cool. I hadn't twigged that choosing Adds and Updates would send something if a row gets deleted, so now I've changed it to Adds Only.

I like this, thanks so much for the coding help!