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 table, you need to first show the table. See Show tables for more information about tables.
- Open the map with the feature data you want to see in a table.
- Click Details and click Contents.
- Browse to the feature layer and click Show Table. The table appears at the bottom of your map.
Add a field
- 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 the discussion in the Date functions section.
- 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
- Click the column containing the field you want to delete.
- Click Delete. You are asked to 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
- Click the column containing the field whose values you want to calculate.
- Click Calculate. This opens the Expression Builder dialog box.
Basic operations
In the Expression Builder dialog box, you can build simple expressions using operators such as plus, minus, multiply, and divide.
- 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 this topic.
- 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.
Other 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, 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 tables below 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 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 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 then 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 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
You can assign a date and time to a date field. Operations such as subtracting two dates, or adding time to a date field, are not supported.
Before you begin calculating date fields, there are a few things you should be aware of.
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 the 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:00PM 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:00AM, it is 1:00AM PST (or 2:00AM if daylight savings 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:00AM 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 (seven 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 12PM PST, apply the eight 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.
Date functions
| Function | Description |
|---|---|
CURRENT_DATE()* Note:*If your portal's hosting server uses 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 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 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). |
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, Delete, and Calculate are not available for copies of layers.
- Add, Delete, and Calculate are only available for hosted feature layers. You must be the owner of the hosted feature layer, or an administrator of your organization.
- Calculate is only available if your portal's hosting server uses ArcGIS Data Store.
- Only standardized SQL queries are supported.