Converting Dynamics GP SOP Master numbers to file system folder hierarchy

There are a number of cases where I need to store a document or documents against an “object” in GP for the various system integrations and modifications. This can be problematic, even more so with the latest update to Windows 10 that seemed to seriously detrimentally affect the performance of folders with large numbers of files or subfolders in them. Take the SOP document in GP. SOP documents are threaded together via a relationship called the master number. This master number relates all documents together between quote, order, invoice, return.

It is appropriate in some cases to store files against those master numbers. This could be done in a flat structure where a documents folder contains a sub folder for each master number and in side those folders lie the documents we wish to preserve. This starts out great, however by the time you end up with 500,000 sales master numbers and documents, opening the network store folder starts taking up to three to four minutes.  Although anything over a few thousand files or subfolders in a folder has always incurred a performance hit, with the new update this seemed to get a whole lot worse (it felt like a caching issue as subsequent re-opening of folder happened instantaneously).

Storing all those folders in a single folder is not a good user experience either, it is overwhelming to try and find a folder of interest among all those folders. A much better way is to store the folders in a hierarchy so there are never more than 1000 folders in each sub folder. This keeps the file system happy and quick.


So how to migrate the flat structure to hierarchy?


I love powershell and wish I could script in it more fluently than I can. Powershell seems to have the power to address any mundane task to do with windows servers and clients. Using power shell it is possible to move the flat structure into a new root directory, building it up into a hierarchy. My preference is for each folder to intuitively orientate the user as to where they are without having to digest/process too much of the path information.

There are lots of options for a directory arrangement scheme, but I like to do as follows, for a master number of 13224223, the folders can be organised like this:


This then necessitates a way to transform the number 13224223 to this directory arrangement. This is something regular expressions can help with, I’ve been using this technique for many, many years now.

Regular expression:


Replacement expression:


So integrating this into our powershell with optional date limit we get the following…

$sourcePath = "\\SalesArchive\"
$destPath = "\\SalesArchive\New\"
Get-ChildItem "$sourcePath\*" |? {$_.psiscontainer -and $_.lastwritetime -le (get-date).adddays(0)} |%{
if($_.Name -ne "New" -and $_.Name -ne "New2"){
$newFolder = ($destPath + "\" + ($_.Name -replace '([0-9a-zA-Z]*)([0-9a-zA-Z]{1})([0-9a-zA-Z]{1})([0-9a-zA-Z]{2})$','${1}0000\${1}${2}000\${1}${2}${3}00'));

New-Item -Path $newFolder -Type Directory -Force
Move-Item $_ $newFolder
Write-Host "source: $sourcepath dest: $newFolder"

This will move the folders into the new shape for us.

In .NET we can also use the same regular expression to open the folder or fetch/save files but using the .NET regular expression library something like this:

Return RootFolder & System.Text.RegularExpressions.Regex.Replace(DocumentMasterNumber,

Working things this way lets users access the folders instantly again and makes manual navigation of the folder structure possible, should it be needed.