SOLVED

Reset Vacation Hours based on Anniversary Date

Copper Contributor

Hello,

 

I am creating an app in power apps for time off requests.  My app pulls values from a SharePoint list that has the employees time off data.  Could someone help me with a formula that when they reach their next anniversary date the vacation and medical time resets automatically?

 

Example:

Employee A has 40 hours of vacation time, within the year they used 20.  Our vacation time does not carry over to the next year.  On 09/01/24 (anniversary date of when they were hired) their vacation time will reset back to 40

5 Replies
best response confirmed by exceluser20232410 (Copper Contributor)
Solution

I'll write the step by step for how you do this and I want you to try to recreate it :) . Follow these steps and try to recreate the workflow on your own. If you encounter any difficulties, feel free to quote me and I will assist you.as I want you to learn and understand :)

 

 

1. Create a scheduled flow in Power Automate that runs once a day. To do this, go to https://flow.microsoft.com/ and sign in with your account. Then click on Create from the left menu and select Scheduled flow from the options. Give your flow a name and specify when you want it to run (e.g., every day at 08:00 ). Click on Create.

 

2.  Add a SharePoint trigger to your flow that gets all items from a list. To do this, click on New step from the flow designer and search for SharePoint in the connectors list.

 

Select SharePoint - Get items from the actions list. Then enter the site address and list name of your SharePoint list that contains the employee information. You can also use dynamic content or expressions to specify these values.

 

3.  Add a loop to your flow that iterates through all items from the SharePoint list and checks if today's date matches the anniversary date field.

 

To do this, click on New step and search for Control in the connectors list. Select Control - Apply to each from the actions list. Then select value from the dynamic content panel as the output of the previous step (Get items). This will create a loop that runs for each item in the SharePoint list.

 

4. Inside the loop, add a condition that compares today's date with the anniversary date field of each item. To do this, click on Add an action inside the Apply to each loop and search for Control in the connectors list. Select Control - Condition from the actions list. Then click on Edit in advanced mode and enter the following expression:

 

@equals(formatDateTime(utcNow(),'MM-dd'),formatDateTime(items('Apply_to_each')?['Anniversary_date_field'],'MM-dd')) 

 

This expression uses the formatDateTime function to convert both dates to a common format (MM-dd) and then compares them using the equals function.

 

5.  Inside the condition, add an action that updates the item and resets the number of free days to the default value

 

To do this, click on Add an action under If yes branch of the condition and search for SharePoint in the connectors list. Select SharePoint - Update item from the actions list. Then enter the same site address and list name as before and select Id from the dynamic content panel as the Id of the item to update. Then enter 7 (or any other value) as the value for field.

 

6. Save your flow and test it by running it manually or waiting for it to run on schedule. You can also check your SharePoint list to see if the number of free days has been reset for any employees whose anniversary date is today.

@NicolasKheirallah 

 

Thank you so much for this! I have been working on the app today and made some changes to get other items flowing properly. I changed to retrieve the employee information from an excel table instead of SharePoint. I think I switched everything properly from your step by step to excel but I think I am messing up the expression part in my condition. Could you take a look and help if you have a chance?

 

Capture.JPGCapture2.JPG

Looks good, I would strongly recommend you to use SharePoint List for this instead of Excel. Just as a rule of thumb, use SharePoint lists for anything that doesn't need to be shared externally or need advanced calculations.


As for Equal, you want to check with year now so like this:

@equals(formatDateTime(utcNow(),'yyyy-MM-dd'),formatDateTime(items('Apply_to_each')?['RenewalDate'],'yyyy-MM-dd'))

You probably need to manually add the renewaldate and replace into the expression above :)

Great job so far!

@NicolasKheirallah Thank you so much for helping!  I ended up changing it back to the sharepoint list instead of excel that was becoming a headache!

Good Job! And now you have an great understanding of power plattform and it's flexibility! :)
1 best response

Accepted Solutions
best response confirmed by exceluser20232410 (Copper Contributor)
Solution

I'll write the step by step for how you do this and I want you to try to recreate it :) . Follow these steps and try to recreate the workflow on your own. If you encounter any difficulties, feel free to quote me and I will assist you.as I want you to learn and understand :)

 

 

1. Create a scheduled flow in Power Automate that runs once a day. To do this, go to https://flow.microsoft.com/ and sign in with your account. Then click on Create from the left menu and select Scheduled flow from the options. Give your flow a name and specify when you want it to run (e.g., every day at 08:00 ). Click on Create.

 

2.  Add a SharePoint trigger to your flow that gets all items from a list. To do this, click on New step from the flow designer and search for SharePoint in the connectors list.

 

Select SharePoint - Get items from the actions list. Then enter the site address and list name of your SharePoint list that contains the employee information. You can also use dynamic content or expressions to specify these values.

 

3.  Add a loop to your flow that iterates through all items from the SharePoint list and checks if today's date matches the anniversary date field.

 

To do this, click on New step and search for Control in the connectors list. Select Control - Apply to each from the actions list. Then select value from the dynamic content panel as the output of the previous step (Get items). This will create a loop that runs for each item in the SharePoint list.

 

4. Inside the loop, add a condition that compares today's date with the anniversary date field of each item. To do this, click on Add an action inside the Apply to each loop and search for Control in the connectors list. Select Control - Condition from the actions list. Then click on Edit in advanced mode and enter the following expression:

 

@equals(formatDateTime(utcNow(),'MM-dd'),formatDateTime(items('Apply_to_each')?['Anniversary_date_field'],'MM-dd')) 

 

This expression uses the formatDateTime function to convert both dates to a common format (MM-dd) and then compares them using the equals function.

 

5.  Inside the condition, add an action that updates the item and resets the number of free days to the default value

 

To do this, click on Add an action under If yes branch of the condition and search for SharePoint in the connectors list. Select SharePoint - Update item from the actions list. Then enter the same site address and list name as before and select Id from the dynamic content panel as the Id of the item to update. Then enter 7 (or any other value) as the value for field.

 

6. Save your flow and test it by running it manually or waiting for it to run on schedule. You can also check your SharePoint list to see if the number of free days has been reset for any employees whose anniversary date is today.

View solution in original post