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

josh_the_tech · ‎02-24-2022

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

-O- · ‎02-25-2022

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".

View solution in original post

Sai Kumar B · ‎02-24-2022

@josh_the_tech

Is the reference column(Full name of Nominee) value is sys_id in your data sheet?

josh_the_tech · ‎02-25-2022

Hi @Sai Kumar,

Thanks for replying. It's not no, It's the name of the nominee as I don't have the sys_id. I mean I can get them and do that with an XLOOKUP in Excel, but I wanted to avoid that if I could for future years, as I may not be doing the imports in future years and may be a person in the Business.

Thanks,

Josh

Sai Kumar B · ‎02-25-2022

@josh_the_tech

Try the below

1.) In your field mail choose choice action as "Ignore"

2.) Write onbefore() transform script with the below code

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

target.give_nominee_field_name= source.u_full_name_of_nominee;

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

josh_the_tech · ‎02-25-2022

Hi @Sai Kumar,

Just tried it and unfortunately it still shows (Empty) in the field.