Solved: Adding Holidays and Notifying users

Sidhant_02 · ‎01-08-2024

Hi everyone,
So few months back I had designed a flow (Power Automate flow) that collects all users from a Microsoft Teams group (with headers in the excel sheet: Name, Email, Dates of the week) this file is created at the start of the week (i.e. Monday at 9:30 am IST) and a message is shared on the Teams channel that the file is being created for the week please enter your work mode for the current week (WFO - Work From Office and WFH - Work From Home).
But there were some issues like if a holiday comes in (like on Monday like for New year 1st Jan) in this case no user will login and fill in the timesheet (as it's a Holiday 😉) so in such cases I wanted the flow to fill it as 'Holiday'.
So my flow looks like this:

This flow creates an Excel which looks like this:

So I created a HolidayList excel which has all the list of holidays as per my region

So now I was thinking how to refer this (holiday list sheet) and if there is any date that is present and matches with one of the dates in the week (like 26th Jan is a Republic Day which is on Friday) so for that week on Friday the flow should add the value to all rows for that day (for all users) as 'Holiday'/ 'Holiday_name (Republic Day) Holiday'.

And the next thing that I was thinking of sending a reminder (at-least for two days like Tuesday,Wednesday: For users who have not filled the time sheet 'Please fill your work mode time sheet for the week' and next week (the previous week timesheet should not be editable for users)

So if anyone has any idea about this please let me know it would help and if any more information is needed do let me know.

Regards,
Sidhant.

BCBuizer

Hi @Sidhant_02 ,

It seems the second argument in the right set of split functions was missing:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{split(outputs('Headers'),',')[0]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[0]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[1]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[1]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[2]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[2]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[3]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[4]),'Holiday','')}"
}

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

BCBuizer

Hi @Sidhant_02 ,

In the Excel file, try to format the Date column as text instead of a date. I think that should resolve the issue.

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

BCBuizer

Hi @Sidhant_02 ,

I see you still left the wrong date formatting in your test item:

Changing the value for this item in the Date column of your Excel to "20/04/2024" should result in a positive hit.

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

v-jefferni · ‎01-08-2024

Hi @Sidhant_02 ,

I think it will be much easier to add holidays to this Excel table when the table is created. You already have the column names in the flow, so it's possible to compare column names with the Dates in holidays table and get the holiday name in Notes column. In Add Teams member to Excel, you should be able to add the holiday along with members.

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Sidhant_02 · ‎01-08-2024

Hi @v-jefferni ,
Could you please explain with an example or recreating what you mentioned and in my existing flow where exactly the comparison with the new list (Holiday list) should be done.

In the loop this is what happens:

Regards,
Sidhant.

v-jefferni · ‎01-08-2024

Hi @Sidhant_02 ,

What's the output of Header?

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Sidhant_02 · ‎01-08-2024

Hi @v-jefferni ,

So basically I will just give you an overview what the flow does:
- The flow is triggered on Monday every morning at 9:30 am (IST).
- Then a variable named varCounter is intialised to zero (as a counter: to have count of the individuals)
- The Header variable is used to store the headers which are 'Name, Email, Dates_of_week' (like this week: 08/01/2024: current date and for other dates I am just increasing the values like for 09/01/2024 [currentdate +1 ], for wednesday: [currentDate + 2] and so on.

- In Get File Content action I am pointing to an empty excel file named EmptyExcel.xlsx

Then in the same onedrive file location (Location name: Template Folder), creating the excel file.

- Using the create table action all the headers are being added to the same file (that was created earlier in the previous step)

- To get the members details have used 'List members' action that points to the group which is used as reference (here it is named 'Attendance')

- Using 'Apply to each loop' all the members are added to the excel sheet

- Have added a delay this is to get all the team members (as earlier I observed few names were skipped)

- Then using the 'Get file content 2' I am referencing the file that is created in OneDrive and using the same file I am creating an Sharepoint file in the respective SharePoint site (here its named 'Attendance') and at last I am notifying by posting it in the Teams channel.

If you need any more information do let me know.

Regards,
Sidhant.

v-jefferni · ‎01-08-2024

OK, since it seems that there won't be two holidays in one week, you could use a Filter array from the holidays table by each column from split(outputs('Header', ',')):

Compose 2 is the Row Compose on your end, just add a new row with additional column name and value:

"@{items('Apply_to_each')}": "@{first(body('Filter_array'))?['Notes']}"

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Sidhant_02 · ‎01-08-2024

Hi @v-jefferni ,
So should I add be adding 'List rows present in a Table' action (that will list all the rows from the second excel which in my case is 'HolidayList' with the columns : Date and Notes)

In the apply to each loop action instead of using the split(outputs('Headers')) I am making use of the outputs from the Teams 'List of members'

So should the filter action be added after the counter is incremented but I am still unsure how is 'Holiday' being appended to the specific rows if there a specific date that matches with the Holiday List
Like:
On January 1 2024 this was the empty excel file with all the employee names was generated

And Jan 1,2024 was a Holiday (assume it was mentioned in the Holiday List excel sheet that I have mentioned earlier), so during the creation of the template file itself all the columns of 1/1/2024 should have value as 'Holiday'.
So the flow working that I shared with you yesterday (as a reply) does this i.e. creation of the excel template file with all the names and dates of the current week.
The other thing is if there is no Holiday or there is no match (i.e. no date in the current week) is present in the Holiday list then in that case it should work as it doing currently like it will have the headers and no value in any columns, (so do I need to add the same in the 'No' section that is been done now

)

A bit confused, if possible can we have a short call to discuss about this as per your convenience we can have a call that suits both of us as per our time-zones mine is IST

Regards,
Sidhant.

v-jefferni · ‎01-09-2024

Hi @Sidhant_02 ,

The list rows action is needed and can place it anywhere above your apply to each control. The Apply to each in my flow, you need to add the entire block into your Apply to each too.

I know it's complicated and hard to explain in words, but I have no right to make a call. You can raise a ticket on support page and paste the link of this thread so MS support engineer could understand your scenario and explain the solution to you.

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Sidhant_02 · ‎01-09-2024

Hi @v-jefferni ,
So I need to place an action element 'List rows' that gets the details of the Holidays list. So now I just wanted to ask the 'Apply to each block' that you shared earlier (which I need to use in my loop) so if there is any match found (date like 15/1/2024 is present in as a Holiday in the holiday list and in the next week run will the new apply to each loop that you shared will add 'Holiday' to each and every column for that particular date)

Regards,
Sidhant.

Sidhant_02 · ‎01-10-2024

Hi @v-jefferni ,
I understood that in the loop (Apply_to_each) we are comparing the date column with each date from the excel, so I added the loop structure that you had shared yesterday which looks like this:

In the compose Action after specifying the values for the respective columns (EmployeeName, EmployeeEmail) it did another Apply_to_Each (2nd loop) and when I did try to create excel file 'The table was not found (which should be Table1: here)' so for now I have hardcoded it (but not sure whether it is correct or not).
If there is no match (there is no match with the date from the HolidayList with the current week (Date Headers)) then it should only add the three columns i.e. SNo, EmployeeName, EmployeeEmail, so is that right

The new flow:

Just added the delay of 5 minute (as there more loop to execute than my older flow) and then referencing the same file created on OneDrive to create a file in the Teams channel (here 'Attendance').

So is this what you were saying?

Regards,
Sidhant