{$page}

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.

Concept

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.
  1. 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.
  1. Open a CMD (DOS) prompt.
  1. Navigate to the root folder (where you saved the bat files).
  1. 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_formalsql

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

Cautions

Warning or areas where the approach might not work as expected

Steps

  1. Step 1
  1. Step 2

Source

Contributor: Team Cyrious

Date: May 2010

Version: Control 4.0+

See Also

You could leave a comment if you were logged in.