This WIKI article discusses the approach Cyrious took when it moved all of it's customer and vendor records online. Specifically, we wanted to place all scanned information in a folder structure that we could easily find it.

We created a folder structure that looked like:

Though we have several thousand customers and a few hundred vendors, this folder structure was faily workable. Since we have many customers with the same or similar name, we wanted to Customer/Vendor Number also in the name, which is the number in parenthesis.

1. Build the CMD (DOS) command to create the folder using SQL to access the company names.
   • Run the SQL Below and view the results.
2. Create a BATch file with the commands.
   • Open NotePad.
   • Copy the data in the SQL results and paste it in notepad.
   • Save the file as CreateFolders.BAT in the root folder.
3. Open a CMD (DOS) prompt.
4. Navigate to the root folder (where you saved the bat files).
5. Create the Hierarchy folders
   • This procedure requires that the “Customer Files”, “Vendor Files”, “A”, “B”, etc. folders already exist.
   • Copy and run these lines to create the folders.

1. Run the BAT file.

code formal="sql"

select 'IF NOT Exist "'+FolderName+'" MD "'+FolderName+'"' as BatCommand

from

(

  select

     ID as AccountID,

     'Customer Files\'

     + LEFT(CompanyName, 1) + '\'

     + RTrim(CompanyName) + ' ('

     + cast(AccountNumber as varchar(6)) + ')\' as FolderName

  from Account

  where IsActive = 1 and IsClient = 1

  union all

  select

     ID as AccountID,

     'Vendor Files\'

     + LEFT(CompanyName, 1) + '\'

     + RTrim(CompanyName) + ' ('

     + cast(AccountNumber as varchar(6)) + ')\' as FolderName

  from Account

  where IsActive = 1 and IsVendor = 1

) Temp

order by FolderName

Warning or areas where the approach might not work as expected

1. Step 1
2. Step 2

Contributor: Team Cyrious Date: May 2010 Version: Control 4.0+