Why Database View working weirdly?

[Ankita Kolhe]

Hi Community,

I created one database view & everything was working fine. The report was showing around 3k+ records. However, suddenly something got broken & report is now showing only 3 records.

I'm assuming it's because of the background script I ran because after that only this issue occurs. But I'm concerned as the script was not updating any records so not sure about the exact issue.

Please find the script below:-

I highlighted the query which I think caused the issue (gr2.addQuery('em_u_location='+uniqueValues[i]+'^em_membership_status_em='+choiceValue);). It might be the reason that instead of addEncodedQuery , I used addQuery())

var records=[];

var memStatusArray=[];

var uniqueValues = [];

var final1=[];

    var ga = new GlideAggregate('regional_membership_short_version');

    ga.groupBy('em_u_location');

    ga.query();

    while (ga.next()) {

        // check if the row is for a unique value 

        if(ga.getDisplayValue('em_u_location')){

            uniqueValues.push(ga.getValue('em_u_location')); // add the value to the array

        }

    }

gs.info(uniqueValues);

for(var i=0; i<uniqueValues.length;i++){

var gr = new GlideRecord('regional_membership_short_version');

gr.addEncodedQuery('em_entity_status=Current^em_not_part_of_kpmg_network=false^em_membership_status_emINmember - full scope,member - limited scope,member - non-standard,member - regional,sublicensee - practising,sublicensee - practising (multiple sublicensors),sublicensee - non-practising,sublicensee - non-practising (multiple sublicensors),sublicensee - non-standard,sublicensee - regional,foreign branch^em_u_location='+uniqueValues[i]);

gr.query();

gs.info('row count: '+gr.getRowCount());

while(gr.next()){

gs.info('location: '+gr.em_u_location.getDisplayValue());

memStatusArray.push(gr.getDisplayValue('em_membership_status_em'));

}

var memStatus=memStatusArray.toString();

var cd=new GlideAggregate('choice_definitions');

cd.addQuery('field_name=Membership Status^choice_valueIN'+memStatus);

cd.addAggregate('MIN', 'order');

cd.setGroup(false);

cd.query();

gs.info('cd: '+cd.getRowCount());

while(cd.next()){

//var test=cd.getValue('choice_value');

    gs.info('Most recent Incident Created Date = ' + cd.getAggregate('MIN', 'order'));

var cd2=new GlideRecord('x_kpm79_kpmgi_iogc_kpmgi_iogc_choice_definitions');

cd2.addQuery('order',cd.getAggregate('MIN', 'order'));

cd2.query();

if(cd2.next()){

 var cd3=new GlideRecord('choice_definitions');

cd3.addQuery('order',cd.getAggregate('MIN', 'order'));

cd3.addQuery('field_name','Membership Status');

cd3.query();

if(cd3.next()){

gs.info(cd3.getValue('choice_value'));

var value=cd3.getValue('choice_value');

var choiceValue=value.toLowerCase();

gs.info('choiceValue: '+choiceValue);

var gr2 = new GlideRecord('regional_membership_short_version');  //database view name

gr2.addQuery('em_u_location='+uniqueValues[i]+'^em_membership_status_em='+choiceValue);  //sublicensee - practising

//gr2.addQuery('em_membership_status_em',choiceValue);

gr2.query();

gs.info('final: '+gr2.getRowCount());

if(gr2.next()){

gs.info(gr2.em_er_number);

final1.push(gr2.em_sys_id);

}

}

Thanks in advance!

}

}

}