How do I access Autotask UDFs (user defined fields) for contracts?

Questions
,
1

Please, how do I access Autotask UDFs (user defined fields) for contracts?

The universal sql below gets me the main data but is missing the UDF Data.

select *
from Autotask.Contracts.Contracts@at
limit 1000
2

The values of User Defined Field values are in a different table, together with the actual object (here: contract) data.

To retrieve a list of user defined field values, one row per value, you would issue:

select cttudf.id
,      cttudfname
,      cttudf.value
from   Contracts_ItemsUserDefinedFields cttudf

This also includes the values from the parent (contract), so you can also create a view or query to retrieve the contract data, plus the custom fields transposed using for instance:

--create or replace view ContractsWithUDFs
--as
select *
from   Contracts_ItemsUserDefinedFields cttudf
pivot   ( max(cttudf.value)
          for cttudf.name in
          ( [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-1]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-2]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-3]
          , ...list all custom field names that you need...
          )
        )

Note that Autotask includes values for all User Defined Fields, even when null, so there is no need for a left outer join from Contracts to Contracts_ItemsUserDefinedFields to make sure every contract is included.

An idea has been registered to create these transposed variants automatically.

Please note that a confusing error itgensql704: Duplicate table alias 'p' may occur when the pivot is postfixed with a table alias and the base table retrieved from is also included in the column list:

select cttudf.*
,      p.*
from   Contracts_ItemsUserDefinedFields cttudf
pivot   ( max(cttudf.value)
          for cttudf.name in
          ( [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-1]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-2]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-3]
          )
        ) p

In that case it recommended against retrieve the data from the base table (cttudf here). It’s columns are already included by p.*. The working and probably intended solution is:

select p.*
from   Contracts_ItemsUserDefinedFields cttudf
pivot   ( max(cttudf.value)
          for cttudf.name in
          ( [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-1]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-2]
          , [VALUE-OF-name-COLUMN-FOR-CUSTOM-FIELD-3]
          )
        ) p