Find the target record using reference table and update the record using transform maps

[Tapish Sharma1]

Hi All, 

I am importing company data in customer_accounts table. The excel does not contain Account name or other unique identifier directly from accounts table. There is a role_code that we have for each account but that is stored in separate table and has account reference. The excel sheet with account details like addrs and stuff has role_code. So I need to find the account using role_code and then update that account details like address and stuff. How can I achieve this using transform map ? 

[Sonu Parab]

Hi @Tapish Sharma1 Have a look at this post. Transform Map Script changing email address to user name 

You can take help of script mentioned in the above article .

Thank you

[Tapish Sharma1]

Shall I create an OnBefore Script for this ? because I dont want to create a record , i want to find the record and update that 

[Harshal Aditya · Accepted solution]

Hi Tapish,

If I have understood you requirement right then you would need to put coalesce on sys_id and use source script.

Below is the rough example of source script

var sourceCode = "";

    var getCode = new GlideRecord("your_custom_table");

    getCode.addQuery("your_custom_field",source.role_code);

    sourceCode = getCode.sys_id;

    var getAccount = new GlideRecord("customer_accounts");

    getAccount.addQuery("your_reference_field",sourceCode);

    getAccount.query();

    if(getAccount.next()){

        answer=getAccount.sys_id; 

    }

    else{

        answer= GlideGuid.generate(null); 

    }

Note - You would need to adjust the script as per your requirement.

If my answer has helped with your question, please mark it as helpful and give it a thumbs up!

Regards,
Harshal