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

[BKME]

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 3

[v-litu-msft · Accepted solution]

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.

[BKME]

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]

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]

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 · Accepted solution]

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!