country name in advanced query

Hi Andreas Pfau , 

Sugar's standard reporting natively displays the dropdown display value as you have noted. The custom queries available in the Advanced Reports module directly queries the database and does not model the results, so that is why you see the 2 character code corresponding to your dropdown list entry. In order to display a country name, your query would need to use a CASE statement in the SELECT portion of your query like the following:

SELECT 
    id, 
    name, 
    CASE 
        WHEN 
            billing_address_country = 'US' 
        THEN 
            'United States' 
        WHEN 
            billing_address_country = 'CA' 
        THEN 
            'Canada' 
        WHEN 
            billing_address_country = 'UK' 
        THEN 
            'United Kingdom' 
        ELSE 
            billing_address_country 
    END 
FROM 
    accounts
WHERE
    deleted = 0;

The ELSE portion of that query ensures that value of the billing_address_country will be displayed if you did not account for one of the possible values in the WHEN statements. If you do not include the ELSE portion, then any values returned that do not have a matching option in the WHEN statements will display as blank in the query results.

Chris

The name of the country is defined in the dropdown which is in the metadata, on the file system, not in the database.

This means that you can't get the full name of the country from a query.

 Chris Raffle 's suggestion works well, though you have to introduce the case statement in every advanced report that uses the country.

An alternative would be to create a custom calculated field on Account and populate that with the full country name. Then use that field in your query for the report output.

There is an article in the Sugar Knowledge Base that explains how to copy the value of a dropdown in a calculated field.

https://support.sugarcrm.com/knowledge_base/studio_and_module_builder/sugar_logic/calculated_field__copying_dropdown_value_to_a_text_field/

You would need to recalculate all your Accounts (you can do so from the list view's action menu) to make sure the value is inserted in the formula field:
 https://support.sugarcrm.com/knowledge_base/studio_and_module_builder/sugar_logic/introduction_to_calculated_fields/#Automatically_Updating_Calculated_Values

FrancescaS