getRecords Function

This will pull all header level record data for the date range or sliding time provided in the invoked function parameters. That being the case each row will contain 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)

📘

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

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

With Date Range:

getRecords("Processed", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, null, null)
getRecords("Processed", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, "ADFTP Audit Form MS", "FormName")
getRecords("Processed", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null, "ADFTP Audit Form MS", "FormLike")

With Min Ago:

 getRecords("Modified", 0, null, null, 1440, null, null)
 getRecords("Modified", 0, null, null, 1440, "ADFTP Audit Form MS", "FormName")
 getRecords("Modified", 0, null, null, 1440, "ADFTP Audit Form MS", "FormLike")

📘

Note:

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

getRecords Result:

The following field columns will be outputted in a table.

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

👍

Tip:

Once the table is created, remember to rename (Right-click the table and click Rename) and format your columns. You can set all ProcessedDate, ModifiedDate, and SubmittedDate as datetime fields; see Power Query example below.

Table.TransformColumnTypes(#"Invoked FunctiongetRecords",{{"SubmittedDate", type datetime}, {"ModifiedDate", type datetime}, {"ProcessedDate", type datetime}})