cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tarjani Advocate II
Advocate II

Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi there,

I am trying to create a flow that will update an invoice date when the Payment Terms or Invoice Date is changed.  

Due Date flow.jpgView of the size of Due Date Flowdue date formula.jpgDue Date Formula

Initially I started to write a flow to allow conditions to determine what is in the field (7, 30, 60, 90), and add that value to the Invoice Due Date using this expression:  addDays(utcNow(), 7)


However I realised that the if the Invoice Date has been updated (in theory it should stay as the created date, but I need to build this to allow an update), the new invoice date might not be for the day it was updated (utcnow).

I'm looking for a way to write an expression that will add the number listed in the Payment Terms field, to the date on the Invoice Date field.


Any thoughts appreciated,


The Invoice Due Date is to be the Payment Terms + the Invoice Date.  However , so my flow has been getting built 


1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @Tarjani ,

 

Interesting, try once again following: 'yyyy-MM-dd',  (lower case  yyyy). i just did a quick test and it works for me.

EDM.date format works with yyyy-MM-dd actually.

 

 

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

 

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @Tarjani  , if you are already getting "Payment Term" and "Invoice Date" in Power Automate a simple expression

adddays(Invoice Date,Payment Term,'MM-dd-YYYY)  will do the task.

 

Please correct me i am over simplifying your issue?

 

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

 

Tarjani Advocate II
Advocate II

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @DeepakS ,

Thanks for the reply.  In regards to having the Invoice Date and Payment Terms fields, I have them initialized as variables in the beginning.  So the expression might look a little like the one below:

addDays(variables('Invoice Date'), variables('Payment Terms'), 'dd-MM-YYYY')


However we are now wondering if we can use the value within the expression instead of the label within the Payment Terms, for the number that is added to the date.  The values for Payment terms match the number of days so this would be the same idea but pulling the data from the a different level.

expression using initialised variables.jpg
Tarjani Advocate II
Advocate II

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

@DeepakS 
Further to the below, I have just updated the expression in a way that I think will identify the the value, but is in line with previous comments.
- - >      addDays(variables('Invoice Date'), item(variables('Payment Terms'))?['Value'], 'dd-MM-YYYY')

however we keep getting an error message  that says:
'Cannot convert the literal '2020-01-21T00:00:00.0000000' to the expected type 'Edm.Date'.

Would you know anything on this?    The field is set to time only, so the time will always be set to 00:00:00.0000000, but this doesn't seem to met the Edm.Date requirement.  ??

Due date field settings.jpg


Super User
Super User

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @Tarjani 

 

Use following expression:

addDays(variables('Invoice Date'), item(variables('Payment Terms'))?['Value'], 'yyyy-MM-dd')

 

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Tarjani Advocate II
Advocate II

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @DeepakS ,

Ah - yes, we have also tried the date formatted like this, and it brings back the same message. 

We tried the date as dd-MM-YYYY, MM-dd-YYYY, YYYY-MM-dd and also tried them with / instead of -.
Everything brings back the same kind of message with alterations depending on the format entered  ie
Cannot convert the literal 'YYYY-01-21T00:00:00.0000000' to the expected type 'Edm.Date'
.

 

 

Super User
Super User

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

Hi @Tarjani ,

 

Interesting, try once again following: 'yyyy-MM-dd',  (lower case  yyyy). i just did a quick test and it works for me.

EDM.date format works with yyyy-MM-dd actually.

 

 

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

 

View solution in original post

Tarjani Advocate II
Advocate II

Re: Calculate Invoice Due Date using 'x' days in Payment Terms field

 

That's the one @DeepakS .  I had just been playing with that myself, and it works.

Thanks for replying to assist here.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (8,287)