Formula field to evaluate a custom expression (like Excel)

I have the need for a formula field to do two things:

1. evaluate an expression that my user types in. example: ((2+2) * 6) / 3

2. store the actual formula the user typed in to another field, in the case the formula should be audited for accuracy.

The formula is different record to record (being used on the Contract standard object), thus the need for my user to type it in. Plus, I have to be able include the actual formula in a Report column for auditors.

August 27, 2009
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

ShawnF
So, I've put together a solution for this.

I solved this using a few things:

   1. Two custom fields:"FieldX__c" for the custom expression and "FieldY__c" for the evaluation
   2. Javacript to conduct the calculation
   3. A visualforce page to put the two fields and javascript together

Here, in written format, is what the user experiences:

    * The user types their custom expression into "FieldX__c" - ex: (2 + 2) * 6
    * The user clicks on the "=" button
    * The javascript evalutes the expression in "FieldX__C" and stores the value in "FieldY__c"

Here is the apex code used for the visualforce page that includes the javascript&colon;

<apex:page showHeader="true" sidebar="false" standardController="Contract">

<script type="text/javascript">
function calculate(equation)
{
// equation can have characters 0-9, +, -, *, /, (, ), . and spaces
// If there are any other characters, return an error

var answer = 'Equation can ONLY have characters 0-9, +, -, *, /, (, ), . and spaces';

if (equation.match(/^[0-9+\-*/(). ]*$/))
{
    try
    {
      answer = equation != '' ? eval(equation) : '0';
    }
    catch (e)
    {
      // Syntax error - Do nothing... answer remains 'err'
    }
}

return answer;
}
//
</script>
<apex:form>

<apex:pageBlock id="custCalcs">

    <apex:pageBlockButtons >
        <apex:commandButton value="Save" action="{!save}" />
        <apex:commandButton value="Cancel" action="{!cancel}"/>
    </apex:pageBlockButtons>

    <apex:pageBlockSection title="Enter Custom Expressions Here" columns="1">
        <apex:pageBlockSectionItem >
            <apex:outputLabel value="Total Rent" />
            <apex:outputPanel >
                <apex:inputField value="{!Contract.FieldX__c}" style="width:400px;" id="equation"
                    onkeypress="if ((window.event ? event.keyCode : event.which) == 13) { this.form.elements['{!$Component.answer}'].value = calculate(this.value); }"/>

                <input type="button" value=" = " onclick="this.form.elements['{!$component.answer}'].value = calculate(this.form.elements['{!$component.equation}'].value);" />

                <apex:inputField value="{!Contract.FieldY__c}" id="answer"/>
            </apex:outputPanel>
        </apex:pageBlockSectionItem>

    </apex:pageBlockSection>

</apex:pageBlock>

</apex:form>

</apex:page>

October 7, 2009
·
Like
0
·
Dislike
0

Steve :-/
So are you saying that you want to create a User Editable Formula Field on your standard SFDC Contract Object?

When you say that "formula is different record to record" do you mean that the Formula may be entirely different from record to record, or just the numbers that make up the formula?

For example:

The Formula will ALWAYS be:

(Unit Price * Quantity) - Discount %

Or

on one contract the Formula might be

(Unit Price * Quantity) - Discount %

and on another it might be

(Unit Price * Quantity) + Delivery Charge + Late Fee %

August 27, 2009
·
Like
0
·
Dislike
0

ShawnF
Yes, to both of your questions.

The fields would be on the Contract standard object. And the formula is different record to record.

on one contract the Formula might be

(Unit Price * Quantity) - Discount %

and on another it might be

(Unit Price * Quantity) + Delivery Charge + Late Fee %

...but the forumla is pretty nasty. It has to do with monthly rent rates, start dates, increases in rent at periodic month counts, etc. So the formula MUST be typed in by the user.

August 27, 2009
·
Like
0
·
Dislike
0

Steve :-/
Ugh... I dunno how you can do that out of the box in SFDC, your best bet might be Visualforce, but even there I don't see how you can allow for variable operators and functions in single field at the Object Level.

We have something that is a *little* bit like that in spirit on our Opportunity records for one of our Sales Teams, but it's just a Free-Form Long Text field where they "describe" the calculations they used for the Sale (it's not an actual formula that spits out a $Amount)

August 27, 2009
·
Like
0
·
Dislike
0

ShawnF
Yah, its daunting. I've been trying to develop a solution for a while now using different options: Apex, Visualforce, Flex.

I found this calculator on the AppExchange that enables a user to launch a pop-up with a calculator interface, which then updates the Opp Amount field:
http://sites.force.com/appexchange/listingDetail?listingId=a0N300000016aTpEAI

...but it doesn't satisfy my second requirement which is to store the actual formula as text in another field.

I'll keep at it.