Form end user entered calculation scripts

[#1]

I have been asked if it is possible to create a LiveCycle form where users can click in a field, add or subtract several amounts (as they apparently can in Excel by entering a formula with the amounts, eg  '=2.54+41.87+14.62'), then, in addition to seeing the resultant total, they also want to be able to see the items that were added/subtracted, ie the working out! Is this even remotely possible in LiveCycle?

[#2 · Accepted solution]

OK, first let me say sarcastically that I sort of hate you for asking this question... This wracked my brain for WAY longer than it should have.

I was able to get this to work with the 4 primary operators (+, -, *, and /) in order. I have not attempted to apply order of operations because that would be even worse. (It would require recursive operations that I'm not really interested in trying to pull off in LC right now.)

Here's the working code for this along with some screen shots of what it looks like. If an operator is the first character, the event performs whatever operation on the existing subtotal, removes the operator and number, then loops back if there is more left to do.

Screenshot of working text field for the equation and decimal field for the answer:

Screenshot of the design view with some code:

The entire code in the exit field. (I'm certain others could streamline this a little more, but this is what I came up with.) Also, I apologize if the indentations are not right. The syntax highlighting is garbage.:

var f = this.rawValue;
var subtotal = operatorIndex = 0;
var opEx = /\+|\/|\*|\-/;
do{
  switch (f.slice(0,1)){
    case "+":
      f=f.slice(1);
      operatorIndex = f.search(opEx);
      if (operatorIndex > -1) {
        var addend = parseFloat(f.slice(0,operatorIndex));
        f=f.slice(operatorIndex); 
      }
     else {
       var addend = parseFloat(f);
       f=null; 
     }
     subtotal = subtotal + addend;
  break;
  case "-":
     f=f.slice(1);
     operatorIndex = f.search(opEx);
     if (operatorIndex > -1) {
       var subtrahend = parseFloat(f.slice(0,operatorIndex));
       f=f.slice(operatorIndex); 
     }
     else {
       var subtrahend = parseFloat(f);
      f=null; 
     }
     subtotal = subtotal - subtrahend;
     break;  
  case "/":
    f=f.slice(1);
    operatorIndex = f.search(opEx);
    if (operatorIndex > -1) {
      var divisor = parseFloat(f.slice(0,operatorIndex));
      f=f.slice(operatorIndex); 
    }
    else {
      var divisor = parseFloat(f);
      f=null; 
    }
    subtotal = subtotal / divisor;
    break;
  case "*":
    f=f.slice(1);
    operatorIndex = f.search(opEx);
    if (operatorIndex > -1) {
      var multiplier = parseFloat(f.slice(0,operatorIndex));
      f=f.slice(operatorIndex); 
    }
    else {
      var multiplier = parseFloat(f);
      f=null; 
    } 
    subtotal = subtotal * multiplier;
    break;
  case "=":
    f=f.slice(1);
  default:
    operatorIndex = f.search(opEx);
    if (operatorIndex > -1) {
      subtotal = parseFloat(f.slice(0,operatorIndex));
      f = f.slice(operatorIndex); 
    }
    else {
      subtotal = f;
      f=null; 
    }
    break; 
} while(f!=null);
nfAnswer.rawValue = subtotal;

[#3]

Thanks again jasotastic81 - Your coding works like a charm and I have a very happy internal customer who love their new form!

[#4]

It was an interesting mental exercise, to say the least.

I would agree with your original assessment, though. They should stick to Excel. (Unless they're paying you to write an Excel knock off PDF for them.)

[#5]

Gosh, jasotastic81, let me say sarcastically that I sort of hate you for answering this question... now I gotta at least try to make it work instead of telling them to just stick with their Excel spreadsheet version! 🙂

But seriously, thank you, and apologies for the late thanks (but lucky me I've been on holidays). I will let you know how I go with this once I work through my backlog of tasks and get to this beauty of a form.