GetVoidedRecords

📘

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

The GetVoidedRecords function retrieves voided record-level data based on the parameters provided. Each row in the result set represents a single record.

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)
• Location Name (Optional)
• Resource Name (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 (TableType as text, FormName as text, FormQueryType as text, DateType as text, TimeZone as number, StartDate as datetime, EndDate as datetime, MinAgo as number, Location as text, Resource as text, RequestIdentifier as text)

Invoking the Function

Here is an example of invoking them using Power Query or through the visual editor.

With Date Range:

GetVoidedRecords("Submitted", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, null, null)
GetVoidedRecords("Modified", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, "HC-Test Form", "FormName")
GetVoidedRecords("Processed", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, "HC-Test", "FormLike")

With Min Ago:

 GetVoidedRecords("Submitted", 0, null, null, 1440, null, null)
 GetVoidedRecords("Modified", 0, null, null, 1440, "HC-Test Form", "FormName")
 GetVoidedRecords("Processed", 0, null, null, 1440, "HC-Test", "FormLike")

📘

Note:

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

GetVoidedRecords Results

The following field columns will be outputted in a table.

#table({
  "RecordNumber",
  "RecordCompliance",
  "SubmittedDate",
  "LocationName",
  "FormName",
  "FormVersion",
  "ResourceName",
  "ResourceType",
  "ResourceId",
  "SubmittedUserName",
  "IsSigned",
  "IsComplete",
  "Comment",
  "SubmittedTimeZone",
  "ProcessedDate",
  "RecordId",
  "ModifiedDate",
  "ModifiedUserName",
  "VerificationList",
  "ImageList",
  "Row"
}, {})

Formatting Column Data Types

👍

Tip:

Once the table is created, you may format the columns as needed. Select Transform-> Detect Data Type.

For example, you may choose to set ProcessedDate, ModifiedDate, and SubmittedDate as datetime fields (see the Power Query example below).

Table.TransformColumnTypes(Source,{{"RecordNumber", Int64.Type},{"SubmittedDate", type datetime},{"ModifiedDate", type datetime},{"ProcessedDate", type datetime}})