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.
!When transferring >50 files using For-Each to SharePoint Online it is extremely important to group the files so that the underlying API gets more than 1 file to process at the time. Failure to do so will result in bad performance, throttling and ultimately Server 500 error with tenant blocked 24 hours + unpredictable results.
You can Group-By columns from the source or you can use a Variable also with an associated transformation.
Important: The GroupBy assignments must be the last assignments in the Variables list.
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.)
*Group all rows into 1 and call Step once
When selected then each [<#MyCol>] Variable will contain the column values from all rows separated by ;#
| 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/destination: Use the column
FileShare Folder
|
- SharePoint source: Use the column
Override URL
|
- SharePoint source/destination: Use the column
Override URL
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.
|
- 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).
For SharePoint source use the column “Override URL”
|
- 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
|
- If there is no need for multiple destination SharePoint libraries then
use the option [x] Group all into 1
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
|
- If source or destination variables has multiple values you cannot merge with constants for example prefixing with a http://.. address. This kind of calculation must be done in the For-Each transformations.
|
- 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
|
- Recommended max grouping count is 50000. Theoretically there is no limit however the internal string manipulation overhead will eat up the performance gained, unless you have a massive CPU. If you have more files per group consider regrouping using additional columns.
|
Last modified:
7 March 2023
Post your comment on this topic.