How do I iterate through a loop and also query the same table

[gnewuser]

Hi, I have a table  A and I query all the reocrds and I have 2 fields in the table that I need to get for example "numberofdays1" and "numberofdays2".

Once I get that I add the "numberofdays1" and "numberofdays2". But once I get this "totalnumberofdays", I need to query the same table A and find a field called  "Enddate"(which is a date field) and add the following condition:

get all records where the "Enddate" is within the  "totalnumberofdays" .

Now I am confused as to how do I code this.

var grA = new GlideRecord("A");
grA.Query(); 

var totalnumberofdays;

while (grA .next()){
 
totalnumberofdays= grA .numberofdays1+ grA .numberofdays1;
}

Now inside this loop, how do I query the same table "A", and find all the records where the "Enddate" is within a "totalnumberfdays"

[Brad Bowman]

First, if you are wanting totalnumberofdays to equal the numberofdays1 field + the numberofdays2 field as a running total for EVERY record in table A / returned by the query, it would have to look more like this:

var grA = new GlideRecord("A");
grA.query(); 
var totalnumberofdays = 0;
while (grA .next()) {
    totalnumberofdays += parseInt(grA.numberofdays1) + parseInt(grA.numberofdays2);
}

then the second GR would follow, outside of the grA while loop.  If you want to get all of the records where the Enddate is less than or equal to the totalnumberofdays from today for EACH record returned by the grA query, it would look more like this:

var grA = new GlideRecord("A");
grA.query(); 
var totalnumberofdays = 0;
while (grA .next()) {
    totalnumberofdays = parseInt(grA.numberofdays1) + parseInt(grA.numberofdays2);
    var gr2 = new GlideRecord("A");
    gr2.addEncodedQuery('enddateRELATIVELT@dayofweek@ahead@' + totalnumberofdays);
    gr2.query();
    while (gr2.next()) {
        //do something
    }
}

where 'enddate' is the name of your field.

[gnewuser]

Thanks Brad for your response. I think this is where I am concerned to have one  while within another while and also querying. I will try your suggestion and update you on the outcome.

Thanks again

[Gaurav Shirsat · Accepted solution]

@gnewuser 

from your question I did not get how you are doing this I am not sure, if you are doing this using Business Rule, I will request you to create 2 separate functions in Script Include and call one by one here. also you can provide input query parameter to script include too.

Call the First Function which will give you the counting of your total number of days. same thing doing in business rule is also Ok. just pass this result as input query to script include.

var grQuery = new GlideRecord("A");
grQuery .query();
var days = 0;
while (grQuery .next()) {{

days += parseInt(grA.numberofdays1) + parseInt(grA.numberofdays2);
}

break;

}

Now you just have to pass this result to your script include, in which you can write below code

reassignWork: function(myDay) {
var aTable = new GlideRecord('atable');
atable.EncodedQuery("");
aTable.query();
if (aTable.next()) {
//your work

return variable_name

}
},

refer below screenshots and links how you can call script include to Business Rule.

https://www.basicoservicenowlearning.in/2019/12/script-include-servicenow.html#:~:text=To%20use%20sc... 

https://www.servicenow.com/community/csm-forum/how-to-call-script-include-from-business-rule/m-p/398... 

Mark my Response as Correct or Helpful, if you find it Appropriate.
Gaurav Shirsat : ServiceNow Community MVP 2022
https://www.linkedin.com/in/gauravshirsat/

[gnewuser]

Thanks Gaurav. The whole thing is going to be in a scheduled job. The requirement is to run the  job every night to get all the records and calculate the total number of days from table A . Now run through table A again and find all the records which are total number of days within end date. Not sure how would I incorporate business rules in that. Your inputs are appreciated.

Thanks!