The Update Job States SQL Scripts enables you to define SQL scripts to import job states into DMS and update the corresponding jobs.  This import can be used to set the job states in DMS to complete from a host system, or to any other job state as required.  Before you add the SQL scripts, one or more database connection strings must be defined.  Any number of SQL import scripts can be defined.  For help writing SQL scripts in DMS please see Hints and Tips Writing SQL Scripts.  For details on the SQL columns required for the import, please see SQL Update Jobs States Columns Required.

To define Job Sates importing SQL scripts, select Import | Import Settings | Update Job States SQL Scripts.  A window will appear with the current list of defined update job states by SQL scripts.  The example below shows the window without any SQL Scripts defined:

The following tabs and commands are:

File

1. New  Select File | New to add a new job states import SQL script.
2. Edit  Select File | Edit to edit the currently highlighted SQL script in the tree, or double click on the SQL script name in the tree to edit the script.
3. Delete  Select File | Delete to delete the currently highlighted SQL script in the tree.  DMS will verify the SQL Script you want to delete.  Click Yes to delete the SQL script or No to ignore the delete.

The tab requires three entries:

1. Script Name The script name is a unique name used to identify the update script.  The name must be unique and can be up to 45 alphanumeric characters long. 
2. Connection Name The connection string name is entered for the database you wish to connect to.  Valid connection strings are displayed in the drop down menu. 
3. Update Jobs States SQL Script Enter the SQL script to extract the necessary update information from the external database to add information to DMS.  

Click Save to save the new script.  Click Cancel to cancel the addition of the new script. 

In order to help you build the SQL script DMS provides a  button that adds the necessary information for you to add your custom information to.  Click on the Build SQL button, DMS will ask you to verify that you want to add the required SQL fields to the script.  If you select yes, DMS adds the SQL script as in the example below:

To the above script you would add the parameters required to complete the SQL.  For example, for each value after the “as” enter the name of the column or calculated field from the source database.  You also need to add the table(s) to the “From” statement, any joins, and any required information to the “where” clause.  Note that the yellow line after the lines numbers indicates lines that have been edited.  Once the save button has been selected, the yellow line will disappear.

The Job State is an alphanumeric string value and must match an existing job state that has been defined in DMS.  Examples of job states are Hold, Quotation, Schedule, Rework, Released, History, Completed, etc.  Please refer to menu item Tables | Job States from the main menu in DMS for a complete list of the job states you have defined for your DMS installation.

Once a script has been entered, you can test the script by using the  button. This button will run the script and access the external database.

Please note that if an Order Number being imported does not exist in DMS, a warning message is not printed as the import may select many jobs from the host that are not in DMS.