If you are the owner of a hosted feature layer, or an administrator of your organization, you can add, delete, and calculate fields on hosted feature layers. Some restrictions apply; for example, you cannot delete or calculate the OBJECTID field. For a complete list of restrictions, see the Restrictions section.
To add, delete, or calculate fields on a hosted feature layer, you must first show the layer's attribute table. See Show tables for more information about tables.
Add a field
- Follow the steps to show a table.
- Click Table Options.
- Click Add Field.
- Name—Field names should not contain special characters, such as spaces, hyphens, parentheses, brackets, or characters such as $, %, and #. Avoid using field names that contain words that are typically considered database-reserved keywords, such as CHARACTER or DATE. If you attempt to add a field name with special characters, or a database-reserved keyword, you'll receive an error message.
- Alias—An alternative name for a field that is more descriptive and user-friendly than the name. The alias can contain special characters and database-reserved keywords.
- Type—One of the following:
- Date—Date and time. See Work with date fields.
- Double—Numbers with decimal places.
- Integer—Whole numbers from -2,147,483,648 to 2,147,483,647 (long integer).
- String—Any sequence of characters.
- Length—The maximum length, in characters, that a string field can store. Length is required for the String field type. The default is 256 characters.
Delete a field
- Follow the steps to show a table.
- Click the column containing the field you want to delete.
- Click Delete. Confirm that you want the field deleted.
For certain fields, such as OBJECTID, CREATIONDATE, CREATOR, EDITDATE, and EDITOR, and fields used in your map for styling and filters, the Delete option is unavailable. However, other maps may be using fields for symbology and filters that are different than the fields your map uses, and Delete is available for these fields. Exercise caution when deleting fields that may be used for symbology and filtering in other maps.
Calculate a field
- Follow the steps to show a table.
- Click the column containing the field whose values you want to calculate.
- Click Calculate.
The Expression Builder dialog box appears.
Basic operations
On the Expression Builder dialog box, you can build simple expressions using operators such as plus, minus, multiply, and divide. Examples and tips 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 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.
After you've created your expression, click the Calculate button. If there are any errors, an error message will appear at the bottom of the dialog box.
The following are additional considerations:
- If you have a filter on your layer, only the records that meet the filter criteria will have their values calculated.
- The Expression Builder works only with field names, not 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.
Use functions
In addition to simple expressions using operators, you can also use functions. 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, or a date in MM/DD/YYYY hh:mm:ss format, surrounded by single quotation marks.
- If your organization allows nonstandard SQL queries, non-English characters in a string literal must be escaped with N (for example, N'针叶林').
- 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.
String functions
| Function | Description |
|---|---|
CHAR_LENGTH(string) | Returns the number of characters in string. The result is an integer. Example
|
CONCAT(string1, string2) | Concatenates two strings. Only two strings can be provided. To concatenate more than two strings, nest consecutive CONCATs as shown in the example below. Example
Null values are converted to an empty string. |
POSITION(substring, string )* Note:*If your portal's hosting server uses ArcGIS Data Store or a managed database in PostgreSQL, you must use POSITION(substring in string) instead. | Returns the position of the first occurrence of substring in string. If substring is not found, the result is 0. Examples
|
SUBSTRING(string, start, length) | Returns a part of string; start is an integer index specifying where the returned characters start, and length is the number of characters to be returned. Example
|
TRIM(BOTH | LEADING | TRAILING ' ' FROM string) | Returns a string where all leading or trailing spaces are removed from string. Example
Note that the second argument is two single quotation marks with a space in between. |
UPPER(string) | Returns a string where all characters in string are converted to uppercase. Example
|
LOWER(string) | Returns a string where all characters in string are converted to lowercase. Example
|
Numeric functions
In addition to the numeric functions described below, you can calculate statistics on columns of data. These statistics include the number of records, the minimum and maximum value, the sum of all values, the average value, and the standard deviation of all values.
- Click the column containing the field for whose values you want to obtain statistics.
- Click Statistics. A dialog box shows the calculated statistics.
| Function | Description |
|---|---|
ABS(number) | Returns the absolute (positive) value of number. |
CEILING(number) | Returns the smallest integer greater than or equal to number. Example
|
COS(number) | Returns the trigonometric cosine of number, which is assumed to be an angle in radians. |
CAST(number AS FLOAT | INT) | Converts a number to a different type. FLOAT converts number to a double and INT converts to an integer. |
FLOOR(number) | Returns the largest integer that is less than or equal to number. Example
|
LOG(number)* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use LN(number, decimal_place) instead. | The natural logarithm of number. |
|
LOG10(number)* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use LOG(number, decimal_place) instead. | The base-10 logarithm of number. |
MOD(number, n) | Returns the remainder after the division of n is performed on number. Both n and number must be of type integer. Example
|
NULLIF(number, value) | Returns null if number equals 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 POP18 equal to 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.
|
POWER(number , y) | Returns the value of number raised to the specified power y. |
ROUND(number , length) | Rounds number to the specified length. If length is a positive number, the number is rounded to the decimal position specified by length. When length is a negative number, it is rounded on the left side of the decimal point. Examples
|
SIN(number) | Returns the trigonometric sine of number, which is assumed to be an angle in radians. |
TAN(number) | Returns the tangent of number, which is assumed to be an angle in radians. |
TRUNCATE(number, decimal_place)* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use TRUNC(number, decimal_place) instead. | Truncates number at the specified decimal_place. A positive decimal_place truncates to the decimal position specified. When decimal_place is a negative number, it is truncated on the left side of the decimal point. Examples
|
Date functions
A few functions are available for date fields. For a list of date functions and to learn more about how to work with date fields in attribute tables, see Work with date fields.
Work with date fields
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.
Date functions
The following date functions are available:
| Function | Description |
|---|---|
CURRENT_DATE()* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use CURRENT_DATE instead. | Returns the current date in UTC time. |
CURRENT_TIME()* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use CURRENT_TIME instead. | Returns the current UTC date and time (hours, minutes, seconds). |
CURRENT_TIMESTAMP()* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use CURRENT_TIMESTAMP instead. | Returns the current UTC date and time (hours, minutes, seconds, milliseconds). |
EXTRACT(unit FROM date)* Note:*If your portal's hosting server uses a relational data store created through ArcGIS Data Store or a managed database in PostgreSQL, you must use CURRENT_DATE instead. | Returns a single part of a date/time, such as year, month, day, hour, minute, and so on. For example
|
Calculate date fields
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.
Add time to or subtract time from a date
You may want to add time to or subtract time from a date field or an SQL-supported date literal to produce an updated date field. For example, you can calculate a future inspection or review date by adding time to a date.
The following calculations can be used to add time to or subtract time from a date field. The first one uses date fields and the second uses date literal values. Number fields and number literals can also be used. Any combination of fields and literals is supported, as shown in the third and fourth calculations.<DateField> + / - <NumberField> = updated dateDATE'<SQL-supported Date Literal>' + / - <Number of Days> = updated date<DateField> +/- <Number of Days> = updated dateDATE'<SQL-supported Date Literal >' + / - <NumberField> = updated date
The calculated date field is the original date plus or minus the number of days you want to add or subtract. The number of 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 following example, a piece of machinery is installed 6/14/2016 at 10:00 a.m. You could use either of the following calculations to produce an inspection date one month (30 days) after this installation date. The first calculation uses a date field with a value of 6/14/2016 and a number field with a value of 30, while the second one uses a date literal and a number literal.<MyDateField> + <MyNumberField> = 7/14/2016 10:00 AMDATE'6/14/2016' + 30 = 7/14/2016 10:00 AM
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.
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 betweenDATE'<SQL-supported Date Literal>' - DATE'< SQL-supported Date Literal>' = number of days in between<DateField1> - DATE'<SQL-supported Date Literal>' = number of days in betweenDATE'<SQL-supported Date Literal>' - <DateField2> = number of days in between
The result is a number field that is calculated by subtracting one date field/literal from another date field/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 for date fields
The following information is important to consider when working with date fields.
Change display format of date field
You can change how dates are displayed in the table. Tables display with the same formatting configured in pop-ups. You can change the display format of a date field by selecting the field alias in the Configure Attributes window of Pop-up Properties.
Enter dates in MM/DD/YYYY hh:mm:ss format
Regardless of how dates are displayed in the table, you can only enter dates in MM/DD/YYYY hh:mm:ss format, enclosed in single quotation marks. For example, to set a date field to 5:00 PM on May 27, 2014, you would type '05/27/2014 05:00:00PM', '05/27/2014 5:00PM', or '05/27/2014 17:00:00' (24-hour clock). Storing time alone is not supported—time must always be a component of a date.
Dates are entered and stored in UTC time
Date fields in tables are assumed to contain UTC date and time. UTC stands for Universal Time Coordinated, and it is nearly equivalent to the current time at Earth's prime meridian (zero degrees longitude), located near Greenwich, England. UTC is a more precise version of GMT (Greenwich Mean Time). Dates are assumed to be UTC because the physical location of the server hosting your data can be anywhere in the world. The alternative of storing date and time in a local time zone leads to all sorts of problems, especially if you, or the server hosting your data, move to another time zone.
Whenever a date field is displayed, the date is converted from UTC time into your local time. This is done by querying your computer and asking it for its time zone setting. For example, suppose your computer is set to pacific standard time (PST). PST is eight hours behind UTC—when UTC time is 9:00 a.m, it is 1:00 a.m. PST (or 2:00 a.m. if daylight saving time is in effect).
Any date you enter in an expression is assumed to be UTC time, not the time in your current time zone. This assumption can lead to confusion. For example, if you enter '05/27/2014 05:00:00PM' using a computer set to PST, it will display as 5/27/2014 9:00 AM due to the time conversion described above.
To account for the UTC-to-local-time-zone conversion, here are a few tips:
- If you need to enter your current date and time, use one of the time functions below, as these functions always return UTC time.
- If you are entering a date without a time component, add 12 hours to the date. For example, instead of entering '5/27/2014', enter '5/27/2014 12:00PM' or '5/27/2014 12:00'. The reason this offset is needed is that when a date is entered without a time component, it is entered as midnight on that date (5/27/2014 is converted to 5/27/2014 00:00:00), and when it is displayed in your local time zone, hours are added or subtracted. For example, if you are in PST and enter 5/27/2014, it will display as 5/26/2014 (7 hours subtracted from midnight on the 27th).
- If you are entering a date with a time component, apply your time zone offset to the time. For example, if you need to enter 5/27/2014 12 PM PST, apply the 8-hour difference and enter '5/27/2014 8:00PM'. To find your time zone offset, you can search the web using a search phrase such as UTC time difference calculator.
- To view the contents of your date field in UTC time, you can add a string field to your table and calculate it to be equal to your date field. The date will be converted to a string without the time zone offset. This string will also contain milliseconds.
Restrictions
- Delete is not available for the OBJECTID field or fields used for symbology, time slider, filter, labels, or editor tracking. However, be aware that other maps may be using fields for symbology and filters that are different than the fields your map uses, and Delete is available for these fields. Exercise caution when deleting fields that may be used for symbology and filtering in other maps.
- Add Field, Delete, and Calculate are not available for copies of layers or for hosted feature layers that have related tile layers.
- Add Field, Delete, and Calculate are only available for hosted feature layers and tables. You must be the owner of the layer or an administrator of your organization to use this functionality.
- Only standardized SQL queries are supported.