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

[Duaine Johnson1]

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! 😁

[Punit S]

Very good and interesting question, happy to help  ! 

To establish a reference between tables using a Robust Transform for inserted records, you can use a combination of two transform maps and a script include.

1. Create a transform map for Table A to map the Company Name and Company ID fields to their respective fields in the target table.
2. Create a second transform map for Table B to map the Identifier field and a new reference field to Table A to their respective fields in the target table.
3. In the second transform map, create a script include to query Table A for the Company ID using the Company Name from the source record. You can use GlideRecord queries to do this.
4. In the script include, if the query finds a matching record, set the value of the reference field to the matching record's sys_id.
5. In the second transform map, set the transform script to call the script include and pass in the Company Name value from the source record.

When a new record is inserted into Table A, the first transform map will map the Company Name and Company ID fields to their respective fields in the target table. Then, when the second transform map is applied, the script include will query Table A for the matching record using the Company Name value from the source record, and set the value of the reference field in Table B to the matching record's sys_id.

This approach should allow you to establish the reference between the tables for inserted records using a Robust Transform.

Please mark my answer correct/helpful in case it adds value and moves you a step closer to your desired ServiceNow solution goal. 

Thanks,
Punit

[Duaine Johnson1]

Hey Punit, appreciate the quick response. 

Unless I'm missing something, which is entirely possible, I don't see where I can use a Script Include to do the GlideQuery you suggested.  Are you possibly referencing the 'standard' transform maps which are created and run individually?  

To get to where I am now, I've created four ETL Entities - importSet, stage, company (mapped from Table A) and ident (mapped from Table B).  All fields from the importSet, company and ident tables were generated automatically via the Generate Fields related link.  The 'stage' Entity consists of company_name, id and a field dedicated to hosting the company sys_id.

Transform 1: importSet > Stage.   This allows for various transforms to take place, I'm particularly interested in populating the company sys_id, of course.

Transform 2: Stage > Company:  Inserts/Updates the Name and ID with no issues.

Transform 3: Stage > Ident: Inserts/Updates with no issues. (for testing purposes, I have mapped the ID to both the ID field and an "Identity" field, this allows me to confirm that everything loads and coalesces correctly.  The ID field, being a reference to Company, is always blank.

If, in the Stage entity, I create an RTE Entity Operation and select 'Script' from the interceptor, I'm able to use the Source ID mapped from the importSet > Stage transform to do a GlideQuery onthe Company table, get the sys_id, and apply it to a Target ID called company.sys_id.   This works perfectly - but only if the Company (Table A) record already exists prior to the transform being run.  Any records being inserted results in the ID field on the Ident table (Table B) being blank.

I've experimented with multiple combinations, including changing the order of the RTE Entity Mappings.  The end result is always that the Stage > Company and Stage > Ident transforms are completed at the same time.  All of the data transformations take place at the importSet > Stage step and all that remains from there is to move the data from Stage to Company and/or Ident.

For anyone reading in on this, I've also discovered some 'rules' that restrict complexity that may save some time.  Might not be the most ideal place for this info, but I didn't see it anywhere in the docs, so at least it's somewhere: 

• An entity can only be used as a target once.  You cannot create two RTE Entity Mappings that target the same entity.
• A target table can only be targeted once.  You cannot create two ETL Entities with the same target table.  As soon as I tried this, I got the following error: java.lang.RuntimeException: No entity mappings with valid table and field names are defined
• An onAfter script can be used, but it doesn't appear to allow the manipulation of the target object.  I tried assigning the value of the ident.company.sys_id to company.sys_id, but the target object remained unchanged.

Everything we are trying to do works except for being able to reference Table A (Company) from Table B (Identity) for new records.  This is big for us as we will be doing our initial load of data (record counts into the 7-figures) and ongoing data updates (6-figures/month).  Minimizing database transactions is quite critical for this particular build. 

Hope I've not overwhelmed with too much information!

Happy to discuss additional ideas, theories, or the above suggestion further! 👍

[Duaine Johnson1 · Accepted solution]

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! 🙂 

[Aruna Nadagoun1]

@Duaine Johnson1  I tired the same way but it isn't working for me .