Unanswered

How to send users email depending on what columns they have data in in a SharePoint list

(0) Share

Report

Posted on by Oliviamicrouser

Hi All,

Would really appreciate some help on building a flow. To put it simply: I am trying to build a flow that sends an email (when I want to trigger the flow, so I assume this is a button flow?) to all users in the SharePoint list.

Sounds simple, but just wait...

Users have been added to the SharePoint list for different things - the dates that they have completed a certain 'certificate' on.

There is 3 different 'certificates' that they can complete. There is a column for each of these certificates eg. Certificate A, Certificate B and Certificate C. If a user has completed any of the certificates, the date they have completed it on will appear in the that column (on the same row as their name)

So what I want to do is send a reminder email to each person saying:

"Hi abc, you are still yet to complete your x and y certificate"

So what this would require is the flow reading the SharePoint list, recognizing who has and hasn't done which certificates and then sending them a reminder email to users for them to do the ones they have not done all in the one email to each user (not sending a user 2 separate emails for each of the certificates they have not done)

Is this possible to do?

Would love any help or suggestions as I have been stuck trying to do this for days - not sure if I am barking up the wrong tree by trying to initialize variables or what?!

Thanks in advance!

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

zaphod88 502 on at

Like (0)

Report

I'd suggest to use several "Filter array" actions to filter the "Get items" array. You may have to edit "Filter array" actions in advanced mode as the graphical editing just allows one condition.

For example:
@And(and(equals(item()?['Cert1'],false),equals(item()?['Cert2'], false),equals(item()?['Cert3'], true))
- gets you the users for which Cert 1 + Cert 2 are false, Cert 3 is true. You have to replace "Cert#" by your corresponding column names.
Afterwards, you can create a "Send email" action in an "Apply to each" (value: Filter array - body) and send an email to "item()?['Email']" (where "Email" is the column name of the column where the email is given).

Was this reply helpful? Yes No
Oliviamicrouser 8 on at

Like (0)

Report

Thanks for this suggestion @zaphod88 , I have got pretty lost with attempting to do what you explained so am now trying a way I know I can get a bit further with - initializing the variables, and then using conditions.

The photo I have attached might look large but better explains the flow I have setup

My new problem is, now when I run the flow it is coming up with "InvalidTemplate. Unable to process template language expressions for action 'Condition_2' at line '1' and column '17382': 'The template language function 'contains' expects its first argument 'collection' to be a dictionary (object), an array or a string. The provided value is of type 'Null'.'."

Any ideas why it is doing this now????

Was this reply helpful? Yes No
zaphod88 502 on at

Like (0)

Report

Try it like that:
Filter array value is the expression from above. Just change the boolean values and your column names to adapt your needs and insert the same action(s) as often as you need them. Mail "To" is item()?['PeoplePickerColumn/Email'].
You can of course also try working with conditions, but that is pretty ineffective as the flow loops through the whole array. "Filter array" action is much faster.

Was this reply helpful? Yes No
Oliviamicrouser 8 on at

Like (0)

Report

Hi Zaphod88,

Thanks again so much for your help.

I have adjusted the flow as you recommended and now when I run the flow it comes up with an error at the Filter Array which can be seen in the image below.

The data in the columns is either a date (but in a string format) eg. 19/4/2021 or a blank cell if the user has not completed the certificate.

Is it still possible to use this method you are suggesting?

Was this reply helpful? Yes No
zaphod88 502 on at

Like (0)

Report

Try by this scheme:
@And(and(Not(equals(item()?['Cert1'],null)),Not(equals(item()?['Cert2'], null)),Not(equals(item()?['Cert3'], null)))

As your column names contain spaces, please go to list preferences of your SP list and edit the three columns each. At the end of the URL, you can find the column name you need to insert in the expression. When a column name contains spaces or special characters, the name that needs to be filled in in expression editor is (or may be) different from the one Flow shows.

Was this reply helpful? Yes No
Oliviamicrouser 8 on at

Like (0)

Report

Gah, it is still coming back with the same thing even after I have changed the column names in the list settings so they don't have any spaces in them - it is saying they are not a valid boolean value (exact same error message as before)

Was this reply helpful? Yes No
zaphod88 502 on at

Like (0)

Report

Changing Column names doesn't help because expressions take the column name given when created even when it is changed afterwards. So you have to take those from end of the URL.

Was this reply helpful? Yes No
Oliviamicrouser 8 on at

Like (0)

Report

Yeah I have taken the expression from the end of the URL (from each of the 3 columns list settings), but still the error is coming up when I run the flow as

Was this reply helpful? Yes No
zaphod88 502 on at

Like (0)

Report

Please check my formula above again. Its equals(date,null), not equals(date,false).

So: @And(and(Not(equals(item()?['Digital_xx020_Passport'],null)),Not(equals(item()?['Data_x0020_Science_xx020_Stamp'], null)),Not(equals(item()?['AI_x0020_Stamp'], null)))

Was this reply helpful? Yes No