Power automate flow for attendance tracking and Send an reminder on teams

[MaverickNK]

I am attempting to create a Power Automate cloud flow that will send a reminder on Teams if an employee fails to update their attendance on an Excel Online sheet. The sheet contains a sample dataset that includes columns for Name, Email, Employee ID, and daily attendance for each day of the month, Below is the sample data for reference

Name

Email

Emp Id

1-Mar

2-Mar

3-Mar

4-Mar

5-Mar

6-Mar

7-Mar

8-Mar

9-Mar

10-Mar

11-Mar

12-Mar

13-Mar

14-Mar

15-Mar

16-Mar

17-Mar

18-Mar

19-Mar

20-Mar

21-Mar

22-Mar

23-Mar

24-Mar

25-Mar

26-Mar

27-Mar

28-Mar

29-Mar

30-Mar

31-Mar

ABC

ABC@abc.com

12345678

P

WO

WO

P

P

P

P

P

WO

WO

P

P

P

P

P

WO

WO

P

P

P

WO

WO

WO

WO

XYZ

XYZ@abc.com

87654432

P

WO

WO

P

P

P

P

P

WO

WO

P

P

P

P

P

WO

WO

P

P

P

WO

WO

WO

WO

The flow should scan the table and identify any date columns where attendance has not been marked and add the name of the employee to an array. The flow should then post a message in a Teams channel or group that includes the names of all employees who missed marking their attendance.

Your support would be appreciated...Thank You!

[Rhiassuring]

Do you want to send this daily? Weekly? Monthly?

[MaverickNK]

Daily, End of the Day

[Rhiassuring]

@Chriddle  @Expiscornovus @Pstork1 

Hey friends, I was trying to figure this one out and I got stuck on trying to Filter an Array on a variable. The steps for the most part seem easy and then I came to a grinding halt after spending 3 hours trying to figure that one piece out...... 

Need to filter the array by utcNow('dd-MMM') -- I've tried putting it in a compose, wrapping it in a string, putting it in a variable, changing it to an item()?with the output at the end... 

How do we trick PA into taking a column name that is a variable???

[Pstork1]

I don't think you'll be able to specify the column name directly as the result of a formula.  What I would do instead is build a tiered set of Switch statements based on the result of utcNow('MMM') and utcNow('dd'). So first you would have 12 cases to switch based on the month and then 28 to 31 cases for each month (use default to get more than 26). Eventually you'll make it to a hard coded filter array. Store the results in an array variable and use the array for the rest of the processing.  There may be a more elegant solution, perhaps using a child flow, but I'm not sure what it would be.

[Rhiassuring]

Oh wow, okay. Well, I feel better that I'm not alone in internally-screaming at this requirement that should be easy (haha, "should be easy", I sound like a new poster 🙂 ) but ... isn't. 

OP, I am going to keep thinking on this for you. Even for a workaround.

[ManishSolanki · Accepted solution]

I have a sample flow that can send reminders for attendance. 

1. Firstly, set up the recurrence flow that runs daily. Set the time zone and start time accordingly:

2. Initialize an array variable 'defaulterList' that holds the list of users & dates for which attendance is not marked:

3. Add "List rows present in a table" action to fetch records from the excel sheet:

4. Add "Select" action to get all the dates of the correct month starting from 1st to current date in an array. We need to use expression to get the array of dates. Expression needs to be added in the expression box as shown below:

range(1,int(utcNow('dd')))

Similar, add the below expression in key parameter of Map & leave the value textbox blank:

concat(item(),'-',utcNow('MMM'))

5. Add another "Select" action to create an array of name of the columns which we would be comparing in the source excel file. This is done to avoid getting reminder for future dates:

Expression used in 'From' parameter:

xpath(xml(json(concat('{"root":{"items":',body('Select'),'}}'))),'//items')

Click on "Switch Map to text mode" link button on the right side of map parameter and enter the below expression in the Map textbox:

xpath(item(),'name(/items/*)')

6. Add "Apply to each" action and pass value object from the output of list rows present in a table action. We will iterate each record of the user and check for empty entries till the current date:

Inside loop, add "Select" action which will return the missing entries for a user:

Expression used for 'From' parameter:

xpath(xml(json(concat('{"items":',createArray(item()),'}'))),concat('/items/*[contains(','''',join(body('Select_2'),','),'''',',local-name()) and .='''']'))

Expression used for 'Map' parameter. Please click on 'Switch Map to text mode' link bottom before entering the below expression:

replace(replace(xpath(item(),'name(/*)'),'_x003',''),'_','')

Add "Condition" action to compare if previous select action returns missing attendance entries. If yes, then we will store the username & date in an array variable:

length(body('Select_3'))

In "Yes" block, add "Append to array variable" to store the name of the user & the date for which attendance is not marked:

{
"Name": @{items('Apply_to_each')?['Name']},
"Day(s)": @{join(body('Select_3'),', ')}
}

Pls copy & paste the above code in the action.

7. Finally, add a condition action to check if we have an element in the array variable for sending reminder. 

length(variables('defaulterList'))

In "Yes" block, add "Create HTML table" action to create a table from an array:

Finally, add "Post message in a chat or channel" action to post the reminder on the channel message:

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[Pstork1]

The reason it isn't easy is because this kind of feature opens the system up for Code Injection hacks.  Most systems nowadays don't allow the injection of dynamic code.  Its just too much of a security risk.

[MaverickNK]

Thank You @ManishSolanki for your invaluable help and the learning that came with it...

[MaverickNK]

Thank You @Rhiassuring for the time that you spent helping me with this query...