{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":617,"path":"api-reference/scripts/c_UsingGlideRecordToQueryTables.md","title":"Querying tables in script","bundle":null,"doc_type":null,"release":"australia","body":"\n# Querying tables in script\n\nUsing methods in the GlideRecord API, you can return all records from a table, return records from a table that satisfy specific conditions, or return records that include a string from a single table or from multiple tables in a text index group.\n\nQuery tables using the GlideRecord API. For API reference, see [GlideRecord - Scoped](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideRecordScopedAPI.md).\n\n## Return all records from a table\n\nTo query a table, first create a GlideRecord object. To create a GlideRecord, create the following in script:\n\n```\nvar target = new GlideRecord('incident');\n```\n\nCreating a GlideRecord creates a `target` variable which is a GlideRecord object for the incident table.\n\nTo process all records from the incident table, add the following script:\n\n```\ntarget.query(); // Issue the query to the database to get all records \nwhile (target.next()) { \n // add code here to process the incident record \n}\n```\n\nThis script issues the query\\(\\) to the database. Each call to next\\(\\) would load the next record for processing.\n\n## Return records from a table that satisfy query conditions\n\nMost of the time, you want to retrieve a specific record or a specific set of records, and you have some criteria \\(query conditions\\) that define the records you want to obtain. For example, say that you want to obtain all the incident records that have a priority value of 1. Here is the code that would accomplish that.\n\n```\nvar target = new GlideRecord('incident'); \ntarget.addQuery('priority',1);\ntarget.query(); // Issue the query to the database to get relevant records \nwhile (target.next()) { \n // add code here to process the incident record \n}\n```\n\nNotice that the example script includes `target.addQuery('priority', 1);`. This line indicates that you only want the records where the **priority** field is equal to 1. In general, most queries that you want to perform are equality queries; queries where you want to find records with a field equal to a value. For this reason, you do not need to provide an equality operator. However, lets say you wanted to find all incidents where the **priority** field is greater than 1. In this case, you would provide the operator that you want to apply to the query.\n\n```\nvar target = new GlideRecord('incident') ; \ntarget.addQuery('priority','>',1);\ntarget.query(); // Issue the query to the database to get relevant records \nwhile (target.next()) { \n // add code here to process the incident record \n}\n```\n\n## Return records from a table that include a string\n\nUse the `'123TEXTQUERY321'` reserved name to search for string matches in all fields on a table. For example, this script returns records from the Incident table with field values that include the 'email' string.\n\n```\nvar now_GR = new GlideRecord('incident');\ngr.addQuery('123TEXTQUERY321', 'email');\ngr.query();\n```\n\n`'123TEXTQUERY321'` is a reserved option for the **name** parameter in the `addQuery()` method. You can use this option in an encoded query string. For example, instead of `gr.addQuery('123TEXTQUERY321', 'email');`, you can use `gr.addEncodedQuery('123TEXTQUERY321=email')`.\n\nString search is case-insensitive. The system returns the same results whether you search for `email`, `Email`, or `EMAIL`.\n\n**Note:** Before you can query using a string search, you must configure text indexing \\(and optionally search attributes\\) for the table you want to search. For more information, see [Configure a single table for indexing and searching](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/configure-single-table-for-indexing.md).\n\n## Return records from multiple tables in a text index group that include a string\n\nUse the `'123TEXTINDEXGROUP321'` reserved name to search for a string in a table from a text index group. This option returns results with relevancy scores computed using the text index group's settings.\n\n**Note:** You can only query one table in the text index group at a time. Use this option when you want to issue multiple single-table queries and merge their results, ordering them by relevancy score. The advantage of this option is that search result relevancy scores for the individual table queries are normalized consistently for all tables in the text index group.\n\nFor example, this script returns records from the Knowledge table that include the keyword 'email', with relevancy scores computed for the 'portal' index group.\n\n```\nvar now_GR = new GlideRecord('kb_knowledge');\ngr.addQuery('123TEXTQUERY321', 'email');\ngr.addQuery('123TEXTINDEXGROUP321', 'portal');\ngr.query();\n```\n\nYou can create a similar query for each additional table in the 'portal' index group that you want to search, and then merge the individual queries' results, displaying the results with the highest relevancy scores first. Because all of these search queries use the same text index group search settings, the relevancy scores for their results are all normalized consistently. If you searched the same set of tables without using the `gr.addQuery('123TEXTINDEXGROUP321', 'portal')` method, the individual queries' relevancy scores would be normalized differently and would not be a useful basis for sorting the merged result set.\n\n`'123TEXTINDEXGROUP321'` is a reserved option for the **name** parameter in the `addQuery()` method. You can use this option in an encoded query string. For example, instead of `gr.addQuery('123TEXTINDEXGROUP321', 'portal');`, you can use `gr.addEncodedQuery('123TEXTINDEXGROUP321=portal')`.\n\nMulti-table string search is case-insensitive. The system returns the same results whether you search for `email`, `Email`, or `EMAIL`.\n\n**Note:** Before you can query tables in an index group, you must configure text indexing and search attributes for those tables and include them in the index group. For more information, see [Configure multiple tables for indexing and searching](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/index-multiple-tables.md). All tables in the index group must use the V4 indexing format.\n\n**Parent Topic:**[Server-side scripting](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/scripts/c_ServerScripting.md)\n\n## Available JavaScript operators\n\nDescribes the operators that can be used within an addQuery\\(\\) request.\n\n
\n\nField\n\n\n\nDefinition\n\n\n\naddQuery\n\n
\n\n=\n\n\n\nField must be equal to value supplied.\n\n\n\naddQuery\\('priority', '=', 1\\);\n\n
\n\n>\n\n\n\nField must be greater than value supplied.\n\n\n\naddQuery\\('priority', '>', 1\\);\n\n
\n\n<\n\n\n\nField must be less than value supplied.\n\n\n\naddQuery\\('priority', '<', 3\\);\n\n
\n\n>=\n\n\n\nField must be equal or greater than value supplied.\n\n\n\naddQuery\\('priority', '>=', 1\\);\n\n
\n\n<=\n\n\n\nField must be equal or less than value supplied.\n\n\n\naddQuery\\('priority', '<=', 3\\);\n\n
\n\n!=\n\n\n\nField must not equal the value supplied.\n\n\n\naddQuery\\('priority', '!=', 1\\);\n\n
\n\nSTARTSWITH\n\n\n\nField must start with the value supplied. The example shown on the right returns all records where the *short\\_description* field starts with the text Error.\n\n\n\naddQuery\\('short\\_description', 'STARTSWITH', 'Error'\\);\n\n
\n\nCONTAINS\n\n\n\nField must contain the value supplied somewhere in the text. The example shown on the right returns all records where the *short\\_description* field contains the text Error anywhere in the field.**Note:** The LIKE operation is not supported. Administrators must use CONTAINS in the query.\n\n\n\naddQuery\\('short\\_description', 'CONTAINS', 'Error'\\);\n\n
\n\nIN\n\n\n\nTakes a map of values that allows commas, and gathers a collection of records that meet some other requirement. Behaves as `Select * from where short_description IN ('Error', 'Success', 'Failure')`, which is identical to `Select * from
where short_description='Error'`. For example, to query all variable values that belong to a specific Activity, use the **IN** clause, and store their sys\\_ids in a map, or comma-separated list. Then query the variable value table and supply this list of sys\\_ids.\n\n
\n\naddQuery\\('short\\_description', 'IN', 'Error,Success,Failure'\\);\n\n
\n\nENDSWITH\n\n\n\nField must terminate with the value supplied. The example shown on the right returns all records where the *short\\_description* field ends with text Error.\n\n\n\naddQuery\\('short\\_description', 'ENDSWITH', 'Error'\\);\n\n
\n\nDOES NOT CONTAIN\n\n\n\nSelects records that do NOT match the pattern in the field. This operator does not retrieve empty fields. For empty values, use the operators \"is empty\" or \"is not empty.\"The example shown on the right returns all records where the *short\\_description* field does not have the word \"Error.\"\n\n\n\naddQuery\\('short\\_description', 'DOES NOT CONTAIN', 'Error'\\);\n\n
\n\nNOT IN\n\n\n\nTakes a map of values that allows commas, and gathers a collection of records that meet some other requirement. Behaves as: `Select * from where short_description NOT IN ('Error')`.\n\n\n
\n\naddQuery\\('short\\_description', 'NOT IN', 'Error,Success,Failure'\\);\n\n
\n\nINSTANCEOF\n\n\n\nSpecial operator that retrieves only records of a specified \"class\" for extended tables. The code example on the right, shows how to retrieve all configuration items that are classified as computers.\n\n\n\naddQuery\\('sys\\_class\\_name', 'INSTANCEOF', 'cmdb\\_ci\\_computer'\\);\n\n
For additional information on the operators that are available for filters and queries, see [Operators available for filters and queries](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-user-interface/r_OpAvailableFiltersQueries.md).\n\nThere are also some special methods that you can use to search for data that is NULL or NOT NULL. To search for all incidents where the *short\\_description* field has not been supplied \\(is null\\), use the following query:\n\n```\nvar target = new GlideRecord('incident'); \ntarget.addNullQuery('short_description');\ntarget.query(); // Issue the query to the database to get all records \nwhile (target.next()) { \n // add code here to process the incident record \n}\n```\n\nTo find all incidents in which a *short\\_description* has been supplied, use the following query:\n\n```\nvar target = new GlideRecord('incident'); \ntarget.addNotNullQuery('short_description');\ntarget.query(); // Issue the query to the database to get all records \nwhile (target.next()) { \n // add code here to process the incident record \n}\n```\n\nFor more information on the GlideRecord API and its available methods, see [GlideRecord](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideRecordScopedAPI.md).\n\n## GlideRecord query examples\n\nThese examples demonstrate how to perform various GlideRecord queries.\n\n### query\n\n```\nvar rec = new GlideRecord('incident');\nrec.query(); \nwhile(rec.next()) { \n gs.print(rec.number + ' exists'); }\n```\n\n### update\n\n```\nvar rec = new GlideRecord('incident');\nrec.addQuery('active',true);\nrec.query(); \nwhile(rec.next()) { \n rec.active = false;\n gs.print('Active incident ' + rec.number = ' closed');\n rec.update(); }\n```\n\n### insert\n\n```\nvar rec = new GlideRecord('incident');\nrec.initialize();\nrec.short_description = 'Network problem'; \nrec.caller_id.setDisplayValue('Joe Employee'); \nrec.insert();\n```\n\n### delete\n\n```\nvar rec = new GlideRecord('incident');\nrec.addQuery('active',false);\nrec.query(); \nwhile(rec.next()) { \n gs.print('Inactive incident ' + rec.number + ' deleted');\n rec.deleteRecord(); }\n```\n\n### Querying Service Catalog Tables\n\nYou cannot directly query the variables of the Service Catalog Request Item table \\[sc\\_req\\_item\\]. Instead, query the Variable Ownership table, \\[sc\\_item\\_option\\_mtom\\], by adding two queries, one for the variable name and another for the value. The query returns the many-to-many relationship, which you can dot-walk to the requested item. The following example finds the request items that have the variable *item\\_name* with a value of *item\\_value* and displays the request item numbers:\n\n```\nvar now_GR = new GlideRecord('sc_item_option_mtom');\ngr.addQuery('sc_item_option.item_option_new.name','item_name');\ngr.addQuery('sc_item_option.value','item_value');\ngr.query();\n \nwhile(gr.next()) {\n gs.addInfoMessage(gr.request_item.number); }\n```\n\nFor additional information see [GlideRecord](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideRecordScopedAPI.md).\n\n"}}