How to query field to currency that shows 0 amount? It seems normal query not working.

[Jmea]

How to query field to currency that shows 0 amount? It seems normal query not working.

I have 605 total or recods. 506 records containts u_total is not zero. So I'm expecting to have 99 records that contract 0 u_total value. Here's my script,  I want to query all records that has 0 value to u_total field but always showing 0 result. Pls help, thank you.

Script showing 99 records that u_total field is zero (not working)

var gr=new GlideRecord('custom_table');

gr.addQuery('u_total','=','0');

gr.query();

gs.info(gr.getRowCount());

Output: 0

Script showing 506 records that u_total field is not zero (works fine)

var gr=new GlideRecord('custom_table');

gr.addQuery('u_total','!=','0');

gr.query();

gs.info(gr.getRowCount());

Output: 506

[Anurag Tripathi]

Hi,

Try this in background script

var pricefilter = global.getCurrencyFilter('sc_cat_item','price', 'USD;0'); //append the currency type
gs.print(pricefilter);
var gr = new GlideRecord('<table name>')
gr.addEncodedQuery('price>pricefilter');
gr.query();
if(gr.next()){
//do whatever
}

-Anurag

[Jmea]

this one also not working 😞 

it shows 0 result

[Vamsi Sreenivas]

Hi @Jmea , try the below script.

var gr = new GlideRecord('YOUR_TABLE_NAME');
gr.addEncodedQuery("list_price>javascript:global.getCurrencyFilter('sc_cat_item','list_price', ';0')"); // replace sc_cat_item with your table name and list_price with your currency field name
gr.query();
if(gr.next()){
gs.info(gr.getRowCount());
}

Mark my answer as HELPFUL / CORRECT if this help resolve your issue.

Regards,

Vamsi S

[Mahendra RC]

Hello Jmea,

Could you please check with below background script:

(function () {
	var pricefilter = "price=javascript:global.getCurrencyFilter('custom_table','u_total', 'USD;0')";
	gs.print(pricefilter);
	var gr = new GlideRecord("custom_table")
	gr.addEncodedQuery(pricefilter);
	gr.query();
	if(gr.next()){
		//do whatever
	}
})();

Please mark my respsone as helpful/correct, if it answer your question.

Thanks