cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BKME
Frequent Visitor

Use Flow to Calculate Date based on other Columns in Sharepoint List

Hello! I am very new to Power Automate and am hitting some snags with my flows. 

 

I am using a Sharepoint list to keep track of medications and want to use flow to calculate an expiration date for each script based on the values in several other columns (Date Written, Pill Quantity, Fills, Pills per Day).

 

The math is essentially: 

1. Quantity*Fills=Total Quantity 

2. Total Quantity/Pills per Day=Total Doses

3. Total Doses + Date Written = Expiration Date

 

I have approached this process in various ways (none very successful!). First I used calculated columns in the Sharepoint list to generate the expiration dates which worked really well until I realized I couldn't use calculated values in Power Automate flows. Then I tried to do the calculations in flow but can't figure out how to set it up to avoid creating an infinite loop. Step 3 has been the most difficult for me. I'm sure my flow is arranged incorrectly or the syntax of my expression is wonky.

 

In an ideal world, all of these calculations would be done in one flow instead of the clunky mess I've put together 😅Anyone have any suggestions? Thank you!Step 2Step 2Step 3Step 3

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @BKME,

 

The expression is about like below. When you using it please replace these parameters with dynamic content actually, mine is just an example:

addDays(DateWritten,div(mul(Quantity,Fills),PillsPerDay),'yyyy-MM-dd')

 

And in case of an infinite loop, you could create a column in the SharePoint list named update by Flow, set its default value to No, and every time an item is created or modified, trigger the Flow but it should check if the update column value is No, if No, update item and set its value to Yes; if Yes, do nothing.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

BKME
Frequent Visitor

Figured it out!!!

I just needed to add the Integer function and it worked beautifully! 

addDays(DateWritten,int(div(mul(Quantity,Fills),PillsPerDay)),'yyyy-MM-dd')

 

 Thank you for your help @v-litu-msft!

View solution in original post

5 REPLIES 5
v-litu-msft
Community Support
Community Support

Hi @BKME,

 

The expression is about like below. When you using it please replace these parameters with dynamic content actually, mine is just an example:

addDays(DateWritten,div(mul(Quantity,Fills),PillsPerDay),'yyyy-MM-dd')

 

And in case of an infinite loop, you could create a column in the SharePoint list named update by Flow, set its default value to No, and every time an item is created or modified, trigger the Flow but it should check if the update column value is No, if No, update item and set its value to Yes; if Yes, do nothing.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

BKME
Frequent Visitor

Hi @v-litu-msft! Thanks for the response! 

 

I tried using the expression you provided and it returned an error that said it couldn't convert the date because it didn't have the expected format 'String/date.'  I don't have a clue how to troubleshoot that. Any suggestions? 

 

I put the expression you provided in Compose and then applied the output to the "Expires On" field in Update Item. Did I do that correctly? Or does the expression need to be applied to another step? 

BKME
Frequent Visitor

Also, in previous flows, I have used the format 'MM/dd/yyyy" for dates rather than 'yyyy-MM-dd.' Could that be part of the issue? 

BKME
Frequent Visitor

OK, so I've played with it a little more. Disregard the last two posts on the thread. Here's my current issue:

 

When I put in the expression, it returns an error that says "The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'Float.'"

 

I've thought perhaps I had a comma or a close parenthesis out of place since the expression is quite long so I did 2 tests to see if that was the issue. First, I simplified the expression to:

addDays(DateWritten,30,'yyyy-MM-dd')

This worked just fine. The flow ran successfully.

Then I tied this expression:

addDays(DateWritten,TotalDoses,'yyyy-MM-dd')

I created a new column that already held the correct days to add to the Date Written field and it returned the same error code. It seems as if the issue may be that Power Automate will not read the nested expression as an integer to add?

BKME
Frequent Visitor

Figured it out!!!

I just needed to add the Integer function and it worked beautifully! 

addDays(DateWritten,int(div(mul(Quantity,Fills),PillsPerDay)),'yyyy-MM-dd')

 

 Thank you for your help @v-litu-msft!

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,952)