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
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
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
This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.
Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.
Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.