GetRecordFields

📘

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 GetRecordFields function retrieves all record field data for the specified date range or sliding time defined in the invoked function parameters. Additionally, the Table Type parameter determines how the returned data is structured:

• Field Details – Returns all field-level information, including specification values, corrections, comments, and field values.
• Field Values – Structures the data with one row per record, where each field short name appears as a column, and the corresponding value is populated in each cell.

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

• Table Type (Required)
• Form Name (Required)
• Form Query Type (Required)
  • FormName (Exact Match)** is the only option
• Date Type (Required)
• Time Zone Offset (Required)
• Start Date (Conditional)*
• End Date (Conditional)*
• Minutes Ago (Conditional)*
• 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.

** FormName (Exact Match) helps alleviate potential data conflicts, especially when using the Table Type "Field Values", where the FieldShortName is pivoted into columns with the corresponding value listed beneath it.

Important: The GetRecordFields function with the Table Type set to Field Values is not supported on forms that contain duplicate field names (including repeating fields). When using Field Values, the Power BI Connector maps field names to column names, which must be unique.

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

Invoking the Function

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)

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

Table Type: Provides Field Values or Field Details and the results will format the table in a flat or vertical view. Field Details will be used to get all spec values, corrections, comments and field values. Field Values will provide the table with one row per record and field short name as each column and the value in each cell.

With TableType: Field Details

GetRecordFields("Field Details","HC-Test Form", "FormName","Submitted", 0, #datetime(2023, 6, 21, 0, 0, 0), #datetime(2023, 6, 22, 0, 0, 0), null)

With TableType:Field Values:

GetRecordFields("Field Values","HC-Test Form", "FormName","Modified", 0, null, null, 1440)

📘

Note:

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

GetRecordFields Results

Record example in UI:

Using the following form HC-Test Form, below is the representation of the record in the UI:

Field Details Table Type 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",
  "FieldCompliance",
  "FieldGroupName",
  "FieldImageList",
  "FieldName",
  "FieldOrder",
  "FieldShortName",
  "FieldType",
  "FieldUOM",
  "FieldValue",
  "IsResolved",
  "Max",
  "Min",
  "Section",
  "Target",
  "ValueDate",
  "ValueTime",
  "ValueNum",
  "ValueText",
  "Comments",
  "Correction"
}, {})

Field Values Table Type Results

All records pulled from the date range provided in the parameters will pivot each field name. If a record does not contain the field, the value will automatically show a null value. The two columns in the example above, FreeText and TypesOf Service are FieldShortNames, where the values contained on the record are in the column below.

The RecordFields table will only provide the RecordNumber and each FieldShortName as a column with the value in the row. Here is an example of a table in Power Query Format.

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

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).

Note: When using the Table Type “Field Values” option, column formatting should be applied sparingly. With this option, fields are pivoted into column headers, and the data types may vary depending on the values stored in each field.

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