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

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.