Power Platform Community Forum Thread Details

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 Blog.

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

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>

</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)

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.

Categories:

Building Power Apps