Database Field Suffixes

By adding a suffix to a database field, you can change the value that's returned in a report.

When certain database fields are used in report columns, the output is automatically modified to display the data in an easily readable format. For example, the incidents.assgn_acct_id field stores the ID numbers of the agents who have incidents assigned to them. Since report users generally prefer to see agent names instead of ID numbers, the names are returned instead of the ID numbers.

However, there are occasions when you may want the report to return the agents’ ID numbers instead of their names. In such cases, the suffix .id can be added to incidents.assgn_acct_id, changing the column’s expression to incidents.assgn_acct_id.id. This suffix prevents the name associated with the record from being returned automatically and forces the report to instead return the ID number from the database field.

There are several suffixes that can be added to name lookup fields, such as incidents.assgn_acct_id, or currency fields, such as opportunities.mgr_value. Other types of database fields do not accept suffixes. Each suffix has a different purpose.

Suffix	Description
Name lookup field suffixes	These suffixes can be used with database fields that trigger an automatic name lookup when used in reports.
`.id`	This suffix prevents Analytics from performing a name lookup and returns the ID number stored in the field. For example, `answers.assgn_acct_id.id` would return the account ID number of the staff member an answer is assigned to.
`.desc`	This suffix can be used only with name lookup fields that have a description field. The suffix returns the text from the field’s description rather than the field’s ID number or name. For example, `incidents.prod_lvl1_id.desc` would return the text description of the parent product an incident is associated with.
`.name`	This suffix returns the name that's associated with the database field. For example, `answers.map_cat_lvl1_id.name` would return the name of the parent category an answer is associated with. This suffix is rarely used since the name lookup is usually performed by default.
Currency field suffixes	These suffixes can be used with database fields that store currency values.
`.curr_id`	This suffix returns the type of currency the value was stored in, rather than the currency value. For example, `opportunities.mgr_value.curr_id` could return `USD`, `GBP`, or another currency type.
`.rate_id`	This suffix returns the exchange rate that's used to calculate the field’s currency value when returning the value in a different currency format. For example, `opportunities.rep_value.rate_id` could return 1.45000 or another exchange rate.
`.native`	This suffix returns the field’s value in its original currency instead of the currency determined by staff account or interface settings. For example, `opportunities.mgr_value.native` used in a report on a British interface would return the manager forecast value in USD if that value was originally entered on a U.S. interface.