[O365] Using SharePoint boolean fields with Microsoft Flow
My previous post just now was on the topic of Microsoft Flow, the workflow-style application that allows you to perform “if this, than that” type logic linking different applications together. Basically, Flow provides you a way of automating actions by having a set of triggers, some logic and using API’s to perform actions. It wraps all of this in a nice and easy to use user interface, making this functionality that pretty much everyone can leverage. Power to the business!
In this post I want to show how I created a real-life flow to automate a process for expense declarations. The process is a really simple one:
- We’ve created an Expense Declarations library on SharePoint.
- We added a new Expense Declaration content type which has an Excel template for the declaration.
- We also added a boolean field “Ready” which signals the expense form is ready for processing.
- The form should now be sent to the person handling the declarations. Of course it would be even better to send it directly into an API, but unfortunately that’s not available for us.
As said, the basic elements of a flow are a trigger, some logic (conditions) and actions. Let’s go!
Defining the trigger
To create the flow, we head over to flow.microsoft.com and after signing in (or up), we begin with a blank flow. The first action we add is “When an existing item is modified”. This is because:
- The “created” action will fire off immediately after the form was created and is probably still empty.
- The action for a modified document will not contain the correct information, our Ready field will not be present. This is supposed to be changed in the future though.
So we set up the existing item modified trigger:
Note: because your library is a library, it might not show up in the suggestions. That doesn’t mean you cannot use it though, just type in the name and you should be good to go.
Creating a condition
Next, we need to set-up the condition. We want the declaration to be sent only when the Ready field is set to Yes. Because the value is stored as a boolean, the field value sent to flow will be “true”. You can check that by running your flow (trigger it from SharePoint after saving) and clicking the trigger to inspect the values coming in:
Check out the value for Ready:
So now the most straightforward thing to do would be to set up the condition like this:
But this does not work. I suspect the engine will handle “true” as a string which would give a comparison of “true == ‘true'” which is false. To fix this, put the editor in advanced mode and use the following expression: @equals(triggerBody()?[‘Ready’], bool(1)).
bool(1) will convert to ‘true’ so our comparison should now be “true == true” whenever the Ready field is set to Yes in SharePoint.
Setting up the action
Lastly, I created a simple e-mail action to send out a notification to the correct user. Ideally I wanted to add the file contents to that e-mail but that isn’t possible (yet) due to the “item modified” trigger which is not aware of a file. I tried several ways to get around this but didn’t succeed. You can probably get there with something customized like an Azure Function, but for now the plain old e-mail will do. Simply set-up an Office365 e-mail action to send out a mail to inform the correct user a new declaration has been added, paste in the link to the library and you’re set.
When I find a way to attach the file to the e-mail or send a direct link to the file, I’ll update this post!
November 2, 2016 at 10:57 am |
Hello Jasper
First of all thank you for blogging about this !
Jasper, I have a very similar condition, But I couldn’t resolve it with your advanced mode expression.
My Scenario:
I have a very simple SharePoint Custom List which contains a few columns as below with their data types:
Department1: Yes/No (check box) with default value No which mean it is Unchecked.
Order1: Single line of text.
Department2: Yes/No (check box) with default value No which mean it is Unchecked.
Order2: Single line of text.
Department3: Yes/No (check box) with default value No which mean it is Unchecked.
Order3: Single line of text.
Workflow Need:
If “Department1” is selected, then, Send “Order1” value to Manager1@Email address. Else, Do nothing !
If “Department2” is selected, then, Send “Order2” value to Manager2@Email address. Else, Do nothing !
If “Department3” is selected, then, Send “Order3” value to Manager3@Email address. Else, Do nothing !
In other words Jasper, I only want to send the “Order” value(s) to the Manager(s) whose their “Department” CheckBox is selected.
In SharePoint the column “Yes/No (check box)” stores the values: True = Checked, and False = Unchecked. However, When I use your advanced mode expression it doesn’t work in Flow. Why Jasper?
What can I do in Flow to be able to catch the “Yes/No (check box)” column if it is Checked or Unchecked?
I would greatly appreciate your inputs !
November 2, 2016 at 3:51 pm |
Hi Mary! Personally I would take a different approach to such a problem. You could create a second list where you store the department and the e-mail address of the manager. Something like:
– Department1 | Manager1@Email
– Department2 | Manager2@Email
– Department3 | Manager3@Email
Then in your custom list, just let the user select the correct department (the name should match the above values of course). In flow you can use a “Get List Items” action to fetch all of the list items in the departments list and then combine the two with an expression like this:
equals(item()?[‘Department’], triggerBody()?[‘Department’])
That should give you the item which you can then use to provide the e-mail address of the responsible manager. Makes things a bit cleaner.
As for why your flow does not work: it’s hard to tell without being able to look. You should check the metadata coming from the trigger to verify that the correct value is in there and that it’s set to the value you’re expecting. For boolean fields, use bool(0) or bool(1) in your comparison.
November 4, 2016 at 8:14 pm |
Hello again Jasper
Thank you for comment. I think I did a poor job explaining my problem at the first time, So, I hope this time I am giving the clear picture of the situation.
Well Jasper, The business team wants one single SharePoint list which they will use to order certain items, and when they save/submit a request, an email (Containing the provided data) has to be sent only to the right manager.
So, I have a very simple SharePoint Custom List which contains a few columns which their data types are: “Single line of text” and “Yes/No (check box) with default value No which mean it is Unchecked”.
Exactly as shown on the example screenshot on: https://www.dropbox.com/s/g0wgbv66bnrfqfd/OrderSPList.png
Microsoft Flow Need: Please look at the screenshot of the SharePoint List Form:
– If “Cards” Checkbox is selected, then, Send only the “Cards Order Details” value field to the Cards-Manager@Email address only. Else, Do nothing.
– If “Brochures” Checkbox is selected, then, Send only the “Brochures Order Details” value field to Brochures-Manager@Email address only. Else, Do nothing.
– If “Posters” Checkbox is selected, then, Send only “Posters Order Details” value field to Posters-Manager@Email address only. Else, Do nothing.
– If 2 or 3 Checkboxes are selected, then, Send their “Order Details” value(s) to their corresponding Managers.
– If nothing is selected, Do nothing.
In other words Jasper, We only want to email the “Order Details” value(s) to the Manager(s) whose their area Checkbox is selected.
My problem and attempts:
In Flow, I could not at all catch the trigger if the “Yes/No (check box)” is Checked or not, and I tried zillions of possibilities such as:
SharePoint: When a new item is created
Condition: CheckboxFieldName contains Yes/True/1 of course tried them separately.
Condition: CheckboxFieldNam starts with Yes/True/1
Condition: CheckboxFieldName is equal to Yes/True/1
I tried the advanced mode expressions like:
@equals(triggerBody()?[‘FieldName’], bool(1))
@equals(triggerBody()?[‘FieldName’], bool(Yes))
@equals(triggerBody()?[‘FieldName’], bool(True))
Also, I tried with small/capital letters and with/out “” Nothing works 🙁
Notes:
These are some notes to consider, Please:
– The business team only wants to use only 1 single SharePoint list to submit orders. They will never allow using multiple lists.
– It is really,really important that we use the “Yes/No (check box)” column because we rely on it on the process.
– SharePoint Designer, Nintex, K2 or other solutions are not allowed. Only Flow is allowed !
Could you please help me Jasper? Unfortunately I couldn’t apply your suggestion because I really have to use the “Yes/No (check box)” SharePoint column.
With warm regards
Mary
November 7, 2016 at 12:15 pm |
Hi Mary,
Thanks for the explanation. It would be trivial to check what output your Flow implementation is producing. You can check this by entering the history for your Flow and clicking on one of the steps. The step will expand and show the information relevant for that particular step.
In your case, we’re interested in the output the trigger is producing, as I wonder whether the boolean fields are in there and which value they have. Note there is a separate action available to get an item (based on the ID) and all of its metadata. You could try to include that action to ensure that the metadata you need to actually present. If you could attach a screenshot of your flow steps + their data that would help in finding out what is going wrong here.
December 7, 2016 at 10:07 pm |
***If your Boolean Field has spaces***
I was trying to do something similar but the field I had the Boolean in had spaces in it so my Field was called “BSpoke Access Required” so my advanced lookup had to look like this: @equals(triggerBody()?[‘BSpoke_x0020_Access_x0020_Require’], bool(1)) to keep it simple I used basic mode to create a condition which then filled in the advanced mode field so all I had to do was delete what I had typed into the “Value” Field (anything after the last , ) in basic mode and replace it with bool(1)) in advanced mode.
January 17, 2017 at 5:02 pm |
Hi Jasper
Do you know how to add an multiple OR condition in a flow ?
As :
If field1.value = x or field2.value = z or field3.value > 0 then do an action
Thanks
January 17, 2017 at 5:20 pm |
Hi Hugues, you can add AND and OR statements using the following syntax:
@and(condition1, condition2)
@or(condition1, condition2)
Example:
@and(equals(item()?[‘ID’], body(‘Get_file_metadata’)?[‘Id’]), equals(triggerBody()?[‘Ready’], bool(1)))
And those can be nested, so you can chain multiple.
January 17, 2017 at 11:26 pm |
I ran into this issue today and discovered that the bool(1) was not working. I am not certain if Microsoft changed the behavior or just what happened there. I tested the value by sending it in an email on both condition met and condition not met scenarios. What that revealed is that it was reflecting True (not to be confused with “True” as the value.
I still had to use the advanced mode in order to set this as you mention here, the only difference being the final condition. This could be different for lists vs libraries or old lists vs new. For those users who tried your steps without success they may find success by changing from: @equals(triggerBody()?[‘Ready’], bool(1))
To: @equals(triggerBody()?[‘Ready’], True)
January 18, 2017 at 8:51 am |
Good catch and thanks for adding that here! Could well be that there are some inconsistencies still between the different triggers or ‘read’ actions. Probably missing some parsing. Might be worth while registering this one on the forums as well so the product team can take a look.
February 20, 2017 at 11:10 pm |
Hello,
I figured out a solution to send emails with attachment from sharepoint library and lists via a flow.
But somehow, it seems not to reach the lists, because sometimes I have to fire off the flow several times befor it rans through.
How to I do it. I created an item element out of a library file with a sharepoint workflow, where I save the server relative path from the library into a field of the new item. This path will be used to get the file into the email.
Flow fires of, when:
1. When an item is created
2. Get file value via path (read the path from the first action)
3. Get file meta data via path (read the path from the first action)
4. Send email (you can add file content from step 2.
5. Update item (set SendEmail = True)
That’s it and it works, but not 100% all the time. Sometimes the flow stops and it says that it doesn’t find the file. When I fire off the flow again, it rans trough.
Someone an idea what could cause this behaviour?
Thanks,
Alex
March 4, 2017 at 8:18 pm |
This post rocks!
The bool(1) to tip just solved a problem in a Flow that I’ve been struggling with for hours.
Thank you!
March 30, 2017 at 4:13 pm |
Hello,
I made a workflow – “When item is created” – “When existing item is modified” once a user creates or modifies a list it will trigger an email. It’ll only email once when the user modifies the list. It will not trigger the action “when existing item is modified” again the next time. I think it needs a loop when a user updates the list it’ll trigger an email again.
How can I attain that?
April 1, 2017 at 1:55 pm |
Hi, I’m not sure to be honest. The “when an item is modified” trigger for me fires each time an item is modified, not just once. If you’re seeing different behavior I’d suggest contacting support.
April 4, 2017 at 11:22 am |
Can some one give me an example : I would like to test if FLOW works for multiple IF and ELSE statements.
Also would like example or scenarios with steps to try Where a loop is being run 5 times until loop gets ended using FLOW
August 4, 2017 at 10:12 pm |
Hi everyone,
I have non idea why this is still not working for me. Below is my situation.
I have a yes\no box in a list called submit, I have the condition set to @equals(triggerBody()?[‘Submit’], bool(1))
Keep getting Inputs, expression result false
Then my next step gives me:
ActionBranchingConditionNotSatisfied. The execution of template action ‘Send_approval_email’ skipped: the branching condition for this action is not satisfied.
Yes, I do have Yes selected.
I will be grateful with some assistance.
Thank you much