In Progress

VBScript (JScript/Powershell?) routine to build a file system directory structure on a network server based on details queried from an ODBC database source.

Overview:

VBScript (JScript/Powershell?) routine to build a file system directory structure on a network server based on details queried from an ODBC database source.

Detail:

Key system is Netsuite (CRM / accounting system) - this stores customers information. For each customer in Netsuite we need to automatically create a directory structure on the local network server in order that relevant files for each customer can be filed away . The customers records in Netsuite is accessible via an ODBC interface (see below for sample)

The routine is to be run without user interaction on a scheduled basis on a Windows 2003 server via the scheduler (running via the Windows Script host). The database stores the last modification date of a record (stored as UTC timestamp) and hence the SQL query can be restricted just to fetch new or modified records since the last time the routine was run. The script would need to record the last run time in a local file system file or registry setting (no write access to the database is available)

For each customer record modified since the last time the routine was run, we need to create the following directories if they do not already exist:

<servername><sharename><businessentity><companyname>

<servername><sharename><businessentity><companyname>Letters

<servername><sharename><businessentity><companyname>Orders

<servername><sharename><businessentity><companyname>Payments - Receipts

<servername><sharename><businessentity><companyname>Proposals

<servername><sharename><businessentity><companyname>Site Survey - QAV

<servername> = set via a variable in the script

<sharename> = set via a variable in the script

<businessentity> = field retrieved via SQL query

<companyname> = field retrieved via SQL query

Ideally the script would log activity into a text file (start of processing, count of records retrieved, each <companyname> with status (i.e. directories created or directories not created, end of processing).

I have had a quick play with VBScript to prove that it was doable and this is how far I got (the limit of my knowledge on VBScript and ADODB):

On Error Resume Next

set conn = CreateObject("ADODB.Connection")

[url removed, login to view] "dsn=[url removed, login to view];UID=USERNAME;PWD=PASSWORD"

set rs=CreateObject("ADODB.recordset")

[url removed, login to view] "SELECT REPLACE([url removed, login to view],' ' ,'_') AS BUSINESSENTITY, REPLACE(COMPANYNAME,' ' ,'_') AS COMPANYNAME, CUSTOMER_ID, LAST_MODIFIED_DATE FROM CUSTOMERS, DEPARTMENTS WHERE CATEGORY_0='CUSTOMER' AND CUSTOMERS.BUSINESS_ENTITY_ID=DEPARTMENTS.DEPARTMENT_ID", conn

for each x in [url removed, login to view]

msgbox(x.name&"="&[url removed, login to view])

next

[url removed, login to view]

set rs=Nothing

set conn=Nothing

The above "works" and displays the relevant field names and values as a popup except it only seems to return the results for a single record - presumably somehow you need to scroll through the records but that's beyond my knowledge of ADODB. The SQL query above returns the two relevant fields BUSINESSENTITY and COMPANYNAME in the correct format. It also returns the LAST_MODIFIED_DATE, but obviously we need to change the WHERE clause to only return those where the LAST_MODIFIED_DATE is > then the last time the routine was run.

No need to worry about changes of company names / deletions of customer records or any other such cases.

Skills: Javascript, Visual Basic, Windows Desktop

See more: powershell odbc, jscript odbc, jscript powershell, powershell odbc connection, systemdir vbscript, jscript file system, powershell odbc records, powershell count records, powershell adodb dsn, vbscript read filesystem directory, vbscript run file directory, odbc powershell, powershell jscript, sql query jscript, netsuite powershell, powershell netsuite, powershell odbc query, survey interface, structure stores, sql server resume, sql server open source, source format, source for accounting, server resume, scheduler in javascript

About the Employer:
( 2 reviews ) Queen Victoria Building, Australia

Project ID: #251518

Awarded to:

MKCline

I will be happy to assist you with this project. Please see PMB.

$55 USD in 0 days
(13 Reviews)
3.9

2 freelancers are bidding on average $53 for this job

anuiz

Hi sir, I'm ready to help.

$50 USD in 2 days
(2 Reviews)
2.0