country name in advanced query

Hi,

I write custom query for advance report on module Accounts.
Part of query is:
    IFNULL(accounts.billing_address_country, '') 

Field is dropdown list.

In a normal report this field shows full country name
But in custom query it only shows country code with 2 characters.

How can I add full country name of an account to custom query?

/and

  • Hi  , 

    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:

    Fullscreen
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    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.

      '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

  • Thankyou, I will add suggested calculated field to Accounts