Robust Transform - Get and apply SysID of inserted record to other table(s)

Duaine Johnson1 · ‎04-03-2023

I thought this would be simple and it probably is, but I've yet to come up with the answer, so I hope to find someone smarter than me to provide some guidance. 🤔 I've stripped down our actual requirements for simplicity sake.

Table A has the following fields: Company Name (display value, string), Company ID (string)

Table B has the following fields: Company ID (reference to matching Company ID record on Table A), Identifier (string)

Table A coalesce is on Company ID.

Issue: Using a Robust Transform, what is needed is that when a new record is being inserted into Table A, that reference is also established in the appropriate record in Table B.

We've had success in establishing the reference when the Table A record already exists, but not when the record is being inserted. The success came from querying Table A using a script (RTE Entity Operations, staging entity). The 'glide look up' option never seemed to return anything.

The simple path would be to go back to using standard transforms for this data, however we are trying to minimize the database operations as much as possible for performance reasons as the real-world implementation of this will be handling an extremely high volume, over 100 fields with data being applied to about 10 tables, all with varying references to each other.

If someone has been able to establish a reference between tables using a robust transform for inserted records, the community (and I, of course) would appreciate your wisdom! 😁

Duaine Johnson1 · ‎04-04-2023

Got it working!

Solution:

On the transform target field, dot-walk to the field(s) you coalesce on.

Example:

We mapped the following fields to the Stage entity: ID, Company Name, Identity

When mapping Stage to Company (Table A above), it's a straight-forward map. ID > ID, Company name > Company name

When mapping Stage to Ident (Table B above), this is where the dot-walk takes place. Identity > Identity Type, ID > ID.ID (in our example, the dot-walk is simple: u_id.u_id).

Note: the field that Stage > Ident targets is defined on the Ident entity. We named it ID with a path of u_id.u_id

This dot-walk represents the field of the unique (coalesce) in Table A. If there is more than one field being used to coalesce, map each one to ensure your reference is always pointed to the correct record.

Hope this helps save someone from going through the same long, painful process! 🙂

View solution in original post

SRDan · ‎04-25-2023

Hello @Duaine Johnson1 , I am currently in the same boat..

1. Trying to create user record : stage to user

2. Populate the user sys_id in the xyz table : stage to xyz

Unfortunately I could not understand how you have done the dot walk in the field mappings.

Can you elaborate on that.. Appreciate your response.

Thanks,

Sam

Duaine Johnson · ‎05-01-2023

Hey @SRDan, no worries.

Basically what you're trying to do with a Robust Transform is connect the records based on a specific field rather than a sys_id.

Example:

In stage to user, you populate UserID, EMail, First Name, Last Name, etc. on the sys_user table.

In stage to xyz, you populate x, y, z and 'u' as a reference (to sys_user) field.

To populate the reference field, you would map one of the fields from stage to user in the stage > xyz field for 'u', let's say email. So you would have u.email (the dot-walk to the email field on the sys_user table) and you would populate it with the same value as you did with stage to user.

More details:

Incoming data has the following:

UserID: bob.donut
First Name: Bob
Last Name: Donut
Email: bob.donut@example.com
X: XValue
Y: YValue
Z: ZValue

Import Set entity:

ID = bob.donut
FN = Bob
LN = Donut
EM = bob.donut@example.com
X: X
Y: Y
Z: Z

Staging Entity: (Stage value on left, Import Set value from Import Set entity above on right)

ID = ID (bob.donut)
FN = FN (Bob)
LN = LN (Donut)
EM = EM (bob.donut@example.com)
X = X (XValue)
Y = Y (YValue)
Z = Z (ZValue)

Staging to User:

user_name = ID (bob.donut)
FN = FN (Bob)
LN = LN (Donut)
EM = EM (bob.donut@example.com)

Staging to XYZ:

X = X (XValue)
Y = Y (YValue)
Z = Z (ZValue)
U.email = EM (bob.donut@example.com)

The very last item in Staging to XYZ dot-walks to U.email ('u' being the reference field to sys_user and 'email' being the email field on sys_user), this will create the proper reference value for your 'u' field. If you've spent some time in ServiceNow, this is typically the sys_id when you're coding, but not with a Robust Transform.. instead you find what is basically a 'common field' between the two and the system does the rest.

I hope this helps, please let me know if you're still struggling with your scenario. 🙂

Mathias Vandend · ‎05-11-2023

Can someone please provide this with a screenshot to make it more clear. I can't wrap my head around it 🙂

SRDan · ‎05-16-2023

Thanks for the detailed explanation. Firstly, I appreciate that. Now my question is.

In the last item,

Staging to XYZ:

X = X (XValue)
Y = Y (YValue)
Z = Z (ZValue)
U.email = EM (bob.donut@example.com)

How did you set u.email in a reference field?

Let's say, in my PDI I am trying to populate the caller_id field once the user got created. So, The RTE mappings are like,

1. Import to Stage

2. Stage to User

3. Stage to Incident

As you said, I tried to map the user field as user.caller_id and it's throwing me an error. Please find the screenshot.

Also, if you could post similar screenshot where you could be able to map the reference field, It would be helpful.

Thanks,

Sam

Vidya Shree · ‎05-19-2023

Hi,

Please find the screenshot below.

Use dot-walking while creating Entity fields not while Mapping.

Hope this helps

Thanks,

Vidyashree