Service Desk Practitioners Forum
cancel

Unlimited Custom Fields in SQL DB

SOLVED
Go to solution
Highlighted
KVBockstaele
Valued Contributor.

Unlimited Custom Fields in SQL DB

I am trying to extract data from HP OVSD to copy to another DB. I can retrieve all field values with their relations to other items except for the custom fields in the tables of unlimited custom fields (e.g. SRV_CFT001). I found the table where this data is stored but I couldn't find back the link with the item (e.g. the service on which the field is used).
Can anyone help me find the missing link?
9 REPLIES
George M. Meneg
Acclaimed Contributor.

Re: Unlimited Custom Fields in SQL DB

KVBockstaele
Valued Contributor.

Re: Unlimited Custom Fields in SQL DB

George,

Thanks for your prompt reply. I already had a look at the query in the link you posted, however, in that query there is no real link between a named service and the unlimited custom field. The link which was posted creates a link between the 'entity' service and the custom field. I need to know the value of the custom field for each service.

Thanks for your support,
Kind regards,
K
George M. Meneg
Acclaimed Contributor.
Solution

Re: Unlimited Custom Fields in SQL DB

Hello,

The itsm_services and srv_cft001 are joined on itsm_srv_sft001.itsm_srv_sft001_oid=srv.srv_oid
menes fhtagn
KVBockstaele
Valued Contributor.

Re: Unlimited Custom Fields in SQL DB

Great, thx George; did you try by trial and error? Since SQL doesn't propose this link?
KVBockstaele
Valued Contributor.

Re: Unlimited Custom Fields in SQL DB

George, I know have the link between those two tables, but not yet the values behind the custom field; e.g. custom field of type entity reference to a person.
George M. Meneg
Acclaimed Contributor.

Re: Unlimited Custom Fields in SQL DB

Hello,

If you post exactly what you are trying to do maybe I'll be able to help.
menes fhtagn
George M. Meneg
Acclaimed Contributor.

Re: Unlimited Custom Fields in SQL DB

" Great, thx George; did you try by trial and error? Since SQL doesn't propose this link? "

No, it wasn't trial and error, it was mere observation once I understood the structure of the sd database and the relation between entities and fields.
menes fhtagn
KVBockstaele
Valued Contributor.

Re: Unlimited Custom Fields in SQL DB

I am extracting all Services and their attributes. One of the attributes of a service is an custom field (type=unlimited) which is an entity reference to a person ("Technical Service Responsible").

When extracting my services, I don't know how to extract the value behind this custom field. I can perfectly extract all attribute values of the service and even the standard custom field values of all services. However, I don't know how to link the values behind the custom fields in table SRV_CFT_001 to the Service table. Is this any clearer?
George M. Meneg
Acclaimed Contributor.

Re: Unlimited Custom Fields in SQL DB

Hello,

Ok, in this example the name of the custom person field is TSR (from "Technical Service Responsible").

ITSM_SRV_CFT001 will have at least two columns, the one is ITSM_SRV_CFT001_OID and the other TSR. Since TSR is a person, this column will hold person's OID so you must join it with ITSM_PERSONS on TSM_SRV_CFT001.TSR=PER.PER_OID

To see what I mean just run the following query:

select (lower (tab.tab_acronym) + '.' + col.col_name) as 'from column',
(lower (tab2.tab_acronym) + '.' + col2.col_name)as 'to column'
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 lal.lal_lng_oid=1033 and tab.tab_name like 'itsm_srv_cft%'
order by ent.ent_name asc

Create a left outer join between "from column" to "to column" (this must be left outer join in case that a person is not defined for all services).
menes fhtagn