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

Entity reference from ESC to Person?

SOLVED
Go to solution
Highlighted

Entity reference from ESC to Person?

Hi all.

Is it possible to make an entity reference from E-mail service call to Person? When I try to do this, only E-mail Priority mapping, E-amil source, and Service call is available.

Thanks for any help,
Michael
13 REPLIES
Raman Shcharbak
Frequent Visitor

Re: Entity reference from ESC to Person?

It seems that SD does not allow this. I have the same entities list for e-mail servicecall.
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Hello Michael,

Officially you cannot do that. Unofficially you can providing that you don't mind updating directly the sd database.

However, you can do this at a test environment (where you don't care messing around with the db) and through ACES export it and import it to the production.

If you are interested tell me and I'll post instructions.
menes fhtagn

Re: Entity reference from ESC to Person?

Hi George.

Yes I'm interested, but what consequences would this have?

Could it cause trouble in the future?

Michael
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Hello Michael,

I don't think that it will cause you any trouble and it will be pretty easy to "reverse" the effects. However this is a chance you have to take.
menes fhtagn

Re: Entity reference from ESC to Person?

I'll take the chance George.
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Hello Michael,

Try to import through aces the attached xml file after doing some editing.

Open the xml file with a unicode aware editor and at the third line edit the value of "SysVersion" to the version of your sd_server.

Execute the query
select sys_version from ifc_system
to get the correct value.

Also the following lines might need editing to reflect your system:

633318790267031

555155484

To be sure execute these queries:
select ent_oid from ifc_entities where ent_name='E-mail Service Call'
to get the ent_oid for E-mail Service call
and
select ent_oid from ifc_entities where ent_name='Person'
to get the ent_oid of Person

I strongly advise you first to import the xml at your test server and even there have a backup.
menes fhtagn
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Hello Michael,

Do not assign points to my previous post (and this one) since unless service desk client is specially run the import through aces will fail. I will do a new post instructing how can this be done at the db level.

menes fhtagn
George M. Meneg
Honored Contributor
Solution

Re: Entity reference from ESC to Person?

Hello Michael,

Here are the detailed instructions. The queries were written for mssql and if you skip the "begin transaction, go" they will also work on oracle.

MAKE SURE THAT YOU HAVE A BACKUP.

First, go to data/custom fields and create a person custom field as following:

Item: E-mail Service Call
Field Key: EmailPerson
Name: E-mail Person
Validation Type: Entity Reference
Entity to: E-mail Source

Once this is created exit the client and empty its cache. Also stop server (if you have more than one instances ALL instances) and clear server cache.

Step #1:
Find the ent_oid of person and e-mail source
To do this execute these queries:

person --> select ent_oid from ifc_entities where ent_name='Person'
e-mail source --> select ent_oid from ifc_entities where ent_name='E-mail Source'

Step #2:
Find the name of the column on the table itsm_esc_cft001 which
holds the newly created attribute. It should be something like A5$_EMAILPERSON_OID

then execute this query to find the col_oid of this column:
select col_oid from ifc_columns where col_name='A5$_EMAILPERSON_OID'

Step #3:
Find the oid of column that holds the per_oid:
select col_oid from ifc_columns where col_name='per_oid'

Find the oid of column that holds the rcd_oid:
select col_oid from ifc_columns where col_name='rcd_oid'

Step #4:
The last thing we want is to set the correct att_uii_oid
that controls how "person" will be represented on the E-mail Service Call form.
execute this query:
select uii_oid from rep_ui_items where uii_name='com.hp.ifc.ui.fld.AppLookupField'


Now we are ready to do the updates:

----
begin transaction
go
update ifc_attributes set atr_entity_to_oid=555155484, --person ent_oid
atr_uii_oid=16 --uii_oit of com.hp.ifc.ui.fld.AppLookupField from rep_ui_items
where atr_entity_to_oid=633318790266960 --e-mail source ent_oid
and atr_oid=281511835143915 --atr_oid of e-mail person

then if all are OK execute commit

----
begin transaction
go
update ifc_columns set rel_col_to=602996741 --col_oid of per_oid
where rel_col_to=281480896315418 --col_oid of rcd_oid
and col_oid=281511835076504 --col oid of A5$_EMAILPERSON_OID

then if all are OK execute commit

When you execute the update queries only one column should be altered. DO NOT commit if more than one columns are altered, instead execute rollback.

If things are OK don't forget to execute 'commit' or the SQL Server will hold lock on the tables and OVSD server won't be able to start.

Good luck!
menes fhtagn
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Just a clarification: The OIDs that I used in the update queries are from my system.

With the exception of the OID of the new attribute (E-mail person) the other OIDs probably would be the same on your system but better be safe than sorry!
menes fhtagn
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Just a side note (do not assign points or assign zero points to this post). With the use of some external libraries this is a 1 minute job and no db altering is necessary. You can create a person field on e-mail like any other custom field.

I fail to see we HP has locked down the ability of creation of ANY entity reference fields to the java client.
menes fhtagn
George M. Meneg
Honored Contributor

Re: Entity reference from ESC to Person?

Hello Again,

I forgot to include the following (in step #1):

Find the atr_oid of the newly created attribute:

select atr_oid from ifc_attributes where atr_name='E-mail Person'

this is 281511835143915 in my system, in your it should be different. You'll need this value on the first update

( the clause "and atr_oid=281511835143915 --atr_oid of e-mail person")
menes fhtagn

Re: Entity reference from ESC to Person?

Wow! You really put a lot of work into this!

*Gulp* :-) Do I really dare to do it?

I must check with our database people first to verify that backup is OK and hear their opinion.

Michael

Re: Entity reference from ESC to Person?

Hi George.

We have decided not to make changes to our database at this point, as it seems a bit risky. I am therefore unable to verify that your trick works. I might test it at a later stage though, and will let you know how it worked out.

Thanks anyway, you are always very helpful.

Michael
//Add this to "OnDomLoad" event