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.

[BCBuizer]

Hi @Sidhant_02 ,

It almost seems as if, when the table gets created, the formatting of the column names gets changed to drop the lead 0s for the day and month. As a result, there is no match between the column name and the key in the record that is inserted.

Can you try wrapping the expressions in the Header action in a String() function to convert them to text?

[Sidhant_02]

Hi @BCBuizer ,
Were you meaning something like this:

Because in the list for test purpose I did add one more Holiday on 06/05/2024

But observed the same issue:

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

Yes, that was what I meant, but it doesn't seem to make a difference...

You are sure the headers field in the "Create table" action is referencing the "Headers" action, right?

[Sidhant_02]

Hi @BCBuizer ,
Yes the create table action does refer to the Header output

And the loop action:

Even I am not able to understand what's going wrong?

Regards,
Sidhant.

[BCBuizer · Accepted solution]

Hi @Sidhant_02 ,

So it seems that even with the header values set to a "dd/MM/yyyy" format, when creating the table, they get changed to a "d/M/yyyy" format. Perhaps you can try updating the expressions in the Header action to use the latter format.... Please note that you'll then have to match the same formatting you Holidays Excel file as well.

[Sidhant_02]

Hi @BCBuizer ,
So you are saying currently our format is dd/MM/yyyy but the system while creating the table updates it to d/M/yyyy. Now I will need to incorporate the same format in my Holidays like:

And in the Power Automate flow:
(Header section changes for the date format have modified it to d/M/yyyy in place of dd/MM/yyyy) 

So when I tested the flow (manual Test) got the expected results:

Regards,
Sidhant.

[Sidhant_02]

Hi @BCBuizer ,
Just had one minor issue with the format that we have incorporated in the flow i.e. d/M/yyyy (in place of dd/MM/yyyy) so for the first week of June run (03/06/2024 to 07/06/2024) I observed that using the existing format it appended the 'Holiday' text for 07/06/2024 which it should not have but after observing the Holiday list I observed that for Holidays of 2 digits it was cutting out the first digit (as per the new format)

So the flow was considering only the date from second digit part (i.e. from 7/6/2024 instead of 17/6/2024) which is as expected the format.  So now for Holidays with 2 digits (like 17/06/2024 basically 'dd') how do we incorporate that as earlier we had set it to: 'dd/mm/yyyy' but as you know the automate flow was converting it to d/M/yyyy (from dd/MM/yyyy). 

Any inputs that you have for this.

(In the actual sheet)

Apologies but get some issues keep creeping in 😉 (part of building process) got a feedback about this from few colleagues.

Regards,
Sidhant.

[Sidhant_02]

Like is there any way we can avoid appending the Holiday for incorrect dates (currently it is working as expected as per the format that we have mentioned). If you guys @BCBuizer  @v-jefferni & @ConnorDeasey    come across any solution/methods please let me know it will help.

Regards,
Sidhant.

[BCBuizer · Accepted solution]

Hi @Sidhant_02 ,

Let's try to add a space in front of the date before checking if it is a holiday:

{
  "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'),', '),concat(' ', split(outputs('Headers'),',')[0])),'Holiday','')}",
  "@{split(outputs('Headers'),',')[1]}": "@{if(contains(join(body('Select'),', '),concat(' ', split(outputs('Headers'),',')[1])),'Holiday','')}",
  "@{split(outputs('Headers'),',')[2]}": "@{if(contains(join(body('Select'),', '),concat(' ', split(outputs('Headers'),',')[2])),'Holiday','')}",
  "@{split(outputs('Headers'),',')[3]}": "@{if(contains(join(body('Select'),', '),concat(' ', split(outputs('Headers'),',')[3])),'Holiday','')}",
  "@{split(outputs('Headers'),',')[4]}": "@{if(contains(join(body('Select'),', '),concat(' ', split(outputs('Headers'),',')[4])),'Holiday','')}"
}

The reason this would work is because ', ' is used as a separator, meaning all date should have a space in front of them. Since ' 17' does not contain ' 7', the false positives should not happen again.

This is the updated input for the Compose action.

[Sidhant_02]

Hi @BCBuizer ,
Nice idea, did try it out and it worked as expected:
I added two Holidays in the Holiday List (named Test2 and Test3)

(09/07/2024: Single digit and 12/07/2024: double digit) and made the required change (i.e. added concat function with ' ' just before checking if its an Holiday from the Holiday list)

Updated Compose action Expression (in Add Team member to Excel loop)
{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
  "Employee Email": @{items('Add_Team_Member_to_Excel')?['email']},
  "@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),concat(' ',split(outputs('Header'),',')[3])),'Holiday','')}",
  "@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),concat(' ',split(outputs('Header'),',')[4])),'Holiday','')}",
  "@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),concat(' ',split(outputs('Header'),',')[5])),'Holiday','')}",
  "@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),concat(' ',split(outputs('Header'),',')[6])),'Holiday','')}",
  "@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),concat(' ',split(outputs('Header'),',')[7])),'Holiday','')}"
}

On Testing the flow got the expected result. 
Once again thanks @BCBuizer  for helping me out really appreciate it 😊. Hope now I need to reply back on this post again 😉.

Will mark your reply as Solution.

Regards,
Sidhant