I was recently contacted by a friend from the New York City User Group. She was having trouble getting a validation rule to work correctly, she wasn't getting a syntax error, but the rule wasn’t firing at the correct time. She asked if I had a few minutes to help her through this so she could get this new validation in production for their users. I got on the phone with my friend and she emailed me their formula, which looked something like this:
AND( Approval__c = false,
Test_Price_1__c > Test_Price_2__c +Test_Price_3__c *.3)
After speaking with my friend it turned out what she was trying to do was have a validation that a record couldn't be saved if it wasn’t submitted for approval and the first price field was greater than 30% of the sum of the second and third price fields. She couldn’t figure out what was wrong, she was able to save the validation rule so it wasn’t a syntax error.
What I ended up doing is quickly re-creating the fields in a developer org I have setup so I could play with the rule. She told me that when she was testing she was using 500 in the first price field and 400 and 500 in the 2nd and 3rd price fields respectively. In her head she was thinking the result should be 270 (30% of 900), so she should not have been able to save.
After reviewing the calculation I realized what salesforce was actually calculating was 30% of 500 + 400 which equals 550 so that’s why was able to save her test record.
After she told me all of this I blurted out one word to her “PEMDAS”! I heard a “huh?” on the other line of the phone. I started to explain to her the Acronym for the order of operations of Math and what her validation rule was actually calculating.
I went through that Parentheses will be calculated first, Exponents second, then multiplication and division, then finally adding and subtracting. Even though we were just on the phone I felt like I heard the light bulb go off in her head. She said she understood what I meant and realized the problem wasn’t in her syntax but in her math. She applied PEMDAS and re-wrote the validation like this:
AND( Approval__c = false,
Test_Price_1__c > (Test_Price_2__c +Test_Price_3__c) *.3)
I was really happy that I was able to help my friend with her validation rule and pass on a great lesson from one of my favorite teachers of all time.
After helping my friend I posted a facebook status sharing my re-found love of the PEMDAS Acronym. “Mrs. Baker would be proud (my 7th grade math teacher). I still use PEMDAS to this day when writing Salesforce formulas. And you should too!”
It was through this post that I had a bunch of friends I went to Junior High with comment and share they all still use PEMDAS in their everyday lives too! I also had some friends from the Salesforce world ask about PEMDAS which is what inspired this blog post. If you would like to read more about PEMDAS or math in general check out this article from the Math is Fun website.
I would like to dedicate this post to my 7th grade math teacher, Mrs. Baker. PEMDAS is just one of the many lessons that Mrs. Baker taught me. Mrs. Baker was all about making sure she fostered the love of math in both girls and boys. She even had an afterschool program she ran called “Girls Count” which was open to girls of all grades in our Junior High. Looking back I thought this was just a fun thing to do afters school that would help me in math and make friends. I didn’t realize that some of the lessons she taught us about believing in ourselves, going after our dreams and showing us that we could be just as good at math and science as boys would still be useful over 20 years later.
I hope this post helps some of you out there in Salesforce land and that you’ll think of PEMDAS the next time you write a formula or validation rule in Salesforce.
As always thank you for reading.
Cheryl
