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:

G:\Customer Files\A\Any Co (1234)\

G:\Customer Files\A\Able Co (4743)\

G:\Customer Files\B\Best LLC (343)\

G:\Customer Files\Z\Zulu Inc (3782)\

... etc.

G:\Vendor Files\E\ElectricCo (3231)\

G:\Vendor Files\F\Fedex (3825)\

G:\Vendor Files\U\UPS (1234)\

... etc.

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.

Steps
  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.

MD "Customer Files"

CD "Customer Files"

MD 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

CD ..

MD "Vendor Files"

CD "Vendor Files"

MD 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

CD ..

  1. Run the BAT file.
SQL To Build the Create Folder CMD

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

<code>

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+

You could leave a comment if you were logged in.