OnAfter or Field map script transform script help needed

[E_19]

Hi All,

Can someone advise on how I should approach the below requirement:

I have load a list of companies into core_company table.

They all have a customer id, debit group id which is often the same but not always

After each load I would like to run an onAfter script to take the debit group id and find the matching name in the core_compnay table

and add this to the reference field Parent.

comp id                                                                                                                                                                                                                                                                                                               Parent field should result in             Debit group id                

100000	xyz company	street 19		London	UK	xyz company		100000
100001	abc Ltd	way 1		Berlin	DE	abc Ltd		100001
100004	time Ltd	lane 2		Zürich	CH	xyz company		100000
100005	now Ltd	road 3		New York	USA	time ltd		100004

Any suggestions greatly appreciated.

Thanks,

Ellie

[nthumma]

So you want help with script ? can you post exact table names and field names.

[E_19]

yes please I have tried various but struggling with this on

Source table: u_imp_kunden

u_nr_

u_debitoren_gruppencode

u_name

Target table: core_company

u_company_id

u_debitor_group

name

parent   (this is is a reference field to itself i.e. core_company)

Either during import the Parent field is populated with the company as per value in u_debitoren_gruppencode

or after the load we could run a script to populated the Parent field with the company name as per value in u_debitoren_gruppencode

whatever is best

Many Thanks,

Ellie

[nthumma]

try this onBefore script.

var gr new GlideRecord('core_company'); 
   gr.addQuery('u_debitor_group',source.u_debitoren_gruppencode);
   gr.query();     // Issue the query to the database to get all records
   while (gr.next()) {     
         // add code here to process the incident record
               traget.parent = gr.sys_id
   }

[venkatiyer1]

Just few changes and additions to above code poposed by @srnewbie

var gr new GlideRecord('core_company');    

  gr.addQuery('u_debitor_group',source.u_debitoren_gruppencode);  

  gr.query();     // Issue the query to the database to get all records  

  while (gr.next()) {        

        // add code here to process the incident record  

              traget.parent = gr.sys_id  

  }

  gr.addQuery('u_debitor_group',source.u_debitoren_gruppencode);  

  The above line would be valid if you are getting the sys id of the group but if it is the group id you might have to modify the u_debitor_group.groupID

  Also, saw a typo in the above code. It is target.parent