Populate related list with records from reference field

[nabilo_]

Hi,

I need some help in a complex requirement:

1. I have a Table called PO

2. I have another Table called POL

This first table PO is the master one, and the second table POL is a related table with a field referencing to the PO parent table.

This means that you can have one record in PO with multiple POLs. and I do have a Related List displaying all POLs for every PO record.

Now the Complexity is the fact that I need to add a new Reference Field to the PO table, "called PM" that is a reference to the ProductModule "sc_cat_item" table.

When a User selects one PM from the Product Modules "sc_cat_item", I need to take all what this PM Includes in it's related list "Includes" and put these records in the POL related list of my PO.

My Incomplete Script:  

Condition: PM field Changes.

function onBefore(current, previous) {
     //This function will be automatically called when this rule is processed.
     var gr = new GlideRecord("proc_po_item");   
gr.addQuery("purchase_order",current);   
gr.query();
while(gr.next()){
gr.purchase_order = current.sys_id;
gr.requested_for = current.requested_by;
gr.part_number = ...;
}   
}

I know the script is incomplete, and might even be wrong, but I need to know how to:

1. Create records in the POL related List of my PO table.

2. Fill the fields of this POL record with data from the PM record

3. Loop till I have all the PM related list "Includes" added as POLs.

Best regards,

Nabil O.

[Pradeep Sharma · Accepted solution]

Hi Nabil,

Make sure you query the reference table related list and loop through the records and get the object. Once done query the related table(which is the related list for parent table) and create the records with its object.

Also please make sure that column names are correct when you construct an query.

I hope this helps

[nabilo_]

Pradeep You are by far the most Active, Knowledgeable, and Talented professional on the SeviceNow Communities, your help was of great value to this complex requirement, and I am happy to say that the solution is almost complete with your help.

My Pre Final Code:

function onBefore(current, previous) {//PM: Product Module

   var PM = new GlideRecord("sc_cat_item_children");//m2m Table

   PM.addQuery("parent", current.u_product_model);//Assigning the current.u_product_model as the Parent for the m2m Table 

   PM.query();

   while (PM.next()){// for every Child

   var pol = new GlideRecord("proc_po_item");//Create a POL "PO Line Item"



   //------------------Starting the Initialization and creation of POL----------------------------------

   pol.initialize();





   pol.purchase_order = current.sys_id; // Set the POL PO to the Current/Parent PO

   pol.requested_for = current.requested_by;// Set other fields from the Current/Parent PO





   var child = PM.getDisplayValue('child');//Get the Child from the m2m Table

   pol.short_description = child.short_description;





   var gr = new GlideRecord('cmdb_hardware_product_model');

   gr.addQuery('name', child); //*******************************This is The Issue************************************

   gr.query();

   if(gr.next()){

   pol.cost = child.price;

   pol.u_mrc_nrc = "NRC";



   pol.model = gr.getValue('sys_id');



   pol.short_description = gr.name;

   pol.u_product_id = gr.model_number;

   pol.part_number = gr.u_part_number;

   pol.ordered_quantity = gr.u_quantity;

   }

   pol.insert();

   }

}

The Last Issue is "gr.addQuery('name', child);" where some components have the same name, I tried to change this to work with sys_id, but i was not successful. Please let me know if you have an Idea.

Best regards,

Nabil O.

[Pradeep Sharma]

Hi Nabil,

Thanks a lot for your kind words

You have do a small modification in this case. Here you go.

var child = PM.getDisplayValue('child'); should be var child = PM.child; //will give you the sys_id

Please let me know if you have any questions.

[saritha9]

Try the wiki link for GlideRecord Insert/Updates:

GlideRecord - ServiceNow Wiki

[nagapramodkanch]

See if this helps.

» How to create and manage many-to-many relationship tables in ServiceNow