How do you extract the userID from this kind of field content? urn:sys:user:name@email.gov.uk

[Berto2002]

I am trying to look-up attributes of a User Profile in reference to the 'person' who made an Activity update on a Request.

So I wanted to do a table JOIN between the h_actor of h_buz_activities and the h_userid of h_sys_user_profiles but the former has "urn:sys:user:" prefix.

What do I do to strip that out in my table join statement in the reports please?

[NeilWJ]

Hi Berto,

See attached image. Using table join tab set the join criteria between the two tables as custom and then user  REPLACE(h_buz_activities.h_actor,"urn:sys:user:","")

Cheers

[Berto2002]

@NeilWJ thank you! However looks like a made the wrong call on the which table to join! I need to link to h_sys_accounts instead BUT the information you gave is very useful next time I need to do that. Thank you.

[Steve Giller]

Based on:

On 2/24/2023 at 2:56 PM, Berto2002 said:

I am trying to look-up attributes of a User Profile

the table you require is h_sys_user_profiles

If you need attributes of a User Account it's h_sys_accounts

[Berto2002]

In another example, table "h_sys_tasks" has field "h_obj_ref_urn" which needs "urn:sys:entity:com.hornbill.servicemanager:Requests:" stripped-out to reveal a value that can be matched with field "h_pk_reference" on table "h_itsm_requests" (RequestID) but when I use an analogous custom criteria to the above, I get no results: REPLACE(h_sys_tasks.h_task_id,"urn:sys:entity:com.hornbill.servicemanager:Requests:","")

Can this approach be used where there are other tables on the report? I wonder if this is not working because the custom criteria does not state which table to make the link to?

I am attempting to use it get the TaskID of the current Task/Activity that is open linked to that Request in a report which already lists:

1. Request info (like Summary)
2. Activities Info (like the text of the last update)
3. Account Info (like Job Title of the person who made the last update)

[Steve Giller]

You're never going to get any results.
You're joining h_sys_tasks to itself, and matching a Request URN to a Task ID

I'm assuming that you're needing a Request ID which would be in h_itsm_requests

[Berto2002]

Of course. I had the criteria against the wrong table. And what is more, the expression was wrong. This works though, thank youl

REPLACE(h_sys_tasks.h_obj_ref_urn,"urn:sys:entity:com.hornbill.servicemanager:Requests:","")

[Berto2002]

@Steve Giller sorry would you mind redacting the name in the subject of this post, please?

[Steve Giller]

@Berto2002 Done (I hadn't spotted that as I rarely read the actual titles!)