logo Windmill Hub
Login
All items
0

List Records

by
Published 4 days ago

Query records from any table with an encoded query (sysparm_query), pagination, column selection, and raw/display values.

Script servicenow Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 5 days ago
All edits
Permalink
1 
//native
2
3 
export type DynSelect_table = string
4 
export type DynMultiselect_fields = string[]
5
6 
function authHeader(auth: RT.Servicenow) {
7 
  return auth.token
8 
    ? `Bearer ${auth.token}`
9 
    : `Basic ${btoa(`${auth.username}:${auth.password}`)}`
10 
}
11
12 
// Dropdown of the instance's tables (sys_db_object).
13 
export async function table(auth: RT.Servicenow) {
14 
  const response = await fetch(
15 
    `${auth.instance_url}/api/now/table/sys_db_object?sysparm_fields=name,label&sysparm_limit=10000`,
16 
    {
17 
      headers: {
18 
        Authorization: authHeader(auth),
19 
        Accept: "application/json",
20 
      },
21 
    }
22 
  )
23 
  if (!response.ok) {
24 
    throw new Error(`${response.status} ${await response.text()}`)
25 
  }
26 
  const { result } = (await response.json()) as {
27 
    result: { name: string; label: string }[]
28 
  }
29 
  return result
30 
    .filter((t) => t.name)
31 
    .map((t) => ({ value: t.name, label: `${t.label} (${t.name})` }))
32 
    .sort((a, b) => a.label.localeCompare(b.label))
33 
}
34
35 
// Walk the table's inheritance chain (sys_db_object.super_class) so the column
36 
// list includes fields inherited from parent tables, not just fields defined on
37 
// the chosen table (sys_dictionary stores each field under its defining table —
38 
// e.g. incident extends task, so short_description/priority live on task).
39 
// Best-effort: any failure falls back to just the table itself.
40 
async function tableChain(auth: RT.Servicenow, table: string) {
41 
  const chain: string[] = []
42 
  let current: string | undefined = table
43 
  while (current && !chain.includes(current)) {
44 
    chain.push(current)
45 
    const response = await fetch(
46 
      `${auth.instance_url}/api/now/table/sys_db_object?sysparm_query=name=${current}&sysparm_fields=super_class.name&sysparm_limit=1`,
47 
      {
48 
        headers: {
49 
          Authorization: authHeader(auth),
50 
          Accept: "application/json",
51 
        },
52 
      }
53 
    )
54 
    if (!response.ok) break
55 
    const { result } = (await response.json()) as {
56 
      result: { "super_class.name"?: string }[]
57 
    }
58 
    current = result[0]?.["super_class.name"] || undefined
59 
  }
60 
  return chain
61 
}
62
63 
// Dependent multi-select: the chosen table's columns (sys_dictionary), including
64 
// columns inherited from parent tables.
65 
export async function fields(auth: RT.Servicenow, table: DynSelect_table) {
66 
  if (!table) return []
67 
  const chain = await tableChain(auth, table)
68 
  const url = new URL(`${auth.instance_url}/api/now/table/sys_dictionary`)
69 
  url.searchParams.append(
70 
    "sysparm_query",
71 
    `nameIN${chain.join(",")}^elementISNOTEMPTY`
72 
  )
73 
  url.searchParams.append("sysparm_fields", "element,column_label")
74 
  url.searchParams.append("sysparm_limit", "10000")
75
76 
  const response = await fetch(url, {
77 
    headers: {
78 
      Authorization: authHeader(auth),
79 
      Accept: "application/json",
80 
    },
81 
  })
82 
  if (!response.ok) {
83 
    throw new Error(`${response.status} ${await response.text()}`)
84 
  }
85 
  const { result } = (await response.json()) as {
86 
    result: { element: string; column_label: string }[]
87 
  }
88 
  return result
89 
    .filter((f) => f.element)
90 
    .map((f) => ({
91 
      value: f.element,
92 
      label: `${f.column_label} (${f.element})`,
93 
    }))
94 
    .sort((a, b) => a.label.localeCompare(b.label))
95 
}
96
97 
/**
98 
 * List Records
99 
 * Query records from any table with an encoded query (sysparm_query), pagination, column selection, and raw/display values. Returns one page; iterate offset until fewer than limit rows return.
100 
 */
101 
export async function main(
102 
  auth: RT.Servicenow,
103 
  table: DynSelect_table,
104 
  query: string | undefined,
105 
  limit: number | undefined,
106 
  offset: number | undefined,
107 
  fields: DynMultiselect_fields | undefined,
108 
  display_value: "false" | "true" | "all" | undefined
109 
) {
110 
  const url = new URL(`${auth.instance_url}/api/now/table/${table}`)
111 
  if (query !== undefined && query !== "") {
112 
    url.searchParams.append("sysparm_query", query)
113 
  }
114 
  url.searchParams.append("sysparm_limit", String(limit ?? 100))
115 
  if (offset !== undefined) {
116 
    url.searchParams.append("sysparm_offset", String(offset))
117 
  }
118 
  if (fields && fields.length > 0) {
119 
    url.searchParams.append("sysparm_fields", fields.join(","))
120 
  }
121 
  if (display_value !== undefined && display_value !== "") {
122 
    url.searchParams.append("sysparm_display_value", display_value)
123 
  }
124
125 
  const response = await fetch(url, {
126 
    method: "GET",
127 
    headers: {
128 
      Authorization: authHeader(auth),
129 
      Accept: "application/json",
130 
    },
131 
  })
132
133 
  if (!response.ok) {
134 
    throw new Error(`${response.status} ${await response.text()}`)
135 
  }
136
137 
  return await response.json()
138 
}
139