Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

System

1. COLUMNTOTAL

Calculates total quantities of records or evaluates total amounts / minimal values / maximal values / averages of values in the specified or current column considering column conditions and filters applied.

COLUMNTOTAL(column_id_optional, filter_optional, aggregation_type_optional, aggregation_field_optional).

All function parameters are optional and can be omitted (if last) or should be replaced with null (if not needed, but some arguments are to be placed after them).

Parameters:

  • column_id_optional - if leave empty or set to null, will calculate desired values for current column. If specify column ID, will calculate result for that column (this argument is necessary if column is unknown in current context, e.g., when function is executed on some cell in the Kanban header).

  • filter_optional - if specify, will add custom filter string to query and calculate result matching this filter.

  • aggregation_type_optional - if leave empty or set to null, will calculate total quantity of records matching the query, otherwise will calculate values with the way specified by proper aggregate function (see table below for a list and a SOQL syntax web reference for more help).

  • aggregation_field_optional - API name of the field, should be specified when using aggregation types because of different aggregation functions support only fields of specific types.

List of aggregation types:

Aggregation function
Returned result
Ignores null values?
Supported field types

COUNT

Number of records matching the query criteria (actually the same result as for empty or null proper argument).

No (if aggregation field is ID or is not specified) Yes (if other aggregation field is specified)

Any

COUNT_DISTINCT

Number of distinct non-null field values matching the query criteria.

Yes

Any

AVG

Average value of a numeric field.

Yes

Numeric

SUM

Total sum of a numeric field.

Yes

Numeric

MIN

Minimum value of a field.

Yes

Any. If use on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order.

MAX

Maximum value of a field.

Yes

Any. If use on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order.

COLUMNTOTAL() returns a total number of records in the current column.

COLUMNTOTAL({$Variables.myColumnId}, 'Type = {QUOTE}Electrical{QUOTE}') will return total number of records that match custom filter Type='Electrical' and belong to column with ID stored in variable "myColumnId".

Let Priority = picklist with these values: 'High', 'Medium', 'Low', 'No priority'.

COLUMNTOTAL(null, null, 'MAX', 'Priority') returnы 'No priority' because it is the latest value in picklist field.

COLUMNTOTAL(null, null, 'MIN', 'Units') returns the minimal number value if the field is numeric, and first alphabetically sorted value if the field is text.

COLUMNTOTAL({$Variables.myColumnId}, 'Stage = {QUOTE}Closed Won{QUOTE}', 'AVG', 'ExpectedRevenue__с') returns average Expected Revenue amount for opportunities in Closed Won stage.

2. BUILDFORMLINK

Builds a web URL to specified Form.

BUILDFORMLINK(form_id, record_id, mode, context_fields, parameters, entire_height, as_html_tag_optional, text_optional)

Parameters:

  • form_id - ID of form to be linked to

  • record_id - ID of record to be used as data source

  • mode - form mode (edit, view or new)

  • context_fields - JSON with the list of virtual fields and their values (for pre-population)

  • parameters - JSON with parameters that will be passed to the form

  • entire_height - true or false. If set to true, form will take all available height of container, even if no such space required to show form elements

  • as_html_tag_optional - optional argument. If set to true, not only form URL will be generated, but HTML tag <a> with href attribute equal to this URL

  • text_optional - optional argument. If set to any text except null, this text will be placed as inner content of <a> tag (has sense only if as_html_tag_optional is set to true)

Note: if omit record_id argument, form will be shown without object fields populated. If the form mode is "edit", it will be the same result as for "new" mode.

Let your application is located at your-app-url.com address.

BUILDFORMLINK('a0C9m00000T4LvWDFC', '5001A00000SdeRqQFF', 'edit') returns the following URL:

Let your application is located at your-app-url.com address.

BUILDFORMLINK('a0C9m00000T4LvWDFC','5001A00000SdeRqQFF','edit', null,'{"aaa":"bbb"}',false,true) returns the following HTML code:

Let your application is located at your-app-url.com address.

BUILDFORMLINK('a0C9m00000T4LvWDFC','5001A00000SdeRqQFF','edit', '{"inputTextarea":"This is some description"}', null, false, true, 'My Link Text') returns the following HTML code:

3. BUILDDASHBOARDLINK

Builds a web URL to specified Kanban.

BUILDDASHBOARDLINK(dashboard_id, variables, styles, as_html_tag_optional, text_optional)

Parameters:

  • dashboard_id - ID of dashboard (kanban) to be linked to

  • variables - JSON with the list of variables to be passed to dashboard. This argument is mandatory, so, replace it with null if no variables needed.

  • styles - string with CSS properties to be passed to dashboard. This argument is mandatory, so, replace it with null if no styles needed.

Note: styles argument is actually passed as URL parameter, but does not supported by Kanban page (reserved for future use).

  • as_html_tag_optional - optional argument. If set to true, not only dashboard URL will be generated, but HTML tag <a> with href attribute equal to this URL.

  • text_optional - optional argument. If set to any text except null, this text will be placed as inner content of <a> tag (has sense only if as_html_tag_optional is set to true)

Let your application is located at your-app-url.com address.

BUILDDASHBOARDLINK('a003m00002CvxffAAB','{"myVariable_1":"Some text"}',null) returns the following URL:

BUILDDASHBOARDLINK('a003m00002CvxffAAB',null,null,true) returns the following HTML code:

BUILDDASHBOARDLINK('a003m00002CvxffAAB','{"myVariable_1":"Some text"}',null,true,'My Dashboard') returns the following HTML code:

4. CHECKPERMISSION

Returns true if current user has listed permissions (all or at least one) or false if not.

CHECKPERMISSION(name_or_names_json_array, atleastone_boolean_optional)

Parameters:

  • name_or_names_json_array - name of the permission to be checked or JSON array of permission names.

  • atleastone_boolean_optional - if set to true, function will return true if current user has at least one permission from the array in first agrument, otherwise (or if omitted) function will return true only if current user has all listed permissions.

Let your user has these permissions: User_Test_Permission, Process_admin.

CHECKPERMISSION('User_Test_Permission') returnы true if current user has this permission.

CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ',')) returns true if current user has both these permissions.

CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ','),false) returns true if current user has both these permissions.

CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ','),true) returns true if current user has at least one of these permissions.

5. USERIN

Returns true if given user belongs to at least one of listed users, roles, profiles, queues or groups.

USERIN(user_id, users_roles_profiles_groups_ids).

Parameters:

  • user_id - ID of user which is being checked.

  • users_roles_profiles_groups_ids - one ID or list of IDs of of users/roles/profile/group/queue.

USERIN({$User.id}, 0083n000008qatjCVV) returns true if current user ID is equal to second parameter.

USERIN({$User.id}, ('0083n000008qatjCVV,00G3m000003pllF')) returns true if current user ID is in the listed IDs.

6. SOQL

Executes a SOQL query. Returns JSON with the result of the query.

SOQL(soql_string)

Replace soql_string with the SOQL you wish to execute.

The result is a JSON array of records. An aggregate query (COUNT(), SUM(), GROUP BY) returns an array of AggregateResult rows, so use JGET to read a single value out of it - see Example 2.

Use {QUOTE} for the single quotes that wrap string literals inside the query. Merge fields can be placed directly into the query string, no concatenation needed - see Example 3.

The function is available anywhere formulas are supported - Kanban dashboards, forms, actions, filters and styles. As with any function, wrap it in FORMULA[] except where the type of content is already "Formula" (for example, Action conditions). If the function is not listed in the merge field picker, type the call manually.

The query runs in the context of the current user: it respects that user's sharing rules and field-level security, so the same formula can return different results for different users.

Each call counts against the org's SOQL query governor limits. There is no row ceiling applied by the function itself, so add LIMIT to the end of your query to stay safe. Avoid placing the function where it will be evaluated once per record, and prefer an aggregate query over returning rows and counting them.

SOQL('SELECT Name FROM Account WHERE Type={QUOTE}Prospect{QUOTE}') returns a JSON array of respective Accounts.

JGET(JGET(SOQL('SELECT COUNT(Id) total FROM Opportunity WHERE StageName = {QUOTE}Closed Won{QUOTE}'), 0), 'total') returns the number of won opportunities.

The inner JGET(..., 0) takes the single AggregateResult row from the array, and the outer one reads the aliased total value out of that row.

SOQL('SELECT Id, Name FROM Contact WHERE AccountId = {QUOTE}{Account.Id}{QUOTE} LIMIT 200') returns Contacts of the Account in the current context, using a merge field placed directly inside the query string.

Let each open opportunity carry a weight depending on its stage.

returns a weighted forecast number: one grouped query, each stage's record count multiplied by its weight, then summed and rounded.

Last updated