Discussions

Make picklist labels available in insights

There are times where you have system fields that are integrated which contain technical values. For most cases you can use a picklist on the field to translate it into a readable value.

It would be a huge help to be able to choose if you want to see label or value when adding this field to insights. The only workaround I have found is to use formulas (case) to translate the values which is not ideal.

2 votes

Active · Last Updated

Comments

  • Sebastiaan Van Dingenen-iODigital
    Sebastiaan Van Dingenen-iODigital Posts: 130 Bronze Medal
    edited Jun 10, 2022 12:30PM

    In case someone is interested in the formula way of working:

    • Create a report with the original field
    • Edit the field and click on Edit formula
    • Use the below case structure, replace the field with the fieldname already present in the edit screen and replace the options with your use case
    • Click on Custom Heading to avoid having a bad column name (it will show the formula otherwise)

    The structure is basically:

    • CASE "YourObject"."YourField" WHEN 'X' THEN 'Translated X' WHEN 'Y' THEN 'Translated Y' ELSE 'No match' END

    Example for country translation:

    CASE "Contact"."Contact Country" WHEN 'US' THEN 'United States' WHEN 'GB' THEN 'United Kingdom' WHEN 'CA' THEN 'Canada' WHEN 'IN' THEN 'India' WHEN 'NL' THEN 'Netherlands' WHEN 'AU' THEN 'Australia' WHEN 'ZA' THEN 'South Africa' WHEN 'FR' THEN 'France' WHEN 'DE' THEN 'Germany' WHEN 'SG' THEN 'Singapore' WHEN 'SE' THEN 'Sweden' WHEN 'BR' THEN 'Brazil' WHEN 'AF' THEN 'Afghanistan' WHEN 'AX' THEN 'Åland Islands' WHEN 'AL' THEN 'Albania' WHEN 'DZ' THEN 'Algeria' WHEN 'AS' THEN 'American Samoa' WHEN 'AD' THEN 'Andorra' WHEN 'AO' THEN 'Angola' WHEN 'AI' THEN 'Anguilla' WHEN 'AQ' THEN 'Antarctica' WHEN 'AG' THEN 'Antigua and Barbuda' WHEN 'AR' THEN 'Argentina' WHEN 'AM' THEN 'Armenia' WHEN 'AW' THEN 'Aruba' WHEN 'AU' THEN 'Australia' WHEN 'AT' THEN 'Austria' WHEN 'AZ' THEN 'Azerbaijan' WHEN 'BS' THEN 'Bahamas' WHEN 'BH' THEN 'Bahrain' WHEN 'BD' THEN 'Bangladesh' WHEN 'BB' THEN 'Barbados' WHEN 'BY' THEN 'Belarus' WHEN 'BE' THEN 'Belgium' WHEN 'BZ' THEN 'Belize' WHEN 'BJ' THEN 'Benin' WHEN 'BM' THEN 'Bermuda' WHEN 'BT' THEN 'Bhutan' WHEN 'BO' THEN 'Bolivia' WHEN 'BQ' THEN 'Bonaire Sint Eustatius and Saba' WHEN 'BA' THEN 'Bosnia and Herzegovina' WHEN 'BW' THEN 'Botswana' WHEN 'BV' THEN 'Bouvet Island' WHEN 'BR' THEN 'Brazil' WHEN 'IO' THEN 'Brit/Indian Ocean Terr.' WHEN 'BN' THEN 'Brunei Darussalam' WHEN 'BG' THEN 'Bulgaria' WHEN 'BF' THEN 'Burkina Faso' WHEN 'BI' THEN 'Burundi' WHEN 'KH' THEN 'Cambodia' WHEN 'CM' THEN 'Cameroon' WHEN 'CA' THEN 'Canada' WHEN 'CV' THEN 'Cape Verde' WHEN 'KY' THEN 'Cayman Islands' WHEN 'CF' THEN 'Central African Republic' WHEN 'TD' THEN 'Chad' WHEN 'CL' THEN 'Chile' WHEN 'CN' THEN 'China' WHEN 'CX' THEN 'Christmas Island' WHEN 'CC' THEN 'Cocos (Keeling) Islands' WHEN 'CO' THEN 'Colombia' WHEN 'KM' THEN 'Comoros' WHEN 'CG' THEN 'Congo' WHEN 'CD' THEN ' Congo The Dem. Republic Of' WHEN 'CK' THEN 'Cook Islands' WHEN 'CR' THEN 'Costa Rica' WHEN 'CI' THEN 'Cote d Ivoire' WHEN 'HR' THEN 'Croatia' WHEN 'CU' THEN 'Cuba' WHEN 'CY' THEN 'Cyprus' WHEN 'CZ' THEN 'Czech Republic' WHEN 'DK' THEN 'Denmark' WHEN 'DJ' THEN 'Djibouti' WHEN 'DM' THEN 'Dominica' WHEN 'DO' THEN 'Dominican Republic' WHEN 'EC' THEN 'Ecuador' WHEN 'EG' THEN 'Egypt' WHEN 'SV' THEN 'El Salvador' WHEN 'GQ' THEN 'Equatorial Guinea' WHEN 'ER' THEN 'Eritrea' WHEN 'EE' THEN 'Estonia' WHEN 'ET' THEN 'Ethiopia' WHEN 'FK' THEN 'Falkland Islands' WHEN 'FO' THEN 'Faroe Islands' WHEN 'FJ' THEN 'Fiji' WHEN 'FI' THEN 'Finland' WHEN 'FR' THEN 'France' WHEN 'GF' THEN 'French Guiana' WHEN 'PF' THEN 'French Polynesia' WHEN 'TF' THEN 'French Southern Terr.' WHEN 'GA' THEN 'Gabon' WHEN 'GM' THEN 'Gambia' WHEN 'GE' THEN 'Georgia' WHEN 'DE' THEN 'Germany' WHEN 'GH' THEN 'Ghana' WHEN 'GI' THEN 'Gibraltar' WHEN 'GB' THEN 'United Kingdom' WHEN 'GR' THEN 'Greece' WHEN 'GL' THEN 'Greenland' WHEN 'GD' THEN 'Grenada' WHEN 'GP' THEN 'Guadeloupe' WHEN 'GU' THEN 'Guam' WHEN 'GT' THEN 'Guatemala' WHEN 'GN' THEN 'Guinea' WHEN 'GW' THEN 'Guinea-Bissau' WHEN 'GY' THEN 'Guyana' WHEN 'HT' THEN 'Haiti' WHEN 'HM' THEN 'Heard/McDonald Isls.' WHEN 'HN' THEN 'Honduras' WHEN 'HK' THEN 'Hong Kong' WHEN 'HU' THEN 'Hungary' WHEN 'IS' THEN 'Iceland' WHEN 'IN' THEN 'India' WHEN 'ID' THEN 'Indonesia' WHEN 'IR' THEN 'Iran' WHEN 'IQ' THEN 'Iraq' WHEN 'IE' THEN 'Ireland' WHEN 'IL' THEN 'Israel' WHEN 'IT' THEN 'Italy' WHEN 'JM' THEN 'Jamaica' WHEN 'JP' THEN 'Japan' WHEN 'JO' THEN 'Jordan' WHEN 'KZ' THEN 'Kazakhstan' WHEN 'KE' THEN 'Kenya' WHEN 'KI' THEN 'Kiribati' WHEN 'KP' THEN 'Korea (North)' WHEN 'KR' THEN 'Korea (South)' WHEN 'KW' THEN 'Kuwait' WHEN 'KG' THEN 'Kyrgyzstan' WHEN 'LA' THEN 'Laos' WHEN 'LV' THEN 'Latvia' WHEN 'LB' THEN 'Lebanon' WHEN 'LS' THEN 'Lesotho' WHEN 'LR' THEN 'Liberia' WHEN 'LY' THEN 'Libya' WHEN 'LI' THEN 'Liechtenstein' WHEN 'LT' THEN 'Lithuania' WHEN 'LU' THEN 'Luxembourg' WHEN 'MO' THEN 'Macau' WHEN 'MK' THEN 'Macedonia' WHEN 'MG' THEN 'Madagascar' WHEN 'MW' THEN 'Malawi' WHEN 'MY' THEN 'Malaysia' WHEN 'MV' THEN 'Maldives' WHEN 'ML' THEN 'Mali' WHEN 'MT' THEN 'Malta' WHEN 'MH' THEN 'Marshall Islands' WHEN 'MQ' THEN 'Martinique' WHEN 'MR' THEN 'Mauritania' WHEN 'MU' THEN 'Mauritius' WHEN 'YT' THEN 'Mayotte' WHEN 'MX' THEN 'Mexico' WHEN 'FM' THEN 'Micronesia' WHEN 'MD' THEN 'Moldova' WHEN 'MC' THEN 'Monaco' WHEN 'MN' THEN 'Mongolia' WHEN 'ME' THEN 'Montenegro' WHEN 'MS' THEN 'Montserrat' WHEN 'MA' THEN 'Morocco' WHEN 'MZ' THEN 'Mozambique' WHEN 'MM' THEN 'Myanmar' WHEN 'MP' THEN 'N. Mariana Isls.' WHEN 'NA' THEN 'Namibia' WHEN 'NR' THEN 'Nauru' WHEN 'NP' THEN 'Nepal' WHEN 'NL' THEN 'Netherlands' WHEN 'AN' THEN 'Netherlands Antilles' WHEN 'NC' THEN 'New Caledonia' WHEN 'NZ' THEN 'New Zealand' WHEN 'NI' THEN 'Nicaragua' WHEN 'NE' THEN 'Niger' WHEN 'NG' THEN 'Nigeria' WHEN 'NU' THEN 'Niue' WHEN 'NF' THEN 'Norfolk Island' WHEN 'NO' THEN 'Norway' WHEN 'OM' THEN 'Oman' WHEN 'PK' THEN 'Pakistan' WHEN 'PW' THEN 'Palau' WHEN 'PS' THEN 'Palestine' WHEN 'PA' THEN 'Panama' WHEN 'PG' THEN 'Papua New Guinea' WHEN 'PY' THEN 'Paraguay' WHEN 'PE' THEN 'Peru' WHEN 'PH' THEN 'Philippines' WHEN 'PN' THEN 'Pitcairn' WHEN 'PL' THEN 'Poland' WHEN 'PT' THEN 'Portugal' WHEN 'PR' THEN 'Puerto Rico' WHEN 'QA' THEN 'Qatar' WHEN 'RE' THEN 'Reunion' WHEN 'RO' THEN 'Romania' WHEN 'RU' THEN 'Russian Federation' WHEN 'RW' THEN 'Rwanda' WHEN 'KN' THEN 'Saint Kitts and Nevis' WHEN 'LC' THEN 'Saint Lucia' WHEN 'WS' THEN 'Samoa' WHEN 'SM' THEN 'San Marino' WHEN 'ST' THEN 'Sao Tome/Principe' WHEN 'SA' THEN 'Saudi Arabia' WHEN 'SN' THEN 'Senegal' WHEN 'RS' THEN 'Serbia' WHEN 'CS' THEN 'Serbia and Montenegro' WHEN 'SC' THEN 'Seychelles' WHEN 'SL' THEN 'Sierra Leone' WHEN 'SG' THEN 'Singapore' WHEN 'SK' THEN 'Slovakia' WHEN 'SI' THEN 'Slovenia' WHEN 'SB' THEN 'Solomon Islands' WHEN 'SO' THEN 'Somalia' WHEN 'ZA' THEN 'South Africa' WHEN 'ES' THEN 'Spain' WHEN 'LK' THEN 'Sri Lanka' WHEN 'SH' THEN 'St. Helena' WHEN 'PM' THEN 'St. Pierre and Miquelon' WHEN 'VC' THEN 'St. Vincent and Grenadines' WHEN 'SD' THEN 'Sudan' WHEN 'SR' THEN 'Suriname' WHEN 'SJ' THEN 'Svalbard/Jan Mayen Isls.' WHEN 'SZ' THEN 'Swaziland' WHEN 'SE' THEN 'Sweden' WHEN 'CH' THEN 'Switzerland' WHEN 'SY' THEN 'Syria' WHEN 'TW' THEN 'Taiwan' WHEN 'TJ' THEN 'Tajikistan' WHEN 'TZ' THEN 'Tanzania' WHEN 'TH' THEN 'Thailand' WHEN 'TL' THEN 'Timor-Leste' WHEN 'TG' THEN 'Togo' WHEN 'TK' THEN 'Tokelau' WHEN 'TO' THEN 'Tonga' WHEN 'TT' THEN 'Trinidad and Tobago' WHEN 'TN' THEN 'Tunisia' WHEN 'TR' THEN 'Turkey' WHEN 'TM' THEN 'Turkmenistan' WHEN 'TC' THEN 'Turks/Caicos Isls.' WHEN 'TV' THEN 'Tuvalu' WHEN 'UG' THEN 'Uganda' WHEN 'UA' THEN 'Ukraine' WHEN 'AE' THEN 'United Arab Emirates' WHEN 'US' THEN 'United States' WHEN 'UM' THEN 'US Minor Outlying Is.' WHEN 'UY' THEN 'Uruguay' WHEN 'UZ' THEN 'Uzbekistan' WHEN 'VU' THEN 'Vanuatu' WHEN 'VA' THEN 'Vatican City' WHEN 'VE' THEN 'Venezuela' WHEN 'VN' THEN 'Vietnam' WHEN 'VG' THEN 'Virgin Islands (British)' WHEN 'VI' THEN 'Virgin Islands (U.S.)' WHEN 'WF' THEN 'Wallis/Futuna Isls.' WHEN 'EH' THEN 'Western Sahara' WHEN 'YE' THEN 'Yemen' WHEN 'ZM' THEN 'Zambia' WHEN 'ZW' THEN 'Zimbabwe' ELSE 'Unknown Country' END