Using SQL functions in calculation fields

Question

hi,

I’m trying to add some calculated fields in a table, and found the calculation field and the specific possibilities for each type.

The calculation is rather complex and can be used in multiple tables, so I want to make one function with parameters. But I can’t find examples using SQL functions in a calculation field? Like how to create a control procedure (templated) to create and maintain an SQL function in SF, and using it in a calculation field. I can't find any in the training course, documentation or the community info. Does anybody have a simple example?

It is probably a n00b question, but hey, I am ;-)

icon

Best answer by Erwin Ekkel 23 September 2021, 09:26

View original

Erwin Ekkel · Accepted Answer

Let me explain below how to make function using an expression column. Please note that function's and expression columns can slow down performance. Depending on the calculation and the size of the table/amount of data shown on screen it might be better to create a view or have a hardcoded value with the result of the calculation (depending on how static the input for the calculation is).

First you create a subroutine of type function (scalar for a single result set, table if you want to return a set of values based on the input). Add subroutine parameter(s) as input for the function.

Here is a simple example for a scalar function which returns an integer, with 1 parameter for the input:

After creating the subroutine add functionality for the function code and assign it to the function (code group functions). Here is an example for a code that adds a value of 2 to the input variable and returns the value of the calculation.