How to write an AdHoc query to select UDF data

The best way to get UDF values is to use V_UDF, which unifies all the different UDF types into a single output column.

The query will look similar to this:

SQL

SELECT
SVC.SERVICE_ID_FMT,
SVC_UDF_1.VALUE AS UDF_1_VALUE,
SVC_UDF_2.VALUE AS UDF_2_VALUE
FROM SERVICES SVC

LEFT JOIN V_UDF AS SVC_UDF_1 ON
        -- This identifies the table association between the value and the UDF.
        SVC_UDF_1.VALUE_TABLE_NAME = 'SERVICES'

        -- Link the UDF Value to the Service it was saved on
        AND SVC.RECID = SVC_UDF_1.VALUE_TABLE_RECID

        -- Get the exact UDF that appears on the Service
        AND SVC_UDF_1.IDENTIFIER = 'UDF_ID_1'

LEFT JOIN V_UDF AS SVC_UDF_2 ON
       SVC_UDF_2.VALUE_TABLE_NAME = 'SERVICES'
       AND SVC.RECID = SVC_UDF_2.VALUE_TABLE_RECID
       AND SVC_UDF_2.IDENTIFIER = 'UDF_ID_2'

In order to create and AdHoc Query, Users will need to access the AdHoc Query Builder. To build a new query click the Unknown Attachment button. This button is located in the bottom left corner of the Grid.

The UDF Identifier can be obtained by opening the UDF Grid at Admin > User Defined Fields > User Defined Fields.