Survey123 Power Automate to Create Report "Where" clause

karyal@stalbert.ca_stalbert

I am struggling with Power Automate Survey123 create report to get the results only for the previous month. As it can be done easy for the last x days but I want for the last month. 

I tried different SQL syntax in 'Where' clause. I can do for last 30 days or any number of days but not all the months are 30 days. There should be an easy way to do it but I kept failing. any suggestion?

BETWEEN CURRENT_TIMESTAMP - 30 AND CURRENT_TIMESTAMP successfully creates a report with results for the last 30 days. But I need to create report for previous month automatically to create a report every month.

Thank you in advance

VivekAcharya

@KhemAryal, I've gotten this to work by calculating the dates in Power Automate and using dynamic expressions to merge them into Filter WHERE clause. Please let me know how this solution works for you.

• In your Flow, add a "Subtract from time" action with Interval: 1 and Time Unit Month and the expression:
  

 

formatDateTime(utcNow())

  
This step will grab the exact date 1 month ago - used as reference.
Outputs: "body": "2023-06-12T04:30:10.5467055Z"

 

• Add another step to initialize variable. Name: StartOfPreviousMonth , Type: String , Value expression: 
  

 

formatDateTime(startOfMonth(body('Subtract_from_time')), 'yyyy-MM-dd')

This step uses the earlier reference and determines first day of that month.
Value is 2023-06-01

 

• Initialize another variable. Name: StartOfCurrentMonth , Type: String , Value expression: 
  

 

formatDateTime(startOfMonth(formatDateTime(utcNow())),'yyyy-MM-dd')

This step determines start of the current month.
Value is 2023-07-01

 

• Next, the Feature WHERE clause is set up to reference previously initialized variables:

 

Date_and_time_collected_Data BETWEEN '@{variables('StartOfPreviousMonth')}' AND '@{variables('StartOfCurrentMonth')}'​

Ensure single quotation characters are placed around dates and spaces exist between SQL keywords.
 

 

• Running the flow succeeds and you can observe how Feature WHERE clause gets formatted using the computed values.
  

   

I don't think complex date functions from are supported in this Feature WHERE clause syntax - seems quite limited SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation.