Call the Step connected to a For-Each Step with a group of values. (similar to an array.)

Use the GroupBy functionality for high performance File-Cherry-Pickling and for combining values for use with multivalued SharePoint columns.

When the loop runs, in stead of calling the connected Step for each row, the values assigned to the generated Variables are concatenated separated by ;#. When the group-by conditions are satisfied the connected Step is called.

Grouping data is defined in: “Variable assignment For-Each input row”

  • You can GroupBy column variables directly from the source data set.
  • You can use any associated transformation across all source columns and other Variables

Important: The GroupBy assignments must be the last assignments in the Variables and remember NOT to use any of the variables from the source data set.

Notice that the variables that defines the group holds a single value while the other variables hold multiple values separated by ;# (the data is grouped)
The format for the multivalued non GroupBy variables is: MyValue1;#MyValu2;#..
(The For-Each GroupBy generates that format. But you can also generate is yourself.)

The supported scenarios when GroupBy is supported:

  1. File transfer to SharePoint Online (M365)
  2. converting multirow data into single rowe data in a format that fits SharePoint vulti value columns types
The 2 main SharePoint use cases
  • Cherry-picking files from one or more sources and then use optimized transfer to SharePoint. By grouping files at the folder level or document library level it is possible to greatly enhances performance especially to Microsoft 365
  • Generating data for multivalued SharePoint columns such as Managed Metadata, Lookup and Choice.
Rules and recommendations for using the Group-By with the File Management Step
  • FileShare source: Use the column FileShare Folder and insert one or more NON grouping column variable optionally combined with hardcoded values and other variables
  • SharePoint source: Use the column Override URL and insert one or more NON grouping column variable optionally combined with hardcoded values and other variables
  • SharePoint destination: Use the column Override URL and insert one or more grouping column variable optionally combined with hardcoded values and other variables
    In the metadata setup you can use all assignment types and transformations, including:
    [Rename file] to choose a dynamic file name in place of the source file’s name.
    [Alternative destination path] to define the destination folder for the file.
  • File destination URL or UNC may NOT contain the filename. (Only contain the path to the destination.)
    If you want to rename the file specify the alternative file name in the assignment
    [<Rename file>]. Transformations are fully supported.
  • The destination library must be one of the grouping values. For example a folder or a Document library
  • The source file references must include the absolute path and filename (for cherry-picking he file).
  • To gain performance, the source data set in the For-Each Step must be sorted by destination library and ideally also the destination path. This to gain best performance in terms og the fewest calls to the FileManagement Step and optimal use of multitasking. If the grouping columns are calculated and the source data set does not have columns suited for sorting then the sorting order can be accomplished by creating a staging table having the calculated values in separate columns. And then use that table for driving the cherry-picking operation for best performance.
  • You may use custom variables also with transformations to generate the absolute path and filename and various metadata values in the Grouping variable setup
  • If there is no need for multiple destination SharePoint libraries then
    use the option [x] Group all rows into 1 and call Step once
    Then all the rows from the source data set will be grouped together and there will be just 1 call to the associated FileManagement Step
  • You may rename the destination file by assigning the [<Rename file>] property.
  • Transformations are fully supported also for file renaming.
  • SharePoint source: The widest grouping is Site level . Your source grouping values may not span multiple sites
  • SharePoint destination: The widest grouping is Document Library. Your grouping values may not span multiple Document Libraries
Last modified: 2 May 2023