Recipient Expression for Notification not Returning Emails

[Anton_Dickens]

Hi,

I have a bot to send a monthly notification to a group of users with a link to complete a form - nothing fancy.

I'm having some bother getting the following expression to yield email addresses for the "To" field in the process - SELECT(PersonnelList1[Email],AND([Weekly JSA Review Prompt]="TRUE",[Status]="Active")TRUE). The entity table is set to PersonnelList1.

When I run the test on the expression, for a split second it looks like a result shows up (not sure if it is returning the correct result or not), then disappears. Then test running the bot shows that "To" is missing or invalid.

I would love some help on this please! Thank you in advance!

Anton

[SkrOYC]

---

@Anton_Dickens wrote:

SELECT(PersonnelList1[Email],AND([Weekly JSA Review Prompt]="TRUE",[Status]="Active")TRUE)

---

There is no comma before the last TRUE

[WillowMobileSys]

The syntax for the expression isn't correct, at least what's posted "SELECT(PersonnelList1[Email],AND([Weekly JSA Review Prompt]="TRUE",[Status]="Active")TRUE)".   There should be a comma before "TRUE" but do you really need that parameter to sort the returned emails in descending order?  It is optional.

If this was just a copy/paste error then check these things:

• "PersonnelList1"  is an actual table or slice
• [Email] column is defined as Email data type
• Confirm you do have email rows where [Weekly JSA Review Prompt] = "TRUE and [Status] = "Active"

[Anton_Dickens]

Thank you for the reply - sorry it was a mistype without the comma. I'm finding the same result whether True is specified or not in the expression.

I have checked those things and they are all okay. The Expression Assistant is showing that the fomula is ok.

So interestingly, I wrapped the two parts of the AND statement each in its own CONTAINS() formula, and this seems to be removing the error in the bot test... Maybe this is helpful? It's not a bother for me to leave the CONTAINS() in there, but I'm interested why the expression needed this, any ideas?

The other strange thing is that the test on the formula is only visible for  split second then disappears...

Thank you

[WillowMobileSys]

CONTAINS() probably doesn't work like you think.  It is an operation on text strings to test if a source test string wholly CONTAINS a second sub-string.  You should not need that in this case.

Going back to your original expression inserted into the "To" property of you email task, make sure that you "turned on" the expression flag of that property.  Otherwise, it may be trying to treat it as straight text.

  

Beyond that, you will want to test the expression to make certain it is returning a proper list of email addresses.  To do that, I usually create a temporary Virtual Column  on one of the tables and then add that test VC to a Detail view.  Then I access an existing row to inspect the results from the expression.  If I am not getting correct results then I break down the expression until it does and then analyze why it wasn't before the change.

I don't like the test function.  I feel it is misleading sometimes and there are certain cases where it just doesn't work because the criteria can't be satisfied.  

[Anton_Dickens]

Thank you. 

The way I considered CONTAINS() was how you use it when you are looking into an Enumlist type column for a single entry - to be honest I was getting desperate and trying anything haha.

Yes it's directed as an expression.

I ran it as a VC, it's bringing up the correct list of email addresses when you view it in form view as an email base type column.

Here is the error message which is in the Test run of the bot (and what disappears when CONTAINS() are added to the expression):

The bot completes the process fine when you manually add an email address. I'm really puzzled!

Cheers

[WillowMobileSys]

Make sure that all of the email addresses being returned are valid email addresses. 

Can you show:

1. An image of what the expression was returning in the Virtual Column
2. An image of how the expression looks in the "To" property. 

This is a fairly straight forward implementation.  As a last resort, I would suggest re-creating the Task and possibly the automation, if everything else looks in order.  It does occasionally inexplicably resolve the problem.

[Anton_Dickens]

Thank you.

Yes the emails all look good - used as the user's ID within the app.

Here is the VC result as a list (default):

And as base type Email (these are the ones that I'm wanting returned):

And the expression in the "To" field:

If you can't see anything wrong I will redo the Bot.

Thank you again for your time on this!

Anton

[WillowMobileSys]

The field for your expression in the expression Assistant seems strange.

When I look at one of my Email Tasks I see this for the To property

Task in the Bot

Expression Assistant - note the presence of the property name of "To"

[Anton_Dickens]

Sorry for the late reply.

That's interesting, definitely not what I'm seeing, any ideas why?:

I have started from scratch with the bot, task and process, same result.

I'm just finding it very curious that using CONTAINS() makes it operate fine. I will leave it that way I guess, seems like some funny kind of anomaly.

Thanks again!

[SkrOYC]

Try placing just one email on the To field and the rest on CC

[Anton_Dickens]

Hi guys,

I just looked back over this thread and just to clarify, I'm wanting to send app notifications rather than an email (which is why the To field is looking different I think). Does this change the logic at all?

Thank you!

[WillowMobileSys]

Ah,  Push Notifications I should have realized the difference.  No there is no difference in the logic.  You still need to specify a list of email addresses.

Initially you were getting a message that the To field is "missing or invalid".  Is that still the same problem you are having?

I still think there is some issue with one or more of the email addresses where they are not considered valid.

At one point, I had suggested to create a Virtual Column and assign it the expression and then view it in the app.  Look the list over closely and double-check each email address. 

I would then suggest to copy and paste that list of email addresses into the Push Notification Task instead of the expression.  Do you get the same error message?  If you do, then begin removing the email addresses one by one until you no longer get the error.  Then inspect what you just removed to see why it was causing a problem.

[SkrOYC]

Send a notification from an automation - AppSheet Help

Shouldn't be a problem