Adding Holidays and Notifying users

[Sidhant_02]

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.

[Sidhant_02]

Hi @BCBuizer ,
I did changed the format to Text for the Date column in the Excel

(Just added 20/4/2024 : Friday as holiday for test purpose) and ran the flow but there was no change so when I checked the compose action, I observed it was directly taking the else part

Updated Compose block code: (That is getting all the users data from the Teams group only not populating the Holiday text in the respective column, even after providing the neccessary condition in the value {key-value pair}

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Apply_to_each')?['displayName']}",
  "Employee Email": "@{items('Apply_to_each')?['email']}",
  "@{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','')}",
  "@{split(outputs('Headers'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[5]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[6]),'Holiday','')}",
  "@{split(outputs('Headers'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Headers'),',')[7]),'Holiday','')}"
}

So any guess that what might be the issue?.

Regards,
Sidhant.

[BCBuizer · Accepted solution]

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.

[Sidhant_02]

Hi @BCBuizer ,
Oh I almost forgot about that thanks for pointing that out. I made the update in my excel

And when I ran the flow, I got the expected o/p that I needed:

The excel :

Thank you for helping me in figuring this out and getting the expected response the query replies continued for quite a while but in the end we finally were able to figure out things and get it done. I will mark the latest compose action as solution.

Thanks & Regards,
Sidhant.

[Sidhant_02]

Hi @BCBuizer ,
Today when the flow ran on its usual execution time (9:30 am).  This week on 01/05/2024 is a Holiday as per the Holiday list excel:

So when I checked the flow in successful run I am able to see the Holiday for 01/05/2024 which looks as follows (for all users)

(The above image shows 'Holiday' is shown or inserted for one of the user Rashmi)

But when I was opened the file to enter work mode for this week, I observed that the 'Holiday' placeholder text was not added for 01/05/2024 column in the Excel,.

So I am not sure why its not working as last week when I tested the flow I was able to see the ''Holiday", but somehow the Holiday test is not appended into the sheet. 
Any idea what is the problem here, (The query is marked solved but this was unexpected so thought that I mention the same).

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

Are you sure you are checking the right file? I see that besides the missing placeholder, the formatting of the dates in the column headers are also off: 

Perhaps you are looking at a file that got created in the past?

[Sidhant_02]

Hi @BCBuizer ,
I did recheck but no the screenshot that I shared earlier was of the file that got created today (i.e. 29/04/2024) 

(The above file is in OneDrive shared folder referencing the same file to create the file in SP and that's saved in the Teams channel; which you can see in the flow). So as of now I had to manually enter the text 'Holiday' 01/05/2024

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

The flow run details just don't seem to match the screenshot of the table, hence something external, like checking the wrong file, must be causing this. Unfortunately I can't tell from this end what that thing may be. 

What I'd suggest is re-run the flow and see the results as they are coming in.

[Sidhant_02]

HI @BCBuizer ,
As you suggested, I re-executed the flow as you said.

Kindly ignore the formatting part but now the above is the latest file run generated after executing the flow (manually) but still it has not appended the 'Holiday' text for tomorrow (even though its an Holiday as per the Holiday list excel)

The Power automate flow:

As you can in the flow we can clearly see the 'Holiday' text is present for tomorrow (01/05/2024) but the same is not getting reflected in the excel.

Any idea about this?.

Regards,
Sidhant.

[BCBuizer]

HI @Sidhant_02 ,

Can you please share the details for the action in which the headers are define and for the Create table action? I suspect the key to resolution is in there due to a mismatch in keys / column names: 

 vs.

[Sidhant_02]

Hi @BCBuizer ,
Sure, here are the details:
Header component

Create table action:

Regards,
Sidhant.