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 ;-)
Best answer by Erwin Ekkel
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.
Next in data model add an expression column and to execute the function your code could look like this if column ID was the input.
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.
Next in data model add an expression column and to execute the function your code could look like this if column ID was the input.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
Thank you! 