Here is the scenario.  

The company has a Quote process and a Job process.  Some Quotes turn into Jobs (hopefully) but not always.

Every department had a different share on the file server with a different drive letter mapping in their logon script. 

  The quote department had their own way saving quote-related documents.  By Company Name, Year, Contact, Quote number. 

The Engineering department also had Quote relate documents.  Different share saved by Product type, Year, quote number.  They also had Job related documents.  Saved in a different share again by Product type, Year, Job number. 

  The Drafting department had yet a different schema, as did Accounting and Purchasing. 

  All of these different directories are manually created by the users and subject to spelling errors and who made the directory.  Example is the directory AT&T or ATT or AT & T.  You get the idea. 

    The issue:  No one person could find all of the documents related to a Quote or Job.

The challenge presented to me was as follows. 

Find a way to automatically create directories based on Quote or Job number.  If possible link the Jobs back to the Quotes.  Maintain directory security.  Example purchasing may need to view CAD drawings but not edit them.  Or Engineering does not need to see Accountings costing spreadsheets. Etc. Etc..  But the CEO can go to one location and see everything.  

Here is was the solution: 

  Create a directory structure on the fly based on Quote number or Job number created in the SQL database.  If a Quote or Job is entered in the DB a directory for needed departments is created automatically and security is set. 

Here is the format  

Example Quote number Q010619001  

Directory shall be broken down into its date components then the sequentially for each quote created that day.   In this example the UNC name would be \\KENPDC01\ORDER\Quote\01\06\19\001 

The Mapped Drive would be O:\Quote\01\06\19\001 

Example Job number J010619001  

Directory shall be broken down into its date components then the sequentially for each job created that day.   In this example the UNC name would be \\KENPDC01\ORDER\Job\01\06\19\001 

The Mapped Drive would be O:\Job\01\06\19\001 

Okay…  Now we had a company wide directory schema, but how to implement?  

The solution was to create two batch files to reside on the SQL server.  One file for Quote numbers and one for Jobs.  Then have a SQL Trigger on the Quote and Job DBs/tables to fire off the batch files and pass the numbers to the command line.  See the ZIP file for newdirs.bat and newdirs2.bat  

The batch files create the directory structure then sets NTFS security on the directories it just created.

The next issue was how to link the Quotes and Jobs together.  Enter Flexiblesoft ShortCut’s Twister.  This is a small tool that as the name indicates creates shortcuts.  This is a very inexpensive tool but very powerful when triggered with a batch file. 

Our Job DB had an existing column for Quote Number so the DB was in some way linking Jobs to Quotes already.  When a Job was entered into the DB the associated Quote was entered.   So we modified the newdirs batch so if a Quote number were entered into the Job DB it would crate an additional directory.  We then pass the Quote number to Flexiblesoft ShortCut’s Twister, which creates a link from the Job directory to the back to the Quote directory.  

Now it works.  The SQL DB automatically creating directories on the file server based on data inserted then linking directories using automatically created shortcuts.  Wow! 

The first picture is the job directory structure.  As you can see z_reference directory contains three shortcuts.  These were created by Flexiblesoft ShortCut’s Twister.   Even though you are in the Job directory if you click on one of the folder shortcuts it will automatically jump you to the associated Quote directory. 

This picture shows the Quote directory structure.   I used the shortcuts to get there. 

Now the really cool stuff.  For some types of jobs and quotes the company always uses a set of templates.  Because we now have a structured directory format we can use our batch files to move templates to the newly created directories.  This is done with the same SQL trigger used to pass the Job/Quote number to the command line only we pass the Job/Quote type also.   See the copyfiles.bat. 

There you have it. 

Enjoy!