cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Billy_C
Helper V
Helper V

Model Driven Apps Calculated Date Field Formulas

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!

4 REPLIES 4
summitb
Power Participant
Power Participant

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_C
Helper V
Helper V

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
Power Participant
Power Participant

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_C
Helper V
Helper V

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

Helpful resources

Announcements
PA 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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (71,042)