Record Query Utility
Efficient GlideRecord query patterns with built-in best practices, error handling, and common operations.
#gliderecord #query #database #utilities #crud #pagination #batch-operations
Script Code
JavaScript
1var QueryUtils = Class.create();
2QueryUtils.prototype = {
3
4 /**
5 getById: function(table, sysId) {
6 if (!table || !sysId) {
7 gs.warn('QueryUtils.getById: Missing table or sys_id');
8 return null;
9 }
10
11 var gr = new GlideRecord(table);
12 if (gr.get(sysId)) {
13 return gr;
14 }
15
16 return null;
17 },
18
19 /**
20 getRecords: function(table, query, limit) {
21 var records = [];
22
23 var gr = new GlideRecord(table);
24 if (query) {
25 gr.addEncodedQuery(query);
26 }
27
28 if (limit) {
29 gr.setLimit(limit);
30 }
31
32 gr.query();
33
34 while (gr.next()) {
35 // Push a copy of the record
36 var record = new GlideRecord(table);
37 record.get(gr.sys_id);
38 records.push(record);
39 }
40
41 return records;
42 },
43
44 /**
45 exists: function(table, field, value) {
46 var gr = new GlideRecord(table);
47 gr.addQuery(field, value);
48 gr.query();
49
50 return gr.hasNext();
51 },
52
53 /**
54 count: function(table, query) {
55 var ga = new GlideAggregate(table);
56
57 if (query) {
58 ga.addEncodedQuery(query);
59 }
60
61 ga.addAggregate('COUNT');
62 ga.query();
63
64 if (ga.next()) {
65 return parseInt(ga.getAggregate('COUNT'));
66 }
67
68 return 0;
69 },
70
71 /**
72 getDistinctValues: function(table, field, query) {
73 var values = [];
74
75 var ga = new GlideAggregate(table);
76
77 if (query) {
78 ga.addEncodedQuery(query);
79 }
80
81 ga.groupBy(field);
82 ga.query();
83
84 while (ga.next()) {
85 var value = ga.getValue(field);
86 if (value) {
87 values.push(value);
88 }
89 }
90
91 return values;
92 },
93
94 /**
95 batchUpdate: function(table, query, updates, skipWorkflow) {
96 var count = 0;
97
98 var gr = new GlideRecord(table);
99 if (query) {
100 gr.addEncodedQuery(query);
101 }
102
103 gr.query();
104
105 while (gr.next()) {
106 // Apply updates
107 for (var field in updates) {
108 if (updates.hasOwnProperty(field)) {
109 gr.setValue(field, updates[field]);
110 }
111 }
112
113 // Set workflow flag if requested
114 if (skipWorkflow) {
115 gr.setWorkflow(false);
116 }
117
118 gr.update();
119 count++;
120 }
121
122 gs.info('QueryUtils.batchUpdate: Updated ' + count + ' records in ' + table);
123 return count;
124 },
125
126 /**
127 batchDelete: function(table, query, skipWorkflow) {
128 var count = 0;
129
130 var gr = new GlideRecord(table);
131 if (query) {
132 gr.addEncodedQuery(query);
133 }
134
135 gr.query();
136
137 while (gr.next()) {
138 if (skipWorkflow) {
139 gr.setWorkflow(false);
140 }
141
142 gr.deleteRecord();
143 count++;
144 }
145
146 gs.info('QueryUtils.batchDelete: Deleted ' + count + ' records from ' + table);
147 return count;
148 },
149
150 /**
151 copyRecord: function(sourceTable, sourceSysId, targetTable, excludeFields) {
152 excludeFields = excludeFields || ['sys_id', 'sys_created_on', 'sys_created_by',
153 'sys_updated_on', 'sys_updated_by'];
154
155 var source = new GlideRecord(sourceTable);
156 if (!source.get(sourceSysId)) {
157 gs.warn('QueryUtils.copyRecord: Source record not found');
158 return null;
159 }
160
161 var target = new GlideRecord(targetTable);
162 target.initialize();
163
164 // Copy all fields except excluded ones
165 var fieldNames = source.getFields();
166 for (var i = 0; i < fieldNames.size(); i++) {
167 var fieldName = fieldNames.get(i).getName();
168
169 if (excludeFields.indexOf(fieldName) === -1) {
170 target.setValue(fieldName, source.getValue(fieldName));
171 }
172 }
173
174 var newSysId = target.insert();
175
176 if (newSysId) {
177 gs.info('QueryUtils.copyRecord: Created new record ' + newSysId);
178 return newSysId;
179 }
180
181 return null;
182 },
183
184 /**
185 getPaginated: function(table, query, page, pageSize) {
186 page = page || 1;
187 pageSize = pageSize || 10;
188
189 // Get total count
190 var total = this.count(table, query);
191 var totalPages = Math.ceil(total / pageSize);
192
193 // Get paginated results
194 var gr = new GlideRecord(table);
195 if (query) {
196 gr.addEncodedQuery(query);
197 }
198
199 // Calculate offset
200 var offset = (page - 1) * pageSize;
201 gr.chooseWindow(offset, offset + pageSize);
202 gr.query();
203
204 var records = [];
205 while (gr.next()) {
206 var record = new GlideRecord(table);
207 record.get(gr.sys_id);
208 records.push(record);
209 }
210
211 return {
212 records: records,
213 total: total,
214 page: page,
215 pageSize: pageSize,
216 totalPages: totalPages
217 };
218 },
219
220 type: 'QueryUtils'
221};
222 var newSysId = target.insert();
223
224 if (newSysId) {
225 gs.info('QueryUtils.copyRecord: Created new record ' + newSysId);
226 return newSysId;
227 }
228
229 return null;
230 },
231
232 /**
233 * Get records with pagination
234 * @param {string} table - Table name
235 * @param {string} query - Encoded query string
236 * @param {number} page - Page number (1-indexed)
237 * @param {number} pageSize - Records per page
238 * @returns {object} {records: array, total: number, page: number, totalPages: number}
239 */
240 getPaginated: function(table, query, page, pageSize) {
241 page = page || 1;
242 pageSize = pageSize || 10;
243
244 // Get total count
245 var total = this.count(table, query);
246 var totalPages = Math.ceil(total / pageSize);
247
248 // Get paginated results
249 var gr = new GlideRecord(table);
250 if (query) {
251 gr.addEncodedQuery(query);
252 }
253
254 // Calculate offset
255 var offset = (page - 1) * pageSize;
256 gr.chooseWindow(offset, offset + pageSize);
257 gr.query();
258
259 var records = [];
260 while (gr.next()) {
261 var record = new GlideRecord(table);
262 record.get(gr.sys_id);
263 records.push(record);
264 }
265
266 return {
267 records: records,
268 total: total,
269 page: page,
270 pageSize: pageSize,
271 totalPages: totalPages
272 };
273 },
274
275 type: 'QueryUtils'
276};
How to Use
1. Create a new Script Include named 'QueryUtils'
2. Leave 'Client callable' unchecked
3. Copy the code above
4. Use in Business Rules or Background Scripts:
var query = new QueryUtils();
var incident = query.getById('incident', incidentId);
var count = query.count('incident', 'active=true^priority=1');
query.batchUpdate('task', 'active=false', {state: 'closed'});