Sort and group records

Kathyana Queeman

Updated November 25, 2024 22:19

The Sorting and Grouping option allows you to manage your flow’s files by sorting and grouping records by field. This feature allows you to aggregate your data based on your chosen field(s), and sort the records in ascending or descending order (ASC or DESC).

Sort and group (only file provider exports)

Group (HTTP, database, and data warehouse exports and lookups)

Sort by

This feature allows you to sort by multiple fields (ASC or DESC) or in the order that the fields were initially entered. You can enter the “Sort records by fields” by a field ID or by using a dot notation.

Note:

This feature does not sort numeric fields, including dates, in descending or ascending values.
Sorting only applies to file provider exports. You can’t sort records for HTTP, database, and data warehouse exports or lookups at this time.

Group by

You can group records when parsing files from any file source, or when exporting records from HTTP, databases, or data warehouses. This feature allows you to aggregate your data based on your chosen field(s). Select the fields you want to group, based on your sample response data. Fields other than basic data types (such as string and number) are unavailable. If the source application does not sort the exported data, then grouping may not work as expected. The order of each grouping is always respected. You can enter records by field id, by using a dot notation, or one field at a time. When you input at least one field, the platform will group records in the order that the fields were initially entered, from top to bottom.

Note: The dot notation starts from “record”: { in the example below.

Take the following JSON record:

{
  "page_of_records": [
    {
      "record": {
        "NAME": "Sarah",
        "AGE": 97,
        "PURCHASE": "Beach towel",
        "CATEGORY": "Summer"
      }
    },
    {
      "record": {
        "NAME": "John",
        "AGE": 98,
        "PURCHASE": "Ski gloves",
        "CATEGORY": "Winter"
      }
    },
    {
      "record": {
        "NAME": "Ana",
        "AGE": 99,
        "PURCHASE": "Beach ball",
        "CATEGORY": "Summer"
      }
    },
    {
      "record": {
        "NAME": "James",
        "AGE": 100,
        "PURCHASE": "Snowboard",
        "CATEGORY": "Winter"      
       }
    }
  ]
}

Using the example JSON record, you can group by the Category field. Your data is grouped into Summer and Winter rows. Of course, integrator.io can handle more detailed groupings, say if you want to group records by Category and Purchase, or by Category, Age, and Purchase. If you’re working with a file provider export, you can sort the records by Ascending or Descending.

{
  "page_of_records": [
    {
      "rows": [
        {
          "CATEGORY": "Summer",
          "NAME": "Sarah",
          "AGE": 97,
          "PURCHASE": "Beach towel"
        },
        {
          "CATEGORY": "Summer",
          "NAME": "Ana",
          "AGE": 98,
          "PURCHASE": "Beach ball"
        }
      ]
    },
    {
      "rows": [
        {
          "CATEGORY": "Winter",
          "NAME": "John",
          "AGE": 99,
          "PURCHASE": "Ski gloves"
        },
        {
          "CATEGORY": "Winter",
          "NAME": "James",
          "AGE": 100,
          "PURCHASE": "Snowboard"
        }
      ]
    }
  ]
}

In some cases, you might have CSV file exports or lookups:

Name	Age	Purchase	Category
Sara	97	Beach towel	Summer
Anna	98	Ski gloves	Winter
John	99	Beach ball	Summer
James	100	Snowboard	Winter

In this case, you’d group by column (Category), and each row would be grouped into the Summer or Winter category. Logically, it would look something like this:

Category	Name	Age	Purchase
Summer	Sara	97	Beach towel
Summer	John	99	Beach ball
Winter	James	100	Snowboard
Winter	Anna	98	Ski gloves

Tip: Remember that the Page size property under the Advanced section determines how many records/groups can go into a page. If a single group exceeds the page size, that group is discarded from the export, and an error indicating this issue is reported.

Sorting and grouping matrix

This matrix details all possible sorting and grouping outcomes.

Group records – not provided Group records – provided

Sort records – not provided

File processed as is.

Content is grouped by group record fields.

If a record has a missing field, then the record is grouped in a “missing fields” bucket.

Example: group records by firstName field, but a record does not have a firstName field.

Sort records – provided Content is sorted by the created sort rules. Content is first sorted, then grouped.

Sorting and grouping with new and existing flows

This section provides details regarding sorting and grouping new and existing flows.

Sort and group in a new flow

When sorting and grouping files in a new lookup or export:

Select a Sorting and grouping option to specify the file transfer requirements.
Note: Deprecated grouping fields are not available when creating a flow.
Specify whether the file has headers.
Sort and group records as needed.
Select Save and close.

Edit sort and group in a new flow

When editing sorting and grouping in a new flow:

Select a Sorting and grouping option to specify the file transfer requirements.
Note: Deprecated grouping fields are not available when creating a flow.
Specify whether the file has headers.
Sort and group records as desired.
Select Save and close.

Edit sort and group using the deprecated feature

When editing the sorting and grouping in an existing flow:

The deprecated fields are available until you start using the new feature.
Existing, deprecated group fields are automatically generated in the new feature, though they may require further configuration.
After the new fields are transferred and saved, the deprecated fields will disappear permanently. The only way to use the old fields is through the integrator.io API.