How can I determine the database ID values of various fields and records? Can I add the field ID as an output column in a report?
Environment:
Analytics, Explorers
Resolution:
There are a variety of ways to view the database ID value for fields and records.
In most cases, go to the area where you would edit the field. You can typically place the mouse directly over the value of interest and a small pop-up will appear that indicates the ID for that field. You can use this feature with system fields such as profiles, groups, staff accounts, countries, products, categories, dispositions, incidents, answers, statuses, access level and custom fields.
For example, to determine the database ID for a specific staff profile, go to Configuration > Staff Management > Profiles. Then, in the list of profiles, hover over the specific profile you are interested in and the ID value displays in a pop-up.
Note: The contents of the Notes field also displays when you hover over the field name. This allows you to more easily review internal notes for each field.
Click the 
next to the appropriate heading below to expand that section for viewing.
1. If it's not already visible, open the Data Dictionary (click the Data Dictionary button on the Home ribbon)
2. In the left pane, select the table you are interested in (i.e. incidents)
3. In the right pane, on the field that you would like to see the IDs for, right-click and select View Data Definition
That will open a window that shows all the IDs for the selected field.
The Data Dictionary may also be available from the Navigation area:
Configuration > Database > Data Dictionary
By Creating a Custom Report (i.e. adding ID output column)
If you would like to make the data IDs available to people that don't have access to the report editor, it is possible to obtain database IDs for certain custom menu items by creating a simple custom report. To do so, follow the steps below:
1. Create a new custom report
2. Output the field in question as a column (e.g., incidents.status_id)
3. Output the same field as a column again, but with ".id" appended to the field name (e.g., incidents.status_id.id)
4. Save and generate the report
This should provide something similar to the following, for example:
| STATUS ID NAME | STATUS ID # |
| Pending | 4 |
| Review | 28 |
| Development | 7 |
| Legal | 124 |
...and so forth.
The database values are helpful to know when using particular fields in reports. For example, a report may use the transactions table joined to the incidents table to report on incidents that have undergone a status change transaction (transactions.trans_type = 6). The "transactions.id1" field can be output in the report, which will display the status ID number of the status the incident was changed to, not the status ID name. Therefore, it is helpful to have the corresponding status ID numbers available to interpret this information.
You can add the ID column to any explorer window using the Choose Details... button. This allows you to easily determine the ID for the feature listed in the explorer.
1. Open the explorer associated with reports, navigation sets or workspaces.
2. In the top portion of the explorer, click Choose Details...
3. Click the ID checkbox and click OK.
When the pop-up window closes, the explorer window will refresh and the ID column will now be visible.
