getRecordFields Function

This will pull all record field data for the date range or sliding time provided in the invoked function parameters. Based on the Table Type parameter provided Field Details will be used to get all spec values, corrections, comments, field values and Field Values will provide the table with one row per record and field short name as each column and the value 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)
• Date Type (Required)
• Time Zone Offset (Required)
• Start Date (Conditional)*
• End Date (Conditional)*
• Minutes Ago (Conditional)*

📘

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)

👍

Tip:

To include all forms, you can provide a blank ("") Form Name value when invoking the function.

getRecordFields("FieldDetails","", "FormName","Modified", 0, null, null, 1440)

Invoking the Functions

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 Field Details:

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

With Field Values:

getRecordFields("Field Values","ADFTP Audit Form MS", "FormName","Modified", 0, null, null, 1440)
getRecordFields("Field Values","ADFTP", "FormLike","Modified", 0, null, null, 1440)
getRecordFields("Field Values","", "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 Result:

Example Of Field Details Table Type:

The following field columns will be outputted in a table.

#table({
  "RecordNumber",
  "RecordCompliance",
  "SubmittedDate",
  "LocationName",
  "FormName",
  "FormVersion",
  "ResourceName",
  "ResourceType",
  "SubmittedUserName",
  "IsSigned",
  "IsComplete",
  "RecordComment",
  "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"
}, {})

👍

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 FunctiongetRecordFields",{{"SubmittedDate", type datetime}, {"ModifiedDate", type datetime}, {"ProcessedDate", type datetime}})

Example of Field Values Table Type:

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; see example above where Root Cause does not exist on the form for record 56353, but is included on record 56354.

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",
  "SubmittedUserName",
  "IsSigned",
  "IsComplete",
  "RecordComment",
  "SubmittedTimeZone",
  "ProcessedDate",
  "RecordId",
  "ModifiedDate",
  "ModifiedUserName",
  "Row",
  "FieldShortName1",
  "FieldShortName2"
}, {})

👍

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 FunctiongetRecordFields",{{"SubmittedDate", type datetime}, {"ModifiedDate", type datetime}, {"ProcessedDate", type datetime}})