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.

[ConnorDeasey]

Hi Sidhant, not sure if its what your looking for but could be worth taking the Vacation Booking App course on the Collab365 Academy - Plenty of little nuggets to help you here that you might find useful. https://academy.collab365.com/offer/c365-chg-vacation-app/powerplatform-vacation-challenge-ss/

[v-jefferni]

Hi @Sidhant_02 ,

The order of the two Apply to Each controls needs to be reversed. 

Best regards,

[Sidhant_02]

Hi @ConnorDeasey ,
Thanks for sharing the link, I did check this the only issue was this course was paid if there are free resources do let me know.

Regards,
Sidhant.

[Sidhant_02]

Hi @v-jefferni  & @ConnorDeasey ,
Tested the new flow (that I shared with you earlier) the file that was created in the OneDrive had triple amount of rows the group has only 56 individuals (in the Teams Group) but in the file it had 258 rows

and 12/01/2024 to 14/01/2024 were all merged in column 

And the other thing was the name, email and date fields were empty and the other thing was the 'Delay' that I had added a delay of 5 min just to have everything in sync but this delay was extended way beyond the specified time limit.

Anything that I have missed or done incorrectly

Regards,
Sidhant. 

[Sidhant_02]

Hi @v-jefferni ,
Did interchange the two loops as you suggested and now I am getting the names but for every 4 rows the value is repeated 

And the other thing after the first two dates (10/1/24 and 11/1/24 the remaining three dates are occupied in the same row). And every time I tried testing it it gave the TimeOut message on the flow (which I had shared earlier in my reply)

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

The issue with the duplication seems to be caused by a row being added for each column.

A way to resolve this is to move the contents of the nested loop inside the parent loop and then get rid of the nested loop. Then change the contents of the Compose action to something like the below where you need to replace anything in <> to a dynamic reference or expression:

{
   "SNo": '<varCounter>',
   "Employee Name": '<List of member...>',
   "Employee Email": '<List of member...>',
   "<split(...)[0]>": '<if statement to check if it's a holiday>',
   "<split(...)[1]>": '<if statement to check if it's a holiday>',
   "<split(...)[2]>": '<if statement to check if it's a holiday>',
   "<split(...)[3]>": '<if statement to check if it's a holiday>',
   "<split(...)[4]>": '<if statement to check if it's a holiday>'
}

The idea is that you capture the values for all headers per employee in one go, instead of per header.

The issue for having the merged rows may be a missing separator between the dates. Please check if the header string does contains the right separators.

[Sidhant_02]

Hi @BCBuizer ,
Thanks for the reply you mentioned <> to replace with the dynamic reference or expression 

"<split(...)[0]>": '<if statement to check if it's a holiday>'

Are you referring to something like this

In the split (...) what is the expression and the if statement : What's the expression (is it the same that I have used earlier?)


Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

For the split use this: 

by adding the square brackets and numbers, you can select the items in the array that is returned by the split function. The five items you should select are the ones that have the date as a value.

The if statement uses the same expression as above to check is your Holidays table contains that date to determine if it is a holiday. Again in pseudo expression:

If(
	contains(
		<dynamic reference to 'List row presen in a table', date column>,
		<Split(...)[0]>
	),
	'Holiday',
	''
)

[Sidhant_02]

Hi @BCBuizer , 
Sorry could not reply early (was busy with some other task), so are you referring something like this:

I removed the nested loop and in for loop now just have 3 actions and the expression for the last 4 columns (key-value pair) is:

"split(outputs('Headers',','))[0]" : "if(contains(outputs('List_rows_present_in_a_table')?['body/value'],split(outputs('Headers'))[0]),'Holiday','')"

Is it correct ?.
As you had mentioned 

<dynamic reference to 'List row presen in a table', date column>

So to refer the date column do I need to add an additional expression like:
if(contains(outputs('List_rows_present_in_a_table')?['body/value/Date'],split(outputs('Headers'))[0]),'Holiday','')

OR:

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

Another doubt I had was you did say by adding square brackets and numbers we can select the items in the array returned by the split function so my headers is something like this:

So in the split function should I start from index [3] as from there the dates start?.

If there are any additions please let me know @BCBuizer , @v-jefferni , @ConnorDeasey .

Regards,
Sidhant.

[BCBuizer]

Hi @Sidhant_02 ,

For your 3 points:

1. That seems almost correct: For the values (right side of the colons) replace all double quotes, with single quotes. Also I remember you need 5 key-value pairs for each working day of the week.

2. A quick way I found to make the if function work is by adding a Select action before the apply to each loop. Set the From field to @{outputs('List_rows_present_in_a_table')} and the Map field (in text mode) to @{item()

['Date']}. The output of this can then be converted into a string which will work with the contains operator.

In all this will make the input of your compose action look something like the below:

{
   "SNo": '<varCounter>',
   "Employee Name": '<List of member...>',
   "Employee Email": '<List of member...>',
   "@{split(outputes('Header'),',')[0]}": '@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[0]),'Holiday','')}',
   "@{split(outputes('Header'),',')[1]}": '@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[1]),'Holiday','')}',
   "@{split(outputes('Header'),',')[2]}": '@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[2]),'Holiday','')}',
   "@{split(outputes('Header'),',')[3]}": '@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[3]),'Holiday','')}',
   "@{split(outputes('Header'),',')[4]}": '@{if(contains(join(body('Select'),', '),split(outputs('Headers'))[4]),'Holiday','')}',
}

(The @{}s should make it possible to copy the above directly in the editor without having to indicate those are expressions).

3. It is an interesting technique, but I don't see how this adds value to your scenario. My recommendation is to stick with the above and push to make that work correctly.