Sign in Subscribe
Microsoft Dynamics GP

Converting Dynamics GP SOP Master numbers to file system folder hierarchy

Tim Wappat

• 3 min read

Large numbers of files in a folder causes some serious performance issues on windows

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 where this causes too many files in a single folder. Even more so with the latest update to Windows 10, as it seemes to seriously detrimentally affect the performance of folders with large numbers of files containted in them, or large numbers of subfolders in them.

Example SOP document in Dynamics 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 inside 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, the user will find that 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 to windows, 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, as 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. 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:

\SalesArchive\13220000\13224000\13224200\13224223\

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 expressions:

Match regular expression:
([0-9a-zA-Z]*)([0-9a-zA-Z]{1})([0-9a-zA-Z]{1})([0-9a-zA-Z]{2})$

Replacement expression:
${1}0000${1}${2}000${1}${2}${3}00

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

$sourcePath = "\\SalesArchive\"
$destPath = "\\SalesArchive\New\"
Write-Debug($destPath)
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 folders "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,
                   "([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")

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