cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbastro
Helper I
Helper I

Run Power automate flow by passing empty string variable

Hi All,

 

I built a flow in power automate to run Sharepoint list count by following Power Apps Aggregate Functions – No Delegation warnings with Sum, Average, Count | Reza's...

In Power apps, I have 3 variables pass to the flow which are 3 different filters as below.

bbastro_0-1631073952406.png

If all three filters are selected, I am able to get the count without any issue.  The problem that i am facing currently is, the region and supplier name filters are not mandatory, there might be an empty string that will pass to the flow that I call.  If the empty string pass to the flow, my CAML query inside the flow will treat the empty strings as "empty" from the SharePoint list value which give me an incorrect count.

 

Below is my CAML query under the send HTTP request actions

{
"parameters":{
"ViewXml": "<View><Query><Where>

<And><Eq><FieldRef Name=\"Approval_x0020_Status\"></FieldRef><Value Type='Text'>Failed</Value></Eq>

  <And><Eq><FieldRef Name=\"Season_x0020_Year\"></FieldRef><Value Type='Text'>@{variables('VarFilterSeason')}</Value></Eq>

    <And><Eq><FieldRef Name=\"Sourcing_x0020_Region\"></FieldRef><Value Type='Text'>@{variables('VarFilterRegion')}</Value></Eq><Eq><FieldRef Name=\"Supplier_x0020_Name\"></FieldRef><Value Type='Text'>@{variables('VarFilterSupplier')}</Value></Eq>

    </And>

  </And>

</And>

</Where></Query><ViewFields><FieldRef Name =\"ID\"/></ViewFields><RowLimit>1</RowLimit><Aggregations><FieldRef Name=\"ID\" Type=\"COUNT\"/></Aggregations>
</View>",
"RenderOptions":2
}
}

 

If the filter without any items selected, it has to be "All items" instead of an empty value.  What is the best way to exclude variables with the empty string from the CAML query? 

I had been thinking to use conditions to check if the variable is empty and compose the query.  Since the flow is running in 4 parallel actions with 4 different status value hard codes in the SEND HTTP body (Above CAML query code highlight in RED), also the number of conditions required in the CAML query depends on the number of empty variables that pass to the flow.  It is going to be cumbersome to applying IF conditions in the flow.

(My flow overview)

bbastro_1-1631074304405.png

The other way I can think of is to create 3 sets of flow and control which flow to run from power apps. 

Assumption (filter 2 will never blank)

  • If filter 3 is blank, run flow that query filter 1&2
  • If filter 1 is blank, run flow that query filter 2&3
  • If FIlter 1 & 3 is the blank, run flow that query filter 2
  • If All filter selected, run flow that query all filters.

I am looking for if there is an easier way to implement my logic, all I want to do is just get the records to count by filters without the delegation issue, as my SharePoint list recordset has more than 5000 records, Get rows is not going to be an option for me.

 

 

1 REPLY 1
gabibalaban
Dual Super User
Dual Super User

@bbastro ,

use a single get rows action and in filter query build a dynamic query like

column1 eq ‘value1frompowerapps’ and if(empty(‘value2frompowerapps’), true, column2 eq ‘value2frompowerapps’)

and so on…

The if part you can build it by using expression tab of the dynamic content.

 

hope it helps !

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,070)