Importing data into reference field, which in referenced table, is also a reference field

[josh_the_tech]

Hi,

I'm creating an app which has 2 tables. One table "table 1" references the User table and has a choice field with the year. "Table 2" has a field that references "table 1". This is done so the related lists show in the way I want them to in a workspace.

I've imported the records into "table 1" containing the users and the year.

I'm trying to import the data into "table 2" but the field in this table that references "table 1" isn't populating.

I'll try and put it into context.

 

This is what I've configured on the transform map

I hope this makes sense and any help would be much appreciated. I've tried dot walking in the Referenced value field name but that doesn't work either.

Thanks,

Josh

[Sai Kumar B]

Try the below script in onBefore transform script, If no luck try the same script in Field map record "source script" and revert back the choice action to "create"

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

var getNominee = new GlideRecord('table_1'); //Glide to the table that stores Nominee details
getNominee.addQuery('nominee_field_name.name', source.u_full_name_of_nominee); //Query using Dot-walking on field.name with source full nominee name value
getNominee.query();
if(getNominee.next()){

target.nominee = getNominee.getValue('sys_id'); //Set nominee record sys_id to target table nominee field

})(source, map, log, target);

[chsumit]

Hi,

If you are referring the "Full name of nominee" field from 1st table so my understanding is it will take record from that table only. Please update the choice action to ignore and add the same value in transform map which available in 1st table.

Thanks,

Sumit 

[-O- · Accepted solution]

If I understand correctly, what you want is to get the "Table 1" reference of "Table 2" based on the "User" in "Table 1". I believe that way you do it, it would only work if you had the sys_id of the user, but you would need to Specify the "Table 1" reference in target and the "User" field as "Reference value field name". However, this would only work if User was a sys_id and not the name - as far as I know.

What you need is a Field map script, where you would look up the right record in Table 1, based on user - of course and would return that record's display name. Or sys_id, but than you should specify that in the "Reference value field name".