Building a flow to get factory's production data for working days only. It runs everyday at 9:30 AM.

[Seil]

We have an excel file with 4 columns in the order:

1. Dates of all the Sundays, 3rd Saturdays, and other mandatory governmental holidays. Column name - Holiday Date
2. Days of all the Sundays, 3rd Saturdays, and other mandatory governmental holidays. Column name - Off Day
3. Dates of all the previous working date based on column #1. Column name - Previous Working Date
4. Days of all the previous working date based on column #1. Column name - Previous Working Day

   Now, my manager has asked me to implement the following in the flow: 

   Upon entering the flow, check if today is a holiday. 

   1. If not, then exit the flow and don't do anything else.
   2. If yes, then compute yesterday and declare a variable called Last working date. Check if yesterday was a holiday.
      1. If not, then set Last Working date as yesterday
      2. If yes, then identify the last working date corresponding to yesterday from the excel sheet. This date should now be set as the value for the variable Last working date.

[harshdeol · Accepted solution]

Hi @Seil , I created a sample flow based on the given requirements.

Here is my Sample Excel file that contains the Sundays, 3rd Saturdays, and other holidays (New Year and Republic Day in my case) From January to March.

//Excel File:

//Complete Flow:

//Step 1:  Recurrence 

Add recurrence Trigger and configure it to run at 09:30 AM.

Note: Please make sure to change the Time Zone.

//Step 2: Initialize a variable of String Type

//Step 3: List rows present in a table

This action is used to get Excel Table rows. The filter query is added to filter the data where Holiday Date= Today's Date.

If Today is Holiday, this action returns the row otherwise it returns an empty array [].

Filter Query expression:

HolidayDate eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

Note: Please make sure to select the DateTime Format as ISO 8601.

//Step 4: Condition and Set Variable

This Action is used to check if Today is Holiday or Not.

If 'List rows present in a table' gives empty array [] means No HolidayDate found and Condition goes to "If Yes" Section (Do Nothing).

If there is a row means, there a is Holiday and Condition goes to "If yes" Section and Set the variable "Last Working Day" to previous day's date (TodayDate-1).

Condition expression:

empty(outputs('List_rows_present_in_a_table')?['body/value'])

Set Variable value expression:

addDays(outputs('List_rows_present_in_a_table')?['body/value']?[0]?['HolidayDate'],-1)

//Step 5: List rows present in a table 2

Add another list rows action to get rows and check if previous day Date is in the HolidayDate column.

Filter Query expression:

HolidayDate eq '@{formatDateTime(variables('LastWorkingDate'),'yyyy-MM-dd')}'

Note: Please make sure to select the DateTime Format as ISO 8601.

//Step 6: Condition and Set Variable

Use another condition to check if Yesterday's date is Holiday or not. if yes, then set the variable "LastWorkingDate" to value of yesterday's "Last Working Date" column from the excel sheet.Otherwise, Do nothing. 

Condition expression:

empty(outputs('List_rows_present_in_a_table_2')?['body/value'])

Set Variable Value expression:

outputs('List_rows_present_in_a_table_2')?['body/value']?[0]?['Previous Working Date']

<<To test and check the value of variable, add a "Compose" action at the end and pass the value of "LastWorkingDate" variable from Dynamic Value.>>

Please try it at your end and let me know if you further details are required.

-------------------------------------------------------------------------------------------------------------------------

If your question got an answer, please click "Accept as Solution "✅, If you liked my answer, please hit the "thumbs-up" 👍button.
Thank you,
Harsh Deol

[Seil]

Thank you so much for the effort!
I'll implement the changes and get back here with the results.

[harshdeol]

Hi @Seil , just wanted to check the update on this.

If you’re facing any issues or have any questions please feel free to ask.

-------------------------------------------------------------------------------------------------------------------------
If your question got an answer, please click "Accept as Solution "✅, If you liked my answer, please hit the "thumbs-up" 👍button.
Thank you,
Harsh Deol

[Seil]

Hi @harshdeol,

Thank you for your awesome solutions and continued follow-ups.

The flow worked on all production days I've tried it on so far (Wed-Sat).
However, it failed this morning because of my mistake.

I tried to run step 4 with utcNow instead of the empty and addDays functions you had posted. It was an error because utcNow can only take in one parameter, whereas the other functions have more capability.

Error:
Unable to process template language expressions in action 'Set_variable_2' inputs at line '0' and column '0': 'The template language function 'utcNow' can be invoked with no parameters or with an optional format parameter. The function was invoked with '3' parameter(s). Please see https://aka.ms/logicexpressions#utcnow for usage details.'.

What I had one instead. Need to correct this now.

[harshdeol]

Hi @Seil ,

Just wanted to know the scenario why you are trying to run step 4 with utcNow. What actually the requirement is?

And what you want to configure now?

In the Set Variable 2, you're using the incorrect expression:

If you want to set last working date as yesterday's Date (utcNow-1). Please use below expression:

addDays(utcNow(),-1,'yyyy-MM-dd')

Thank you

[Seil]

You are correct. 
I am learning flows and so I wanted to experiment. The experiment told me that utcNow won't be valid for that, so I'll go with empty and addDays now😀

[Seil]

The latest error is about Step #3.

I tried adding the ODATA filter query and got the error:
Syntax error at position 12 in 'Holiday Date eq '2024-02-04'.

Is it because I am using the column name Holiday Date instead of HolidayDate? The difference is a space b/w the 2 words.

[harshdeol]

Yes, It is because of column name.

Currently ODataFilterQuery expects the Column Name without Space.

[Seil]

Daaaamn!! I'll modify it then.