Doing Math in Microsoft CRM aka Calculated Fields
This is a question I get about once a week and got tired of typing up the same email over and over again. J Doing Math in Microsoft CRM using Jscript is mind numbingly easy. If you can do an Excel (Or VisiCalc) math equation, then you can do one in Microsoft CRM.
How you ask? Insert a Jscript on the field that you want the math done in. (So if you want Annual Income plus Alimony, this is how you could do it… And this is in case you can forget about the Anniversary date or month. J)
So you will have three fields:
1. Annual Income – new_AnnualIncome
2. Annual Alimony – new_AnnualAlimony
3. Total Annual Income – new_totalannualincome
On the Total Annual Income, put a OnChange script that looks a little like this:
crmForm.all.new_totalannualincome.DataValue = crmForm.all.new_annualincome.DataValue + crmForm.all.new_annualalimony.DataValue;
Under normal circumstances CRM will not include disabled fields when saving forms even when the data has been changed. We can, however, force CRM to include this field by using the ForceSubmit property as seen below:
crmForm.all.new_totalannualincome.ForceSubmit = true;
I would disable the field. Then on the other two fields, paste this Jscript into their OnChange event:
new_totalannualincome_onchange0()
So the math you can do includes:
Addition |
+ |
Subtraction |
- |
Division |
/ |
Multiplication |
* |
Comments
Anonymous
August 10, 2006
what would the Jscript look like if you want to Calculated more than one Field ?Anonymous
August 10, 2006
Give me an example...Anonymous
October 18, 2006
Cool, thanks man, that was an eye opener for someone that had no ideia of how to do it.Anonymous
March 16, 2007
My apologies in not getting this up sooner. I flew home on the red-eye on Wednesday night, drove fromAnonymous
March 16, 2007
My apologies in not getting this up sooner. I flew home on the red-eye on Wednesday night, drove fromAnonymous
April 12, 2007
Hey i am curious about a calculated field like the estimated opportunity revenue. where the field is calculated by x - sum(child:x). is there an easy way to do that on the form?Anonymous
April 12, 2007
Easy answer? No. :-) With some work, yes... But easily... No...Anonymous
June 06, 2007
How do i set up a similar calculated totals in a structure similar to contracts and their contract lines? When you add a figure in contract lines the total figure in the contract gets updatedAnonymous
June 10, 2007
Maina, That would require a little custom work... You should do a post call out with an application calling CRM's web service to make the update. :-) Thanks! BenAnonymous
August 02, 2007
Hi to all! I'm going mad, my math customization problem is this: When you go to Sales -> Offers and than open an existing Offer the toolbar have a button Recalculate. This calcolous find actual(at the moment of the launch) prices of all products and sum for a new total (if the prices are changed from the previous launch), this is very interesting because manage the list of products associated to the offer, I need this too. Well, I have to do a simple thing (but I'm walking on the darkness!) I've added a picklist (simple!) with some voice of type "add 10% to the total amount of offer" or "apply Christmas discount to tech products" ecc I've added a new button "MyRecalc" to the toolbar (simple!) BUT I'don't have idea to how apply my personal calcolous related to the picklist choice. Wich is the way to do that? There's examples, exist somewhere the source code of the original "Recalculate" method? Heeeeeelp :) Panez PS if the solution exist I hope it's doesn't use Webservices, I build a custom web service that work if called in a web application inside an Iframe but when I call web service methods inside javascript I've got empty response (I don't know if the web service really respond to my call). PPS Sorry for my EnglishAnonymous
August 20, 2007
Is it possible to do dependant math? i.e.: form1 + form2 only if form3 = x The usage I am looking for is a total of grant funds. Funds from form1, plus funds from form2 but only if the funds in form2 are eligible as specified in a yes or no menu on form3. Thanks in advance for the help!Anonymous
August 20, 2007
I apologize. I think that my usage gives the wrong impression. This is all on the same form, but in 3 different fields.Anonymous
August 20, 2007
I keep getting an error... I do not get the output from the equation in my ending field, and when I try to enter text into the field I get "This control only accepts strings as null or input"Anonymous
August 20, 2007
Could you share your formula?Anonymous
September 19, 2007
Thanks for all the wonderful tips Ben.. you are my CRM hero! I did get the last thing figured out and the code works now. I still never figured out doing dependent math, as stated in my first post. I do have a NEW question... I was wondering if you can do math with date fields. Here is the usage: Say there is a field that says "Date Letter Was Mailed", and then there is a second field for "Followup Date". Well, my follow up date should be 30 days after the letter was sent. So if I fill in the "Date Letter Was Mailed" field, I would like the Followup Date to populate automatically with the date that would be 30 days from the date the letter was sent. Any ideas??Anonymous
September 24, 2007
M- Lookup in the JavaScript Bible doing math on Date Fields. It takes a little more work, but is pretty easy. I don't have it in front of me, but it works VERY well. :-) Thanks! BenAnonymous
October 02, 2007
Ben- Thanks so much for the info - it's much appreciated. Would you happen to know why I might be getting 'object exptected' errors whenever I update either factor variable? I've tried your method and the fireonchange() function and I end up with the same error. If I manually change the sum field it then calls the onchange function and works properly... Thanks!!Anonymous
March 13, 2008
I used an addition function on quotes, orders, and invoices that worked in 3.0 but after the upgrade, something is working quite right. I cannot convert an order to an invoice whenever I have data in my fields that are calculated. I can convert a quote to an order but my total doesn't show on the order after the conversion. I keep wondering what changed?Anonymous
April 04, 2008
I keep getting a "object is null" error... I followed your steps exacly. Could someone help me figure this out? All I am trying to do is add two money fields...Anonymous
May 01, 2008
I have a custom entity that I created with a "Amount Received" field. This Entity have a N:1 relationship with an account. I am trying to get help on the web service code it would require to have a total field on the account that will look at & sum all the related "Amount Received" fields for the child entity. Could you give me some direction?Anonymous
May 21, 2008
In CRM Live (which doesn't support plugin and custom workflow actions), how do we calculate a custom sub-total of Quote Products based on the category / Subject of the product. For example, if a Quote has multiple Quote Product, each Quote Product can be categorized by the subject of the Product (like Materrial + Labour), how do we calculate the sub-total of all the materials and sub-total of all the Labour? I added 2 new attributes to Quote entity for holding the sub-total and I can to an extent achieve the goal by using 2 different workflows on the Quote Product entity (one each for record created and record deleted triggers), how ever I cam not able to do the calculations if someone updates an existing Quote Product record's price or quantity. this would have been easy with plugins, but CRM Live doesn't support plug-ins. Any input on this is truely appreciated. ThanksAnonymous
March 03, 2010
I was delighted to find your article, but I am having some trouble with the implementation. I have 4 fields I added to Opportunity: new_esthardwarerevenue new_estmaintenancerevenue new_estservicerevenue new_totalestrevenue The last field is the sum of the first three. I put the script in the new_totalestrevenue onchange event: crmForm.all.new_totalestrevenue.DataValue = crmForm.all.new_esthardwarerevenue.DataValue + crmForm.all.new_estmaintenancerevenue + crmForm.all.new_estservicerevenue.DataValue; crmForm.all.new_totalestrevenue.ForceSubmit = true; I put the following on all the other fields onchange events: new_totalestrevenue_onchange0() Whenever I use the form and update one of the fields, I get an error message: This control only accepts numbers of null as input. The fields are money fields with the same range as the build estvalue field. I'm sure it is a simple and stupid solution, but I have been working on it all afternoon and unable to get it to work. Nothing is ever placed in the Total Est Revenue field.Anonymous
March 21, 2011
What if I need such a calculated field at the level of entities? Having to put the same jscript code over and over again within the web pages already looks stupid, but we have reports and external systems to deal with the CRM. Can I somehow do calculated field at the level of entity? Do not offer me to tweak the database :-)Anonymous
March 27, 2012
you can see this. ahmed--abdelghany.blogspot.com/.../crm-javascript-calculated-field-from.html