Resource Import SQL Scripts
 		Previous Topic  Next Topic 

The Resource Import SQL Scripts enables you to define SQL scripts to import resources into DMS.  This import can be used to add new resources to DMS and to change existing resources.  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 in the help documentation.  For details on the SQL columns required for the import, please see SQL Resource Import Columns Required.


To define Resource Import SQL scripts, select Interface | Import Settings | Resource Import SQL Scripts.  A window will appear with the current list of defined resource import scripts.  The example below shows there are not any defined SQL Scripts:



The following tabs and commands are:


File


  1. New  Select File | New to add a new resource 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.


There are four tabs that can be defined for the import and they are:


  1. Resource Header SQL.  This script defines the resource header information such as the resource name, department, type of resource, etc.
  2. Resource Profile SQL.  This script defines the resources profile(s) such as type of work, hours per shift for each day of the week, hours per operation for each day of the week, etc.
  3. Pools SQL  This script defines the Pools that are to be imported.
  4. Resource Pools SQL.  This script defines resource/pool relationships which are defined by giving the resource name and the pool name as well as a flag to optionally delete an existing resource/pool relationship.


Resource Header SQL


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. Resource Header SQL Script. Enter the SQL script to extract the necessary resource 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.


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.



Resource Profile SQL


The resource profile SQL tab is used to define the resources profile (working hours) to DMS.  The tab has one entry and that is for the resource profile’s SQL fields.  The screen below is an example of the Resource Profile SQL tab after the  button has been pressed and the SQL has been created:





Pools SQL


The Pools SQL tab is used to define what Pools you want to add to DMS.  To remove a pool from being used in DMS, set the Available flag to false.  The screen example below shows the Pools SQL tab after the  button has been pressed:




Resource Pools SQL


The Resource Pools SQL tab is used to specify a resource/pool relationship.  Both the resource and the pool must already exist in DMS.


The screen example below shows the Resource Pools SQL tab after the  button has been pressed:



If DeletePool is true, the corresponding resource/pool relationship is deleted if it exists.  If adding a new resource/pool relationship, DeletePool should be set to false.