Script to check missing records on different table

[mballinger]

Hello,

We have 2 tables: Orders and Order Lines. The data gets populated on both via Transform and they are not linked whatsoever.

I need a script to find all Orders that have no Order Lines

My background script looks like the following:

var arr = [];
var orderGr = new GlideRecord('csm_order');
orderGr.addEncodedQuery('u_delivery_date<javascript:gs.beginningOfOneYearAgo()');
orderGr.query();
while(orderGr.next()){
	var lineGr = new GlideRecord('csm_order_line_item');
	lineGr.addEncodedQuery('order_id=' + orderGr.sys_id);
	lineGr.query();
	while (!lineGr.next()){
		arr.push(orderGr.number.toString());
	}
}

gs.print('Orders with no Lines ' + arr.toString());

Thanks!

[Aman Kumar S · Accepted solution]

Hey,

Replace "while" with "if"

Try below code:

var arr = [];
var orderGr = new GlideRecord('csm_order');
orderGr.addEncodedQuery('u_delivery_date<javascript:gs.beginningOfOneYearAgo()');
orderGr.query();
while(orderGr.next()){
	var lineGr = new GlideRecord('csm_order_line_item');
	lineGr.addEncodedQuery('order_id=' + orderGr.sys_id);
	lineGr.query();
	if(!lineGr.next()){// if instead of while
		arr.push(orderGr.number.toString());
	}
}