Model Driven Apps Calculated Date Field Formulas

(1) Share

Report

Posted on by Billy

396

Hi There,

So I am trying to create a due date for invoices based on payment terms that have been agreed with a company.

I managed to do an x amount of days after the invoice date. Problem is I'm trying to do the same thing except it's for x amount of days after then end of the month.

So I first thought I could calculate the difference in days and subtract them from the invoice date and add 1 month to it.

Then I could add x amount of days, except this doesn't work.

Is there a method to either set the day of a date or create a date based on a string given a certain format?

Or is there any other way to calculate this?

Is there also a toString() function to calculate due field that shows the amount of days the invoice hasn't been paid past the due date? If it has been paid within the due date it will display the due date.

Thanks in Advance!

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

summitb 2 on at

Like
a
(1)

Report
Copy link

Link copied!

If you want to check the number of days the invoice has not been paid why do you need to store it in CDS, you can just show it in the canvas app. You can AddDays(whole_number, date_time) to show invoice date.

But if you can derive it at UI level why store it in DB.
Billy 396 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @summitb,

Thank you for your response, but I am not using any canvas apps, but rather model driven apps.

I am actually trying to calculate the due date based on the payment terms.
So if the payment term for a company is "x days after the end of the month" then I would need the following formula: Set day to 1, set month to month+1 and add x days.
The only part that Calculated Fields allow is adding x days and x months. So I wondered if there was a way to do this.

Calculating the number of days the invoice hasn't been paid has been resolved by using two extra fields, one with the amount of days it hasn't been paid and one that checks if it has been paid or not and how many days are past the date etc.

So calculating the due date is the only issue left right now.

Thank in Advance!
summitb 2 on at

Like
a
(1)

Report
Copy link

Link copied!

You can use JS webresource for this then.
On change of the field or OnLoad of the form

myFunction(){
Xrm.Page.data.entity.attributes.get("your-field").setValue(your-formula);
}
Billy 396 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @summitb,

Thank you for your fast response, at the moment I am not able to check how it works as the project has been set on hold but when I get back to it I will definitely check it out and let you know if this method works.

Kind Regards