Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

SD4.5 custom field table in database need help

Highlighted

SD4.5 custom field table in database need help

Hi all SD experts,

I have configured a custom field (code), Department for Person. I need to identify which table this field is in. I would like to extract out this data to generate the Service call report that is grouped by Department.

Can this be done?

Many thanks
3 REPLIES
Ruth Porter
Honored Contributor

Re: SD4.5 custom field table in database need help

Hi there,

If you use generate DB views and use them for reporting, it should be much simpler and you should not need to know the tabe structure.

If you do need to do at table level, I think they are in ITSM_CODES and their names in ITSM_CODES_LOCALE and they link to the custom code entry in the table for the relevant item type.

Hope this helps, Ruth
http://www.teamultra.net
Bhuvnesh
Frequent Visitor

Re: SD4.5 custom field table in database need help

Hi,
Your person code field will be in ITSM_PER_CUSTOM_FIELDs and would be named PEC_COD*_OID where * is the number ex: PEC_COD1_OID.
This field will only store the OID and actual text is stored in ITSM_CODES table.
George M. Meneg
Honored Contributor

Re: SD4.5 custom field table in database need help

Execute the query bellow:

select ent.ent_name, lal.LAL_TEXT as 'Label',
col.col_name as 'column', tab.tab_name,
atr.atr_name, atr.atr_computed as 'computed?', atr.atr_customfield as 'custom?',
atr.atr_custfieldact as 'activated?' ,att.att_name as 'type',
ent.ent_name as parent_ent, tab2.tab_name + '.' + col2.col_name as 'linked to'
from ifc_columns col
from ifc_columns col
inner join ifc_tables tab on col.col_tab_oid=tab.tab_oid
inner join ifc_attributes atr on col.col_atr_oid=atr.atr_oid
inner join ifc_attrib_types att on att.att_oid=atr.atr_att_oid
inner join rep_labels_locale lal on atr.atr_lbl_oid=lal.lal_lbl_oid
inner join ifc_entities ent on col.col_ent_ref=ent.ent_oid
inner join ifc_entities ent2 on atr.atr_ent_oid=ent2.ent_oid
left outer join ifc_columns col2 on col.rel_col_to=col2.col_oid
left outer join ifc_tables tab2 on col2.col_tab_oid=tab2.tab_oid
where tab.tab_name
in ('itsm_persons', 'itsm_per_custom_fields', 'itsm_per_cft001')
and col2.col_name is not null --comment this to get all fields not just entity references
and lal.lal_lng_oid= --usually 1033
and atr.atr_name=

substitute with your language oid (1033 for US English) and with name of your attribute. If you comment the "and atr.atr_name=" you will get a list of all attributes of entity reference type for persons. If you also comment
"and col2.col_name is not null" then you will get a list of all person attributes, not just entity references.
menes fhtagn
//Add this to "OnDomLoad" event