The Job Import SQL Scripts are where you define the SQL to import jobs from an external database into DMS.  Before you add the SQL scripts, one or more 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.  Once you have completed and tested the scripts, Job Import via SQL is used to run the script.

To define job importing SQL scripts, select Interface | Import settings | Job Import SQL Scripts.

A window will appear with the current list of defined job import SQL scripts in the tree.  The example below shows an empty list of SQL scripts in the tree:

The following tabs and commands are:

File

1. New  Select File | New to add a new job 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 job 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 job import SQL script has two tabs; the Job SQL tab where you define the name of the SQL script, the connection name and the SQL to retrieve job header information, and the Other SQL tab where you define the following job information:

1. Assembly
2. BOM Link
3. Job Material
4. Job User Field
5. Material Details
6. Operation Material
7. Operation User Fields
8. Operations

If you do not use one of the above SQL definitions, leave the SQL script for these areas blank.  DMS ignores blank SQL scripts.

Note that for each SQL script, the following SQL Order By clause is recommended to ensure the data is imported correctly:

1. Job:  Order By OrderNumber
2. Assembly:  Order By AssemblyName
3. BOM Link:  Order By OrderNumber
4. Job Material:  Order By OrderNumber
5. Job User Field:  Order By OrderNumber
6. Material Details:  Order By OrderNumber
7. Operation Material:  Order By OrderNumber, OpSeq
8. Operation User Fields:  Order By OrderNumber, OpSeq
9. Operations:  Order By OrderNumber, OpSeq

The job tab requires three entries:

1. Script Name The script name is a unique name used to identify the job import.  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. Job SQL Script Enter the SQL script to extract the necessary information from the external database to add information to DMS. 
4. 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 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.

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.  Depending on the number of rows selected, the test may take a few minutes to execute.  After the test, press the close button to close the test window.

If an error occurs, such as a missing column of required information, the test will display the error in a window.  Close the test window and make the changes to the script as required.

The example below shows the Other SQL tab window:

Other SQL Tab

The Other SQL tab requires the following information:

1. Script Type The script type is one of the following:
1. Assembly
2. BOM Link
3. Job Material
4. Job User Field
5. Material Details
6. Operation Material
7. Operation User Fields
8. Operations
2. Number of User Fields If Job User Fields or Operation User Fields was selected, you must enter in the number of user fields that will be import.  DMS will then build the SQL based on the Number of User Fields entry.
3. SQL Script Enter the SQL script to extract the necessary information from the external database to add information to DMS. 
4. Click Save to save the new script.  Click Cancel to cancel the addition of the new script.

Depending on the Script Type selected, pressing the will add the necessary SQL information for you to customize.  The example below shows the Material Details script when the Build SQL button has been pressed:

Note that for user fields you must enter the number of user fields that will be imported.  For example, if you have three job user fields to be imported, enter 3 to the Number of User Fields.  DMS will build the corresponding SQL as follows:

The following is an example of importing three user fields using SQL from and external database table called ExternalJobTbl.  The MapNo must match the DMS user field map reference numbers as defined by User Fields Required.  In the example below, a customer, drawing number, and engineer name are being added to the DMS user fields with map reference numbers 1, 2, and 3:

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.  Depending on the number of rows selected, the test may take a few minutes to execute.  After the test, press the close button to close the test window.

If an error occurs, such as a missing column of required information, the test will display the error in a window.  Close the test window and make the changes to the script as required.

Please note that the test does not validate that the information coming in from an external database is correct.  Rather, it is verifying that the SQL script structure is correct and the external database can be accessed.