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

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.  

View of the size of Due Date FlowView of the size of Due Date FlowDue Date FormulaDue 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

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
DeepakS
Resident Rockstar
Resident Rockstar

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.

 

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

@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


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.

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'
.

 

 

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.

 

 

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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,375)