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

[E_19]

Hi srnewbie,

Good point u_debitor_group is integer I updated the code and added toString() u_debitoren_gruppencode is string

I reloaded but no difference   results are always like:

Can I just add that for each record that is being updated or inserted I want the value that is in u_debitor_group   to be looked up and the I guess sys_id passed to Parent field and often its actually the same as the record currently processed but some 400 companies have different parent.

I am thinking that I need to do my first add query against company_id, source.company_id as below   and then I need for the record being processed to take the u_debitor_group value and look that up in u_company_id and return the sys_id so I can parse the name into the Parent field.

var gr = new GlideRecord('core_company');

gr.addQuery('u_company_id',source.u_nr_.toString());

gr.addQuery('u_debitor_group');

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

  while (gr.next()) {        

        // add code here to process the incident record  

              target.parent = gr.sys_id;  

  }

How do I construct the query on u_debitor_group baring in mind some 400 from 3000 records are different,

Many Thanks,

Ellie

[venkatiyer1]

Hi Ellie,

Parent is a reference record. What is u_debitor_group? Is this a reference to group (sys_user_group) or is it just an integer. From what i have read it seems like you want to create a child in the company table itself. If that is the case, the below code should work

var gr = new GlideRecord('core_company');

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

gr.query();

while (gr.next()) {      

        // add code here to process the incident record

              target.parent = gr.sys_id;

}

[E_19]

Hi Venkat,

u_debitor_group is an integer field in the core_company table.

The parent field is a reference field in the core_company table to core_company

The requirement is: to load the companies into core_company table. Many companies have a parent company but for most companies themselves are the parent company.

I have tried the code sample and below is the result Parent field is undefined.

So when the data is loaded I need to take the value in u_debitor_group and look for a match in u_company_id (both fields are in core_company table) and if and if a match is found I need to put the company name into the parent field.

I have tried the below but its also results in undefined.

var gr = new GlideRecord('core_company');

gr.addQuery('u_company_id', 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  

              target.parent = gr.ys_id;  

  }

Many Thanks,

Ellie

[nthumma]

can you use below code and see what its logging at line 2 ?

var gr = new GlideRecord('core_company');

gs.log('Source '+source.u_debitoren_gruppencode)

gr.addQuery('u_company_id', 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

              target.parent = gr.ys_id;

  }

[E_19]

Hi srnewbie,

Here is an extract of what is logging:

Source 104366

Source 103368

Source 102437

That looks right to me. So what is missing to populate parent field with company as per code above?

Many Thanks,

Ellie