{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":39176,"path":"platform-administration/platform-support-functions.md","title":"Function field","bundle":null,"doc_type":null,"release":"australia","body":"\n# Function field\n\nCreate function fields and scripts in the ServiceNow AI Platform to perform common database transformations and calculations.\n\nRegular fields store a value in the database. Instead of storing data, a function field displays the results of a database query. Function fields do not have an associated database column. Instead, function fields generate a value based on simple computations of other fields and constants. They can be used like any other fields in the system: in forms, lists, query conditions, reports, data visualizations, and so on.\n\n**Warning:** A function field that references another function field in the function will result in an error. Function fields don't hold data or evaluate in run-time.\n\n## Use case\n\nYou want to identify all incidents with a probably useless short description of fewer than 10 characters.\n\nInstead of querying all records to determine which record meets a given criteria, create a function field or function script that retrieves only the records that meet the criteria.\n\nFor a simple computation, it is better to use a function field or function script rather than store a computed value. The value is always calculated on retrieval. Another benefit of using function fields is that the database server performs the transformation rather than the application node.\n\n## Function fields versus calculated fields\n\nThe values of the calculated fields are stored in the database. The values of function fields or function scripts are not stored in the database, but are calculated at the time of retrieval. Function field values are always up to date.\n\n## Function fields versus business rules\n\nUse function fields or scripts when business logic is based on a simple transformation of one or more existing fields. Function fields and scripts let you implement business logic without storing and maintaining the result of a transformation.\n\n## Function fields versus filters, query strings, and Rhino\n\nRather than calling Rhino to perform string transformations, you can use a function field or function script to perform the transformations for you. Function fields and function scripts are more efficient and result in up-to-date values that do not have to be stored or maintained.\n\n## Function field limitations\n\nBecause function field calculations are not stored in the database, some operations are not relevant.\n\n- Function fields cannot be directly audited or indexed.\n\n **Note:** To index a function field as if it were a regular field, make sure the individual fields used by the function are indexed. Or for best performance, make sure that there is a composite index including all fields that are used in the function.\n\n- Function fields cannot be encrypted, since a function field value is never stored in the database.\n- Function fields cannot be converted to regular fields or vice versa.\n- Function fields that you create in the Reporting UI do not support dot-walking. For more information, see [Configure function fields in Reporting](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/now-intelligence/configure-function-fields.md).\n- Security is evaluated on the components of the function and on the calculated value of the field. When used in visualizations, sections that contain information that the user is not permitted to see are hidden from the user.\n- Field function names must be unique.\n\n## Defining platform functions\n\nPlatform functions can be defined in two ways:\n\n1. Use the following application programming interfaces \\(APIs\\) to build and use functions in a script.\n\n |APIs|Description|\n |----|-----------|\n |[Scoped GlideDBFunctionBuilder](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/api-reference/server-api-reference/GlideDBFunctionBuilderScopedAPI.md)|Construct the function to perform a SQL operation.|\n |[GlideRecord - addFunction\\(Object function\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/api-reference/server-api-reference/c_GlideRecordAPI.md)|Apply the function to a GlideRecord.|\n |[GlideDBFunctionCaseBuilder - Global](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/api-reference/server-api-reference/GlideDBFunctionCaseBuilderAPI.md)|Build case statements.|\n\n2. Create a field that holds the function definition, as shown in the following example.\n\n \\[Omitted image \"platform-functions-dictionary-entry.png\"\\] Alt text: Function field definition\n\n Select the **Function field** check box on the Dictionary Entry \\[sys\\_dictionary\\] form. This action specifies that the field runs a function rather than stores a value.\n\n\n## glidefunction operations\n\nFunction fields can perform the following operations using the `glidefunction:` syntax. When providing a field as an argument, you can dot-walk to related fields. For example, `cmdb_ci.name`.\n\n**Note:** The function field type must be compatible with the return type of the glide function. For example, integer, longint, string, and so on. If the returned type cannot be converted to the dictionary type, you get exceptions.\n\n\n
\n\nOperation\n\n\n\nDescription\n\n\n\nExample\n\n
\n\nadd\\(\\)\n\n\n\nTakes two number fields as input, adds them, and returns the results as a field value. This function also takes numerical values for either input. Place numerical values in single or double quotation marks.\n\n\n\nglidefunction:add\\(child\\_incidents, parent\\_incident\\) Returns 6 if the incident has five child incidents and one parent incident.\n\nPossible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\n
\n\ncoalesce\\(\\)\n\n\n\nTakes any number of comma-separated fields as input and returns the first non-empty value.\n\n\n\nglidefunction:coalesce\\(closed\\_at, resolved\\_at, sys\\_updated\\_on\\) If the value of closed\\_at is empty, the function returns the value of resolved\\_at. If the value of resolved\\_at is also empty, the function returns the value of sys\\_updated\\_on.\n\nReturn type: Text\n\n
\n\nconcat\\(\\)\n\n\n\nTakes any number of comma-separated fields and constants as input, concatenates the input, and returns a single string as a field value.\n\n\n\nglidefunction:concat\\(incident\\_number, '/', short\\_description\\) Returns \"INC0001 / My client needs a new laptop.\" if the value of the number field is 'INC0001' and the short\\_description is 'My client needs a new laptop'.\n\nReturn type: Text\n\n
\n\ndatediff\\(\\)\n\n\n\nTakes two date/time fields as input, calculates the difference between the dates in days, minutes, and seconds, and returns the results as a duration field value.\n\n\n\nglidefunction:datediff\\(closed\\_at, sys\\_created\\_on\\) Returns the duration of an incident from the creation date to the close date. Example result: 10 days, 8 hours 23 minutes 11 seconds\n\nReturn type: Duration\n\n
\n\ndayofweek\\(\\)\n\n\n\nTakes two arguments: A date field and a constant of either '1' \\(week starts on Sunday\\) or '2' \\(week starts on Monday\\). Returns the results as an integer value that represents the day of the week.The `dayofweek()` function uses UTC dates, but adjusts comparison values based on the instance's time zone.\n\n\n\nglidefunction:dayofweek\\(resolved\\_at, '1'\\). If resolved\\_at occurs on a Wednesday, returns 4 if the integer is 1 and returns 3 if the integer is 2.\n\nReturn type: Whole number\n\n
\n\ndistance\\_sphere\\(\\)\n\n\n\nTakes two locations and returns the distance between them in meters.\n\n\n\n- glidefunction:distance\\_sphere\\('-0.189937', '51.473584', '80.206458', '25.767242'\\)\n\nReturns the distance between the two sets of longitude and latitude pairs.\n\n- glidefunction:distance\\_sphere\\(u\\_location, '80.206458', '25.767242'\\)\n\nReturns the distance between the u\\_location geo point field and the longitude and latitude coordinates.\n\n- glidefunction:distance\\_sphere\\(location1, location2\\)\n\nReturns the distance between the location1 and location2 geo point fields.\n\n- glidefunction:divide\\(distance\\_sphere\\(location1, '-0.189937', '51.473584'\\), '1000'\\)\n\nReturns the distance between the location1 geo point field and London in kilometers.\n\n\n Return type: Decimal\n\n
\n\ndivide\\(\\)\n\n\n\nTakes two number fields as input, divides the first by the second, and returns the result as a field value. This function also takes numerical values for either input. Place numerical values in single or double quotation marks.\n\n\n\nglidefunction:divide\\(u\\_num2,u\\_num1\\) Returns 5 if num2 = 10 and num1 = 2.\n\nPossible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\n
\n\nget\\_latitude\\(\\)\n\n\n\nTakes any geo point column or numeric field or numeric constant and converts it to a valid latitude value within the \\[-90,90\\] range.\n\n\n\n- glidefunction:get\\_latitude\\(u\\_geo\\_point\\_field\\)Returns the latitude value in the u\\_geo\\_point\\_field.\n- glidefunction:get\\_latitude\\(u\\_numerical\\_column\\)Returns the latitude value in the u\\_numerical\\_column within \\[-90,90\\] range.\n- glidefunction:get\\_latitude\\('305.767242'\\)Returns -54.232758 latitude value.\n\n Return type: Floating point number\n\n
\n\nget\\_longitude\\(\\)\n\n\n\nTakes any geo point column or numeric field or numeric constant and converts it to a valid longitude value within the \\[-179,180\\] range.\n\n\n\n- glidefunction:get\\_longitude\\(u\\_geo\\_point\\_field\\)Returns the longitude value in the u\\_geo\\_point\\_field.\n- glidefunction:get\\_ longitude\\(u\\_numerical\\_column\\)Returns the longitude value in the u\\_numerical\\_column within \\[-179,180\\] range.\n- glidefunction:get\\_longitude\\('380.206458'\\)Returns 20.206458 longitude value.\n\n Return type: Floating point number\n\n
\n\ngreatest\\(\\)\n\n\n\nTakes two or more values as input and returns the greatest value of the list of arguments.\n\n\n\nglidefunction:greatest\\(10,100,1000\\) Returns 1000\n\nPossible numeric return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\nThis function also works with date and string fields.\n\n
\n\nleast\\(\\)\n\n\n\nTakes two or more values as input and returns the lowest value of the list of arguments.\n\n\n\nglidefunction:least\\(10,100,1000\\) Returns 10\n\nPossible numeric return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\nThis function also works with date and string fields.\n\n
\n\nlength\\(\\)\n\n\n\nTakes a string field as input, calculates the field length in characters, and returns the results as a field value.\n\n\n\nglidefunction:length\\(short\\_description\\) Returns 37 if short\\_description = \"This application is performing a test\".\n\nReturn type: Whole number\n\n
\n\nmultiply\\(\\)\n\n\n\nTakes two number fields as input, performs the multiplication, and returns the results as a field value.This function also takes numerical values for either input. Place numerical values in single or double quotation marks.\n\n\n\nglidefunction:multiply\\(u\\_num1, u\\_num2\\) Returns 48 if num1 = 8 and num\\_2 = 6.\n\nPossible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\n
\n\nposition\\(\\)\n\n\n\nTakes two text fields or two text fields and a whole number as input. One or both of the text fields can also be strings.1. Returns the position of the first occurrence of the string of the first text field in the second.\n2. If a whole number is specified, this function returns the position of the second text field after the position of the whole number.\n\n Returns 0 if the first text field is not present in the second \\(after the position of the whole number if specified\\).\n\n\n\n- glidefunction:position\\('e',short\\_description\\)\n\nIf the value of the short\\_description field is \"This computer is performing an upgrade\", returns 12.\n\n- glidefunction:position\\('e',short\\_description,'14'\\)\n\nIf the value of the short\\_description field is \"This computer is performing an upgrade\", returns 19.\n\n\n Return type: Whole number\n\n
\n\nsubstring\\(\\)\n\n\n\nTakes a text field and two whole numbers as input. Returns the first instance of a string that starts at the position of the first whole number and is the length of the second.\n\n\n\nglidefunction:substring\\(short\\_description, '7', '2'\\) If the value of the short\\_description field is 'We're going to the store', returns 'go'.\n\nReturn type: Text\n\n
\n\nsubtract\\(\\)\n\n\n\nTakes two number fields as input, subtracts the second from the first, and returns the result as a field value. This function also takes numerical values for either input. Place numerical values in single or double quotation marks.\n\n\n\nglidefunction:subtract\\(u\\_num1, u\\_num2\\) Returns 2 if num1 = 8 and num\\_2 = 6.\n\nPossible return types: Decimal, Floating Point Number, Large Whole Number, Whole Number\n\n
\n\nto\\_geopoint\\(\\)\n\n\n\nTakes latitude and longitude columns or numerical values and converts them to a geo point column.\n\n\n\n- glidefunction:to\\_geopoint\\(u\\_long\\_column, u\\_lat\\_column\\)\n\nConverts the latitude and longitude columns into a single geo\\_point column.\n\n- glidefunction:to\\_geopoint\\('80.206458','25.767242'\\)\n\nReturns a geo\\_point field with the coordinates “\\(80.206458, 25.767242\\)”.\n\n\n Return type: Geo point\n\n
## Create a function field to perform database functions\n\nCreate a field that displays the results of a database function, such as a mathematical operation, field length computation, or day of the week calculation. Test in a subproduction instance before deploying to a production instance.\n\n### Before you begin\n\nRole required: personalize\\_dictionary or admin.\n\n### Procedure\n\n1. Navigate to **All** > **System Definition** > **Dictionary**.\n\n2. Click **New**.\n\n3. Select **Function field**.\n\n4. Fill in these fields on the form.\n\n\n
\n\nField\n\n\n\nDescription\n\n
\n\nTable\n\n\n\nTable to which the function field is added.\n\n
\n\nType\n\n\n\nSpecies the type of field: string, number, date, and so on.\n\n
\n\nColumn label\n\n\n\nDefines the label for the column.\n\n
\n\nColumn name\n\n\n\nDefines the name of the column. Created automatically based on **Column label**.\n\n
\n\nMax length\n\n\n\nDefines the maximum length of the return value.\n\n
\n\nFunction field\n\n\n\nIf selected, creates a field that displays the results of a database function, such as a mathematical operation, field length computation, or day of the week calculation.Once the new function record is saved, you cannot clear the check box to make the field a regular field.\n\n
\n\nFunction definition\n\n\n\nDefines the function that the field performs.Starts with `glidefunction:`, followed by the operation to be performed \\(for example, `concat`\\), followed by function parameters. Constants must be enclosed in single quotes.\n\nFor example, the following function definition creates a field that shows the short description, followed by a space, followed by the caller name:\n\n`glidefunction:concat(short_description, ' ', caller_id.name)`\n\n**Note:** Function parameters support dot-walking. See [Dot-walking to data in related tables](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-user-interface/c_DotWalking.md).\n\n
5. Click **Submit**.\n\n You return to the Dictionary Entry \\[sys\\_dictionary\\] table.\n\n6. Navigate to the table to which you added the function field.\n\n7. Add the function field to the list.\n\n See [Configure items on forms or in lists using a list collector](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-user-interface/t_UseSlushbuckets.md).\n\n\n### Result\n\nUse the function field like any other field. For example, add it to a form, use it in a filter, or use it in the condition builder.\n\nIf the function definition is invalid, instead of the expected value you receive the message **Invalid function** if the function field is a string type field, or an empty value for other field types.\n\n"}}