Field calculations allow the hosted feature layer owner or organization administrator to alter the values of every row for a single field in the attribute table of a layer.
For example, if you have a hosted feature layer that stores information on property sales—including the sale price and tax rate for the property location—you could add a field to the layer to store estimated property taxes. To populate the estimated_property_taxes field, define a calculation expression for the field that takes the values in the sale_price field and multiplies them by the tax_rate values.
You have two options when writing calculation expressions on fields in layers in a hosted feature layer:
- ArcGIS Arcade—Arcade is ideal for calculations that require more functionality than SQL, as Arcade provides access to attribute values and feature geometry, allowing you to create expressions that include spatial operations. Additionally, if an error occurs while calculating a particular row, you can stop and troubleshoot the problem, and start the calculation again after you correct the problem.
Note:
You must enable editing on the layer to use Arcade for calculations, but the layer cannot have sync enabled. See Manage hosted feature layer editing for information about changing editing and sync settings.
- SQL—Use SQL for the fastest performance with calculations that can be performed with standardized SQL (SQL-92) expressions on nonspatial attributes. You can run SQL on sync-enabled hosted feature layers and layers configured to track feature creators and editors, whereas you cannot run Arcade expressions on such layers.
The next section explains how to calculate values for a field from the item page of a hosted feature layer. Subsequent sections provide examples for common calculations.
Calculate values for a field from the item page
Follow these steps to calculate string, numeric, or date field values in a field from a feature layer's item page.
Note:
Field calculations cannot be undone. For this reason, it is recommended that you add a field, calculate values into it, and confirm the calculation is what you wanted. If it is, you can then calculate the original field to equal the added field. Once you confirm the values are correct in the original field, you can delete the field you added.
- From the layer's item page, click the Data tab to show the table.
- Click the column containing the values you want to calculate.
- Do one of the following to open the Calculate Field dialog box:
- Click Calculate.
- Click Show Detailed View > Calculate.
- Choose the language to use for the calculation, either Arcade or SQL.
If the hosted feature layer has sync enabled or is configured to keep track of who creates and updates features, you do not see this page. Instead, the SQL window appears.
- Compose a calculation expression.
- For SQL, use basic operators, a field list, and functions. Click the Validate button to ensure there are no errors in the expression. If the expression is invalid, click the Remove button and compose a new one. When the expression is complete and valid, click Calculate.
- For Arcade, use global variables, functions, and constants. Click OK to run the expression. If an error occurs when running the expression, you can click Review Error to open the expression window so you can fix the error. Otherwise, click Cancel. If you correct the expression and run it again, the calculation starts over.
The time it takes for the calculation to complete depends on the expression's complexity and the number of features in the layer.
Example calculations
The following sections provide example syntax or methods for performing common calculations in ArcGIS Enterprise.
Perform a mathematical operation on numeric values in two existing fields to populate a third field
One of the most common calculations you perform is to derive a new numeric value based on existing values in your feature layer. For example, you might subtract one year's sales totals for all your stores from sales totals from another year to find the change in profit from one year to the next, or you might divide the total number of residents under 18 years of age by the total population to determine the fraction of the population that is under 18.
Arcade examples
Calculate the difference between the values of two numeric fields, Sales2016 and Sales2017, to populate a numeric field.
$feature.Sales2016 - $feature.Sales2017
SQL examples
Use calculate on a numeric field (SalesDifference) to find the difference between the values in the numeric fields, Sales2016 and Sales2017.
Sales2016 - Sales2017
Populate a numeric field with a decimal number that is the result of calculating what portion of the population is under 18 years of age.
PopUnder18/TotalPop
Concatenate string values from existing fields into a new string field
Another calculation that populates a new field involves combining values from existing string fields. For example, you might have two string fields for room locations in a hotel—Floor and Room—and you want to combine them into a string field that contains both.
In the following examples, values for the Floor and Room fields will be combined into a single field.
Arcade example
Concatenate($feature.room,$feature.floor)
SQL example
CONCAT(Floor,Room)
Remove trailing or leading spaces from string fields
When users type or paste values into a field when they're editing, they can make mistakes and leave a trailing or leading space on the text. You can clean up these mistakes by trimming the values.
In these examples, you know that the editor added a trailing space when pasting New Hampshire into the field that stores state names, so you will trim the space from the end of the string.
Arcade example
Trim('New Hampshire ')
SQL example
Trim(TRAILING ' ' FROM 'New Hampshire ')
Populate a field with different values depending on values in another field
Sometimes, the value you want added to a field varies by feature and depends on another value for the same feature. For example, you can add a string field to the feature layer to store text that describes a numeric or abbreviated string value in another field. As the SQL calculation interface does not support this, use Arcade for these types of calculations.
Arcade example
The following example conditionally writes different string values—None, Low, High, or Other—to a text field based on a number in another field in the layer (HowMany).
When(
$feature.HowMany == 0, "None",
$feature.HowMany == 1, "Low",
$feature.HowMany == 2, "High",
"Other")
Use a filter in Map Viewer Classic
If you don't want to use an Arcade expression, you can apply a filter to the layer in the map, calculate values for the filtered features, remove that filter, apply another filter based on a different value, and calculate values for those fields. This is described in the following steps.
For example, if you have a numeric field that records store types using codes (3, 2, and 1), and you want a string field that spells out what each of these codes represents (chain, franchise, and independent), you can add a string field to the layer, add the layer to the map, filter for each code value, and calculate the value for the string field.
- Add a string field to the hosted feature layer. Set the length to accommodate the longest string you will store.
In the store type example, the new field name is TypeFull.
- On the Overview tab of the layer's item page, click Open in Map Viewer Classic.
- Define a filter on the layer so only features of the same type are present in the map.
For the store example, apply a filter that returns only those features for which the StoreCode field value is 3.
- Open the attribute table for the layer.
- Click the name of the field you added in step 1 and click Calculate.
For this example, click the TypeFull name and click Calculate.
- Type the string you want inserted to this field for these features. Enclose the string inside single quotation marks.
Type 'chain' to populate the TypeFull field for all features that have a StoreCode value of 3.
- Click Calculate.
- Remove the filter you defined in step 3. Open the Filter window for the layer again and click Remove Filter.
- Repeat steps 3 through 8 for the remaining values you need to calculate.
For the store example, define a filter for StoreCode is 2 and calculate TypeFull to store 'franchise'. Remove that filter, define a new filter for StoreCode is 1, and calculate TypeFull to store 'independent'.
- When you finish calculating values for the new field, close the map without saving it.
Replace one value with another
If you need to replace an existing value with another—for example, if the way you represent a particular value has changed or you need to correct mistyped values—you can find all the existing values for a field and replace them with a new value. As the SQL calculation interface does not support this, use Arcade for these types of calculations.
Caution:
Calculations are immediately saved to the feature layer. If you overwrite an existing value in error, you'll need to calculate again to change the value back.
Arcade example
This example uses the Replace function to change the British spelling (colour) to the American spelling.Replace($feature.color, 'colour', 'color')
Use a filter in Map Viewer Classic
Similar to the way you would populate a field based on the values in another field, you can apply a filter to the layer in the map that returns only those features that contain the field value you want to replace. Next, set the filtered fields equal to the new value to update the fields value.
For example, if you need to correct mistyped values or convert a word into an abbreviation, filter for the literal string you need to change and set the field to equal the new value.
- Add the hosted feature layer you need to update to Map Viewer Classic.
You must be the layer owner or a portal administrator.
- Define a filter on the layer so only features that contain the value you want to replace are shown on the map.
For example, if you know a number of features contain Crt values for the StreetType field, apply a filter that returns only those features for which the StreetType field value is Crt.
- Open the attribute table for the layer.
- Click the field name and click Calculate.
For this example, click the StreetType field and click Calculate.
- Type the string you want inserted to this field for these features. Enclose the string inside single quotation marks.
Type 'Ct' to populate the filtered StreetType fields with the corrected abbreviation for Court.
- Click Calculate to apply the changes to the filtered fields.
- Close the map without saving it.
Determine the density of a numeric attribute per feature area
To calculate the density of one attribute in an area, use an Arcade expression, as you cannot perform SQL calculations on the spatial field from the calculation interface.
Arcade example
This example determines population density per feature by dividing the total population value (TotalPop) by the area in square miles of the polygon feature:
$feature.TotalPop / Area ($feature,
'square-miles')
Derive the coordinate for a point feature
You can use an Arcade expression to return the longitude or latitude coordinate for the spatial fields in a hosted feature layer that contains only points.
This type of calculation is not supported on the spatial field from the calculation interface.
Arcade example
This example calculates a field to the x-coordinate for each point in a point layer using the Geometry function.
Geometry($feature).x
Add time to or subtract time from a date
You may want to add time to or subtract time from a date field or date literal value to produce an updated date field. For example, you can calculate a future inspection or review date by adding time to a date.
Arcade example
This example uses the DateAdd function to add seven days to a date to obtain the following week's date.var startDate = Date($feature.dateField);
var oneWeekLater = DateAdd(startDate, 7, 'days');
return oneWeekLater;
SQL examples
Use an INTERVAL query to add time to or subtract time from a date field, as shown in the following calculations. The first example adds three days to a date to result in a new date. The second example subtracts three days from a time stamp field.<DateField> + INTERVAL '3'DAY = updated date
<DateField> - INTERVAL '3 00:00:60' DAY TO SECOND = updated date
You can use INTERVAL with the following date and time values:
- DAY
- HOUR
- MINUTE
- SECOND
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE TO SECOND
Calculate the difference between two dates
You might want to calculate the length of time between two dates. For example, if you have the installation dates for electric meters and you also have inspection dates, you can calculate the difference between the two dates to verify that the length of time between installation and inspection falls within allowable guidelines. The result of the calculation is a number field rather than a date field.
Arcade example
The following example uses the DateDiff function to calculate a person's age by finding the difference between a current date (endDate) and the person's birth date (startDate):var startDate = Date($feature.startDateField);
var endDate = Date($feature.endDateField);
var age = DateDiff(endDate, startDate, 'years');
return age;
SQL examples
Any combination of date fields and date literals can be used to calculate the length of time between two dates. The first calculation below uses a date field, while the second uses a date literal. The third and fourth calculations use both a date field and a date literal.<DateField1> - <DateField2> = number of days in between
DATE'<SQL-supported Date Literal>' - DATE'< SQL-supported Date Literal>' = number of days in between
<DateField1> - DATE'<SQL-supported Date Literal>' = number of days in between
DATE'<SQL-supported Date Literal>' - <DateField2> = number of days in between
The result is a number field that is calculated by subtracting one date field or literal from another date field or literal. The number result (in days) can be a whole number and can also include a fraction—for example, 1.5 would represent one and a half days, or 36 hours.
In the electric meter inspection example mentioned above, any of the following calculations can be used to calculate the length of time between an installation date of 6/1/2015 and an inspection date of 10/1/2015. The first calculation uses date fields, the second uses date literals, and the third and fourth use both a date field and a date literal.<InspectionDateField> - <InstallationDateField> = 122 (days)
DATE'10/1/2015' - DATE'6/1/2015' = 122 (days)
<InspectionDateField> - DATE'6/1/2015' = 122 (days)
DATE'10/1/2015' - <InstallationDateField> = 122 (days)
Considerations when calculating field values
- When you calculate values for a layer in Map Viewer Classic and you have a filter on the layer, only the records that meet the filter criteria will have their values calculated.
- When writing SQL expressions, Calculate Field works only with field names, not with field aliases. The Fields list shows you all the field names available for calculations. You can filter this list by the field types String, Numeric, and Date.
- If you hover over a field name in the Fields list, the field alias and field type are displayed.
- If you click a field name in the Fields list, the field is added to the expression.
- Only standardized SQL queries are supported when calculating field values.
- You cannot use the numeric MOD function on double fields. Cast the field to an integer as shown in the example.
- You cannot write Arcade expressions for hosted feature layers that have sync enabled or are configured to track who created and last updated features.
- You cannot use Arcade to calculate values for the following field data types in a feature layer's item page:
- Big integer
- Date only
- Time only
Note:
When any layer in a hosted feature layer contains these data types, Arcade calculations are disabled for the entire hosted feature layer, even those sublayers that do not contain unsupported data types.
Standardized SQL (SQL-92) reference
When you write an SQL expression to calculate field values, use standardized SQL. This section provides a list of the operators and SQL functions you can use for SQL calculations in ArcGIS Enterprise.
After you create a SQL expression, click the Calculate button. If there are any errors, an error message appears at the bottom of the dialog box. Correct the expression syntax and calculate again.
Operators
On the Calculate Field dialog box, you can build simple SQL expressions using operators such as plus, minus, multiply, and divide. Examples and tips for using these operators are as follows:
- To multiply all values in a numeric field named SAMPLE by 100.0, type SAMPLE * 100.0 for the expression.
- For more complex equations, you can use the parentheses to specify the order of calculations, for example, SAMPLE * (BASELINE - 40).
- Math operators do not work with string fields. You'll need to use the string functions described in the String functions section.
- If you are calculating a field of type double to a field of type integer, the CAST function may be automatically added to your expression. For example, if you're calculating a double field named POP to an integer field named SAMPLE, the expression will appear as CAST(SAMPLE AS FLOAT). Do not remove the CAST function. See Numeric functions below for information on the CAST function.
- To include an apostrophe in the string, use two single quotation marks for the apostrophe. For example, 'Nightingale''s'. Do not use a double quotation mark.
Functions
In addition to simple expressions using operators, you can also use functions to build SQL expressions. Functions work with field names, literals, and other functions. For example, suppose you need to calculate a double field to be TOTALPOP divided by POP18. If any feature has a POP18 equal to zero, the calculation will result in a divide-by-zero error. You can guard against this using the NULLIF function described below. The expression would be TOTALPOP / NULLIF(POP18, 0).
Functions take arguments. In the tables below, any argument can be as follows:
- A field name, as long as the field type matches the argument type (string, number, or date).
- A literal, such as 'Sailboat' (a string surrounded by single quotation marks), the number 5, a date in MM/DD/YYYY hh:mm:ss or YYYY-MM-DD format, or a time in HH:MM:SS format, surrounded by single quotation marks.
- A function that returns a value of the proper type (string, number, or date). For example, FLOOR(POWER(SAMP_ERR, 0.5)) returns the largest integer that is less than or equal to the square root of SAMP_ERR.
For illustrative purposes, the examples in the description column of the following tables mostly use literal arguments. You can substitute a field name or another function for these arguments.
Date functions
Several calculations can be performed on date fields. For example, you can add or subtract time from a date field or calculate the difference between two date fields.
Before working with date fields, read these important considerations.
You can use any combination of date and number fields and literals when calculating date fields. When using date literals, you must use SQL-supported date formats.
The following date functions are available:
| Function | Description |
|---|---|
CURRENT_DATE | Returns the current date in UTC time. What value is displayed depends on the client you're using. In the portal website, dates are displayed in the time zone of your browser. The following example returns all values in the inspection_date field that have a date later than today's date: inspection_date > CURRENT_DATE |
CURRENT_TIMESTAMP | Returns the current UTC date and time (hours, minutes, seconds). What value is displayed depends on the client you're using. In the portal website, time is displayed in the local time of your browser. In this example, all timestamp values prior to today's date and current time (in UTC) are returned for the appointments field: appointments < CURRENT_TIMESTAMP |
EXTRACT(<unit> FROM '<date>') | Returns a single part (<unit>) of the specified <date>. Possible <unit> values include but are not limited to year, month, day, hour, and minute. The following examples extract different units from the date time value 2016-12-21 15:11:44:
|
Numeric functions
| Function | Description |
|---|---|
ABS(<number>) | Returns the absolute (positive) value of the number you specify. |
CAST(<number> AS FLOAT | INT) | Converts a number to a different type. FLOAT converts the specified number to a double and INT converts it to an integer. In the first example below, the number is cast to an integer. Because integers are whole numbers, the result is 1424. In the second example, an integer is cast to a float, which results in a number with decimal places, 1424.0
|
CEILING(<number>) | Returns the smallest integer greater than or equal to the specified number. The following example returns 13: CEILING(12.93) |
COS(<number>) | Returns the trigonometric cosine of <number>, which is assumed to be an angle in radians. |
FLOOR(<number>) | Returns the largest integer that is less than or equal to the specified number. The following example returns 12: FLOOR(12.93) |
LN(<number>,<decimal_place>) | The natural logarithm of the specified number. |
|
LOG(<number>,<decimal_place>) | The base-10 logarithm of the specified number. |
MOD(<number>, <n>) | Returns the remainder after the dividend (<number>) is divided by the divisor <n>. Both <n> and <number> must be of type integer. Examples include the following:
|
NULLIF(<number>, <value>) | Returns null if the specified number equals the specified value. NULLIF is commonly used to prevent divide-by-zero errors by setting <value> to 0. Whenever a calculation encounters a null field value in any of its arguments, the result of the calculation is null. For example, suppose you need to calculate a double field to be TOTALPOP divided by POP18. If any feature has a POP18 value that equals zero, the calculation will result in a divide-by-zero error. You could create a filter to hide records where POP18 is zero, and perform your calculation. A shortcut is to use NULLIF: TOTALPOP / NULLIF(POP18, 0)—returns null if POP18 is equal to zero; otherwise, the value of TOTALPOP / POP18 is returned. |
POWER(<number> , <y>) | Returns the value of the specified number raised to the specified power (<y>). The following example returns 32768: POWER(8,5) |
ROUND(<number> , <length>) | Rounds the number you specify to the specified length. If you use a positive number for the <length>, the number is rounded to the decimal position to the right of the decimal point. When <length> is a negative number, the specified <number> is rounded on the left side of the decimal point. Examples are as follows:
|
SIN(<number>) | Returns the trigonometric sine of the specified <number>, which is assumed to be an angle in radians. |
TAN(<number>) | Returns the tangent of the specified <number>, which is assumed to be an angle in radians. |
TRUNC(<number>,<decimal_place>) | Truncates the <number> at the specified <decimal_place>. A positive <decimal_place> truncates to the decimal position specified. When <decimal_place> is a negative number, the <number> is truncated on the left side of the decimal point. In the first example, the numbers to the right of the decimal place are truncated to include only two numbers, resulting in the value 111.99. In the second example, the numbers to the left of the decimal place are truncated, resulting in the value 100.00.
|
String functions
| Function | Description |
|---|---|
CAST(<string> AS DATE | TIME) | Converts the string to a date or time if the string value is in a supported format. If the string is in the format 'MM/DD/YYYY hh:mm:ss' or 'YYYY-MM-DD', you can cast it to a date. If the string is in the format 'HH:MM:SS', you can cast it to the time only data type (TIME). For example, the follow string can be cast to a date: CAST('1988-05-30' AS DATE) |
CHAR_LENGTH(<string>) | Returns the number of characters in the specified string. The result is an integer. For example, the following statement returns 8: CHAR_LENGTH('Redlands') |
CONCAT(<string1>, <string2>) | Concatenates two string values. Only two strings can be provided. To concatenate more than two strings, nest consecutive CONCAT functions as shown below. The first example below concatenates the letters A and B. The second example shows a nested CONCAT function to concatenate three string values, A, :, and B.
Null values are converted to an empty string. |
CURRENT_USER | When the CURRENT_USER function is included in a query, it acts similar to a variable; the username of the user accessing the hosted feature layer or hosted feature layer view is identified and used in the query. For example, if the user, planner3, is signed in to the organization to access a hosted feature layer view that contains the following query definition, only features where the staffmember field contains the value planner3 are returned to the connecting user: staffmember=current_user In the next example, multiple values are stored in the staffmember field. The following where clause will find the current user name in the text value in the staffmember field even if multiple usernames are stored in the field: where=position(current_user in staffmember)>0 |
POSITION(<substring> in <string>) | Returns the position of the first occurrence of the specified substring in the string you specify. If the specified substring is not found, the result is 0. In the first example below, the result is 5 because the first letter (b) of the substring (boat) is the fifth letter in the string (Sailboat). In the second example, the result is 0, because the substring (motor) is not present in the string.
|
SUBSTRING(<string>, <start>, <length>) | Returns a part of a string value; <start> is an integer index specifying where the returned characters start, and <length> is the number of characters to be returned. See the following examples:
|
TRIM(BOTH | LEADING | TRAILING ' ' FROM <string>) | Returns a string where all leading or trailing spaces are removed from the string you specify. In the following example, a space exists before and after the string, San Bernardino. The keyword BOTH is used to trim the space string (indicated using two single quotation marks with a space between them) from the start and end of the text string: TRIM(BOTH ' ' FROM ' San Bernardino ') This returns the string 'San Bernardino'. |
UPPER(<string>) | Returns a string where all characters are converted to uppercase. In this example, all letters in the string Sailboat are converted to uppercase, resulting in the string 'SAILBOAT': UPPER('Sailboat') |
LOWER(<string>) | Returns a string where all characters are converted to lowercase. In the next example, 'sailboat' is returned: LOWER('Sailboat') |