Summary Queries

📘

Note

Query performance depends on the volume of data retrieved within the selected time frame. Using Submitted Date Types and narrower date ranges can improve performance, but may exclude relevant data. Choose your date range carefully to balance speed and completeness.

Summary

This document covers the following Queries:

• GetForms
• GetLocations
• GetResources

The Summary Queries will provide details for each form, location or resource with a Record Count, how many records of those are passed (compliant) and the pass percentage. The GetResources table will also include the Resource Type (TypeName).

The values will be determined by the date range provided using the following parameters:

• Date Type (Required)
• Time Zone Offset (Required)
• Start Date (Conditional)*
• End Date (Conditional)*
• Minutes Ago (Conditional)*
• Form Name (Optional)
• Form Query Type (Optional)
• Request Identifier (Optional)

📘

Note:

*Start Date + End Date or Minutes Ago must be provided to build the date range query. All other optional values a null value can be provided; see examples below.

For more detail on the parameters see the full list of descriptions here.

function (dateType as text, timeZone as number, startDate as datetime, endDate as datetime, MinAgo as number, formName as text, formQueryType as text)

Invoking the Functions

All Summary Queries utilize the same parameters. Here is an example of invoking them using Power Query or through the visual editor.

With Date Range:

={FunctionName}("{dateType}", {timeZone}, #datetime({year}, {month}, {day}, {hour}, {min}, {sec}), #datetime({year}, {month}, {day}, {hour}, {min}, {sec}), null, null, null)

Example:

GetForms1("Processed", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, null, null)
GetLocations1("Submitted", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, null, null)
GetResources1("Modified", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, null, null)

With Min Ago:

={FunctionName}("{dateType}", {timeZone}, null, null, {MinAgo}, null, null)

 GetForms1("Processed", 0, null, null, 1440, null, null)
 GetLocations1("Submitted", 0, null, null, 1440, null, null)
 GetResources1("Modified", 0, null, null, 1440, null, null)

📘

Note:

You can fill out the function parameters and click _Invoke _to create the table automatically. See example below.

GetForms Results

The following field columns will be outputted in a table.

#table(
  {
    "FormName",
    "Row",
    "RecordCount",
    "PassCount",
    "PassPercent"
  }, {})

GetLocations Results

The following field columns will be outputted in a table.

#table(
  {
    "LocationName",
    "Row",
    "RecordCount",
    "PassCount",
    "PassPercent"
  }, {})

GetResources Results

The following field columns will be outputted in a table.

#table(
  {
    "ResourceName",
    "ResourceType",
    "ResourceId",
    "Row",
    "RecordCount",
    "PassCount",
    "PassPercent",
  }, {})

Formatting Column Data Types

👍

Tip:

Once the table is created, rename it by right-clicking and selecting Rename. You may also format your columns as needed — Row, RecordCount, PassCount, and PassPercent can all be set to a numeric data type.

See the Power Query example below.

Highlight Function and click Detect Data Type

Make appropriate changes

= Table.TransformColumnTypes(Source,{{"FormName", type text}, {"Row", Int64.Type}, {"RecordCount", Int64.Type}, {"PassCount", Int64.Type}, {"PassPercent", Int64.Type}})