Category: Microsoft Flow

Every Power Automate (MS Flow) Filter Query You Ever Wanted To Know As A Functional Consultant

Hello Readers

This blog is to help fellow consultants to start their journey on Power Automate. We all know how easy it is to create a flow (Watch #TGIF Episode 2 here, if not already).

I am sure as a Business user or a functional consultant, you must have had a situation where you needed someone technical to complete your flow. Most of this bottleneck is because as non-technical people we don’t know what ‘ODATA Query’ is?

Coming from Dynamics 365 background, I never required such filters for native workflows of D365. But here we are moving forward and learning together to be able to work with Flows.

This post will talk about the following two filter types you need while building a flow:

  1. ODATA filter query
  2. Filter array

Before we commence with the filters, i will try to explain you the components of ODATA filter query:

1.Field or Column Name 2.Operator 3.Field value you want to check/filter

Sequence: In most queries the sequence of the components remains like ‘fieldname operator fieldvalue’ but in some cases like contains/does not contains sequence and structure changes to ‘operator(fieldname,’fieldvalue’)’

A few operators: 

Operator Description
eq Equal to
ne Not equal to
contains contains
not contains Does not contains
gt Greater than
lt Less than
ge Greater than or equal to
le Less than or equal to
and And
or Or
startswith Start with the specified value
endswith End with the specified value

ODATA filter query

1.Contains for text fields

This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.

Filter query= contains(textfieldschemaname,’value’)

e.g. if I have to check whether the ‘Subject/Topic’ of a Lead record contains ‘New’ in it; my filter would be contains(subject,’new’)

Screen Shot 2019-11-16 at 1.14.33 pm

2. Does not contains for text fields

This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.

Filter query= not contains(textfieldschemaname,’value’)

e.g. if I have to check that the ‘Subject/Topic’ of a Lead record does not contains ‘New’ in it; my filter would be not contains(subject,’new’)Screen Shot 2019-11-16 at 1.15.24 pm.png

3.Contains data (Is not blank)

This one is for both text and option set fields

Filter query= textfieldschemaname ne null

Filter query= optionsetfieldschemaname ne null

e.g. if I have to filter where ‘Job title’  contains data or is not blank; my filter would be jobtitle ne null

e.g. if I have to filter where ‘Rating’  contains data or is not blank; my filter would be leadqualitycode ne null

4. Does not contains data (Is blank)

This one is for both text and option set fields

Filter query= textfieldschemaname eq null

Filter query= optionsetfieldschemaname eq null

e.g. if I have to filter where ‘Job title’  does not contains data or is blank; my filter would be jobtitle eq null

e.g. if I have to filter where ‘Rating’  does not contains data or is blank; my filter would be leadqualitycode eq null

5.Contains for option sets

This one is for option set fields like Rating, Lead Source, Industry, Type etc.

Filter query= optionsetfieldschemaname eq optionsetnumericvalue

e.g. if I have to filter lead’s with rating ‘Hot’ (value =1); my filter would be leadqualitycode eq 1

Note: As per my understanding, you can’t check option set label in ODATA filter but you can in filter array.

6.Does not contains for option sets

This one is for option set fields like Rating, Lead Source, Industry, Type etc.

Filter query= optionsetfieldschemaname ne optionsetnumericvalue

e.g. if I have to filter lead’s with rating ‘Hot’ (value =1); my filter would be leadqualitycode ne 1

7.Contains with ‘OR’ on same field

Filter query= contains(field1name,’value1′) or contains(field1name,’value2′)

Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname1 eq optionsetnumericvalue2

e.g. if I have to filter where ‘Job title’  contains ‘Manager’ or ‘Consultant’; my filter would be contains(jobtitle,’manager’) or contains(jobtitle,’consultant’)

e.g. if I have to filter where ‘Rating’  contains either ‘Hot’ or ‘Warm’ data; my filter would be leadqualitycode eq 1 or leadqualitycode eq 2

8. Contains with ‘AND’ on same  text field

Filter query= contains(textfield1name,’value1′) and contains(textfield1name,’value2′)

e.g. if I have to filter where ‘Topic’  contains ‘New’ and ‘Interested’; my filter would be contains(subject,’new’) and contains(subject,’interested’)

9.Filter an option set checking two or more values

Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname1 eq optionsetnumericvalue2

e.g. if I have to filter where ‘Rating’  contains either ‘Hot’ or ‘Warm’ data; my filter would be leadqualitycode eq 1 or leadqualitycode eq 2

Screen Shot 2019-11-16 at 1.16.25 pm.png

10. Filter by checking two different option sets

Filter query= optionsetfieldname1 eq optionsetnumericvalue1 or optionsetfieldname2 eq optionsetnumericvalue2

e.g. if I have to filter leads where ‘Rating’  contains ‘Hot’ and  ‘Lead Source’ contains ‘Advertisement’; my filter would be leadqualitycode eq 1 and leadsourcecode eq 1

Screen Shot 2019-11-16 at 1.17.15 pm

11.Starts with/Begins with

This is for text fields only

Filter query=startswith(fieldname,’startvalue’)

e.g. if I have to filter all Australian leads , I will look at ‘Business Phone’ starts with country code +61; my filter would be startswith(telephone1,’+61′)

e.g. if I have to filter leads from  Australia or New Zealand, I will look at ‘Business Phone’ starts with country code +61 or +64; my filter would be startswith(telephone1,’+61′) or startswith(telephone1,’+64′)

e.g. if I have to filter leads having ‘Business Phone’  from Australia but ‘Mobile Phone’ from New Zealand, I will look at ‘Business Phone’ starts with country code +61 and +64; my filter would be startswith(telephone1,’+61′) and startswith(mobilephone,’+64′)

12.Ends with

This is for text fields only

Filter query=endswith(fieldname,’endvalue’)

e.g. if I have to filter all leads where ‘Website’ ends with ‘.org’; my filter would be endswith(websiteurl,’org’)

e.g. if I have to filter all leads where ‘Website’ either ends with ‘.org’ or ‘.com’; my filter would be endswith(websiteurl,’org’) or endswith(websiteurl,’com’)

e.g. if I have to filter all leads where ‘Website’ ends with ‘.org’ and email ends with ‘.com’; my filter would be endswith(websiteurl,’org’) and endswith(emailaddress1,’com’)

13.Greater than

This is for Numbers and date fields only

Filter query=datefield gt ‘specificdate’

Filter query=datetimefield gt ‘specificdatetime’

Filter query=numberfield gt specificnumber              (No, ” here)

e.g. if I have to filter leads created after 10th August 2019

createdon gt ’10/08/2019′

e.g. if I have to filter leads created after 5AM on 10th August 2019; my filter would be

createdon gt ’10/08/2019 05:00′

e.g. if I have to filter leads created after 5:30AM on 10th August 2019; my filter would be

createdon gt ’10/08/2019 05:30′

Screen Shot 2019-11-16 at 1.18.15 pm.png

e.g. if I have to filter leads created after 5PM on 10th August 2019; my filter would be

createdon gt ’10/08/2019 17:00′

e.g.if I have to filter leads created after 5:30PM on 10th August 2019; my filter would be

createdon gt ’10/08/2019 17:30′

e.g. if I have to filter leads where annual revenue is more than $2000000

revenue gt 2000000 

e.g. if I have to filter leads where annual revenue is more than $2000000 and number of employees is more than 500

revenue gt 2000000 and numberofemployees gt 500

Screen Shot 2019-11-16 at 1.12.15 pm

14.Less than

This is for Numbers and date fields only

Filter query=datefield lt ‘specificdate’

Filter query=datetimefield lt ‘specificdatetime’

Filter query=numberfield lt specificnumber              (No, ” here)

e.g. if I have to filter leads created before 10th August 2019

createdon lt ’10/08/2019′

e.g. if I have to filter leads created before 5AM on 10th August 2019; my filter would be

createdon lt ’10/08/2019 05:00′

e.g. if I have to filter leads created before 5:30AM on 10th August 2019; my filter would be

createdon lt ’10/08/2019 05:30′

e.g. if I have to filter leads created before 5PM on 10th August 2019; my filter would be

createdon lt ’10/08/2019 17:00′

e.g.if I have to filter leads created before 5:30PM on 10th August 2019; my filter would be

createdon lt ’10/08/2019 17:30′

e.g. if I have to filter leads where annual revenue is less than $2000000

revenue lt 2000000 

e.g. if I have to filter leads where annual revenue is less than $2000000 and number of employees is less than 500

revenue lt 2000000 and numberofemployees lt 500

15.Less than or equal to and Greater than or equal to

This is for Numbers and date fields only

Filter query=datefield ge ‘specificdate’

Filter query=datetimefield ge ‘specificdatetime’

Filter query=numberfield ge specificnumber              (No, ” here)

Filter query=datefield lt ‘specificdate’

Filter query=datetimefield le ‘specificdatetime’

Filter query=numberfield le specificnumber              (No, ” here)

e.g. if I have to filter leads created after or on 10th August 2019

createdon ge ’10/08/2019′

e.g. if I have to filter leads created after or at 5AM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 05:00′

e.g. if I have to filter leads created after or at 5:30AM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 05:30′

e.g. if I have to filter leads created after or at 5PM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 17:00′

e.g.if I have to filter leads created after or at 5:30PM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 17:30′

e.g. if I have to filter leads where annual revenue is more than or equal to $2000000

revenue ge 2000000 

e.g. if I have to filter leads where annual revenue is more than or equal to $2000000 and number of employees is more than or equal to 500

revenue ge 2000000 and numberofemployees ge 500

e.g. if I have to filter leads created before or on 10th August 2019

createdon le ’10/08/2019′

e.g. if I have to filter leads created before or at 5AM on 10th August 2019; my filter would be

createdon le ’10/08/2019 05:00′

e.g. if I have to filter leads created before or at 5:30AM on 10th August 2019; my filter would be

createdon le ’10/08/2019 05:30′

e.g. if I have to filter leads created before or at 5PM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 17:00′

e.g.if I have to filter leads created before or at 5:30PM on 10th August 2019; my filter would be

createdon ge ’10/08/2019 17:30′

e.g. if I have to filter leads where annual revenue is less than or equal to $2000000

revenue ge 2000000 

e.g. if I have to filter leads where annual revenue is less than or equal to $2000000 and number of employees is less than or equal to 500

revenue le ‘2000000’ and numberofemployees le 500

e.g. if I have to filter leads where annual revenue is less than or equal to $2000000 and number of employees is more than or equal to 500

revenue le ‘2000000’ and numberofemployees ge 500

Filter array

These are very much similar to what we get in D365 native workflows except for puttin the value ourselves.

1.Option set label

Select the label field dynamically and not the value field. Then specify your label value on the right.

Screen Shot 2019-11-16 at 1.25.11 pm

Screen Shot 2019-11-16 at 1.24.00 pm

2. Option set value

Select the value field dynamically and not the label field. Then specify your option set value on the right.

Screen Shot 2019-11-16 at 1.29.06 pm

3. Text fields

This one is for text fields like Topic, Subject, Phone, City, Street 1 etc.

Screen Shot 2019-11-16 at 1.30.32 pm.png

4. Number and date fields

This is for number and date fields.

Screen Shot 2019-11-16 at 1.32.31 pm.png

Screen Shot 2019-11-16 at 1.33.25 pm.png

Those are enough filters to get you started. 🙂

Hope you find this helpful!

Subscribe to my YouTube

Thanks!

Let’s keep sharing!

#TGIF Episode 1: Getting Started With Microsoft Flow

Hi Readers and Viewers

In this episode of TGIF, we are going to see that how you and me can start our Flow journey. This is a short episodes and will cover the following:

  1. Licensing
  2. Sign up
  3. Start page
    • Enter a process name
    • Check templates
  4. Check out the mobile app
  5. Have you got the reason to use Flows yet?

I have covered licensing very briefly but there’s a lot of history and details about MS Flow licensing here by Jussi: https://jussiroine.com/2019/01/the-comprehensive-licensing-guide-to-microsoft-flow-and-powerapps/

or you can visit https://flow.microsoft.com/en-us/pricing/?currency=AUD for more details

Here’s the video:

Let me know your thoughts!

Subscribe to my channel and follow my blog.

Thanks!

Let’s keep sharing!

New Series #TGIF, Thank God It’s Flow

Hi Readers and Viewers

Thanks for your support!

As promised, I am coming up with a new series #TGIF. It took some time to decide upon a name. I will be releasing episodes every friday starting 8th November 2019. Today is just the teaser and my thoughts.

In this series we will be covering everything flow mainly the following:

  • Working with Flow
  • Most common queries/filters
  • No code only
  • When and how
  • Integration scenarios
  • Business use cases
  • All about flow and everything about flow except coding

Do watch the teaser here:

Love Love!

Let’s keep sharing!

 

How To Update An Opportunity From Opportunity Close And Send An Email on Opportunity Close, Using MS Flows? (No Code Solution)

Hello Readers & Viewers

In this video, i am showing ‘How to send an email with information from Opportunity Close form, using MS Flows?’. This flow will also help you to update opportunities from opportunity close form, it’s the same concept.

Many of you might know that using the native workflow functionality of Dynamics 365 you can’t achieve this; as ‘opportunity close’ is not in the entity list of workflows. Opportunity close is a type of activity and ‘Activities’ are also not available in the workflow entity list except for a few common one’s like calls, email, tasks, appointments etc.

Doesn’t matter, does it?

With Microsoft Flows you can achieve this and you can do things like:

  • Send an email on opportunity close
  • Send an email on opportunity close with information from opportunity close form
  • Update an opportunity from opportunity close form

We used to do all this with a plugin earlier and now its so easy.

Watch the video here:

Hope it helps!

Happy CRMing!

 

Patient Management App

Hello Readers

In this blog, I am going to show you how you can utilise Power Platform and quickly create an app for your clinics. I have used both Canvas and Model driven App. Let’s have a look:

Canvas App

This app is made of 9 screens:

  • Home Screen
    • Patient’s Home Screen
      • Patient Registration Screen
      • Book a Consultation
    • Doctor’s Home Screen
      • My Patients
      • My Consultations
        • Consultation Details
        • Add Prescriptions

Some screenshots from the app:

                                    Home                                          Patients Home 

 

                       Doctors Home                                           Adding Prescription

                      My Consultations                              My Patients

Note: Video coming soon so you can see the entire app working. Keep an eye on YouTube

Features of the app:

  • Allow patient registration at the clinic reception
  • Allow patients to book consultations at the clinic reception
  • Allow Doctors to look at their Consultations for the day
  • Allow Doctors to add prescriptions
  • Allow Doctors to send prescription to patients via email (checkbox)
  • Allow Doctors to send Medical Certificate via email (checkbox)
  • Allow Doctors to send prescription to the pharmacy of patient’s choice (on demand only)

Model Driven App

This app is made of the following components:

Clinic Management (Dashboard)

Screen Shot 2019-09-11 at 1.13.19 pmScreen Shot 2019-09-11 at 1.13.40 pm

Screen Shot 2019-09-11 at 1.13.59 pm

Screen Shot 2019-09-11 at 1.14.13 pm

Patients (Contacts)

Screen Shot 2019-09-11 at 1.18.18 pm

Pharmacies (Accounts)

Drugs & Dosage (Products)

Consultations (Appointments)

Screen Shot 2019-09-11 at 1.15.44 pm

Prescriptions (Custom)

Screen Shot 2019-09-11 at 1.49.49 pm

Features of the app:

  • Track your patients at one place
  • Track the performance of your clinic with dashboards
  • Add medicines to the catalogue on the go
  • Track history of patients prescriptions
  • Store copies of Medical Certificate and prescriptions without scanning (Word Templates)
  • *Extension: Use MS Flows to convert documents to PDF
  • Set up email templates for regular communication with your patients

 

Hope it helps!

Happy CRMing!