Is there a way to calc. the value of a formula field using a formula that references js on a stored page rather than a formula based on conventional formula functions?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

The elegant IOL method by Dan Dietbolt, as I understand, requires a form to be loaded for control to be passed to the external js page. In my case, I just want the value to be calculated by the external js page and returned to the formula field (in the background) as it would be if the formula field contained a conventional formula. Thanks.

Photo of Chris

Chris

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
There are two issues with your proposal. First, a formula field cannot receive a value. Rather a formula field calculates its value from other fields in the same record. Second, in what context do you want to invoke the functionally from? In other words, what page are you on and what do you want the user to do to make the calculation happen? 
Photo of Chris

Chris

  • 0 Points
Hi Dan. A trivial example would be a formula field that operates on another text field to calculate the total number of instances of a particular string within that text field. So, to calculate the result (count) that will be returned by the function, the function would pass the text field content to the js. The js would perform the analysis on the text to arrive at a count of the instances of the string. Finally, the js would then somehow get that count back to the calling formula which would then result in the assignment of that count (the formula result) to the formula field. Perhaps a more general way to state my question is: for a given formula in a formula field that produces a result X being assigned to that field, is there alternate formula structure that can spawn an equivalent js formula calculation on a stored page that causes that alternate formula to return that same value X that is then assigned to the formula field? In some sense, the formula is calling a js subroutine to do the actual calculation rather than using conventional formula functions. The other key point is that this alternate formula structure would be automatically evaluated and the result assigned to the formula field just as it would with a conventional formula. IE: if [sum] = [field1] + [field2], [sum] is automatically calculated; if you display [sum], it already contains the sum of those two field and updates automatically if either argument is changed. Thanks.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Formulas are evaluated on the QuickBase server before the page is set to your browser. JavaScript on the other hand is evaluated after the page has been sent to your browser. So while you can display the results of your JavaScript on a form or report and even make the appear that it is displaying as a normal field, the calculated value is not yet saved in any field in the table. Now you could make an API call and save the calculated value and save it in a field in the table but that saved value will be incorrectly calculated if any of the inputs to the JavaScript calculation subsequently change. In most cases it is sufficient to just display the value calculated by the JavaScript and not save the result in a field in the table.