External Database Display SQL Scripts
 		Previous Topic  Next Topic 

After defining a connection string to the external database, the SQL required for the display needs to be defined.  This includes the Data Dictionary (columns) you want to display in DMS as well as optional Comparison Information to display certain columns in selected colors if the comparison is true. 


To define the SQL script, select Tables | External Database Display | External Database Display SQL Scripts.  The following screen appear:



Valid commands are:


  1. New To add a new SQL script, select File | New or click on the new icon .
  2. Edit To edit an existing SQL script, highlight the SQL script name in the grid and select File | Edit or click on the edit icon .
  3. Delete  To delete an existing SQL script, highlight the SQL script name in the grid and select File | Delete or click on the delete icon .  DMS will confirm the delete.  Click OK to delete the SQL script, or click Cancel to cancel the delete.


There are three tabs to define an External Database Display SQL Script.  They are:


  1. External Database Display Header
  2. Data Dictionary
  3. Comparison Information


Each of these tabs are explained below.


External Database Display Header



To define the External Database Display header, enter:


  1. Script Name.  The name must be unique and is used to identify the interface to the external database.
  2. Connection Name.  The connection string name that you have previously defined to connect to the database.
  3. Order Number Column Name.  This is the Order Number or MO column name in the external database that you are connecting to.  It is used by DMS to identify what information to display for a given job.
  4. SQL “From” clause.  Enter the SQL From clause that defines what external tables/views you are extracting the information from.
  5. SQL “Where” clause.  Enter the Where clause (if required) for the SQL script.
  6. SQL “Order By” clause.  Enter the Order By clause (if required) for the SQL script.
  7. Once the header information has been entered, click the Save button to save the header information.  Click the Cancel button to ignore the changes.
  8. After you have saved the header, you can press the Test SQL button to test the information you have entered and verify that it is correct.


The example below shows the header screen with data entered.



Data Dictionary


After the External Database Display header has been defined and saved, you enter the data dictionary (columns) of information.  The columns entered to define the data dictionary are the columns you want the user to have the option to display.  The screen example below shows the Data Dictionary tab:



To define the Data Dictionary, enter the following:


  1. Column Name.  Enter the column name of the data defined in the external table or view.
  2. Column Type.  Enter the column type.  Valid column types are String, Integer, Boolean, Float, Currency, BCD, DateTime, Memo, LargeInt, or Variant.
  3. Length.  If a String column type was entered, DMS will prompt you for the length of the string.
  4. Display Title.  The Display Title is the title that will appear on the grid for this column when it is displayed in DMS.
  5. Foreground Color.  You can a foreground color for this column’s data when it is displayed in DMS.  The default is black.
  6. Background Color.  You can a background color for this column’s data when it is displayed in DMS.  The default is white.
  7. Repeat steps 1 through 6 until all the external database columns you want to display in DMS have been entered. 
  8. Once all the columns have been entered, click the Save button to save the Data Dictionary information.  Click Cancel to ignore the entries made.
  9. After you have saved the Data Dictionary, you can press the Test SQL button to test the information you have entered and verify that it is correct.


The example below shows the Data Dictionary screen with data entered.  In this example, five columns of information have been defined.  Two of the columns (with the display names of Work Center and Job Due Date) have different foreground and background colors other than black on white:



Comparison Information


After the header and Data Dictionary have been defined, you can optionally add comparison information that will display columns in different color if the statement is true.  This step enables you to flag certain columns of information if a certain condition exists.  Note that if the Comparison Information is true, the Comparison Information colors will override the data dictionary colors.  Currently, one Comparison Information per external database display can be defined.


The screen example below shows the Comparison Information tab:



To define the Comparison Information, enter the following:


  1. If this Column Name.  Enter the first column name you want to compare.  The column name must be in the Data Dictionary.
  2. Is (select comparison).  Enter the selection comparison of either <, <=, =, =>, or >.
  3. Than this Column Name.  Enter the second column name you want to compare the first column name to.  The column name must be in the Data Dictionary.
  4. Then display foreground color.  Enter the foreground color to display the column in if the statement is true.
  5. And display background color.  Enter the background color to display the column in if the statement is true.
  6. Press the Save button to save the Comparison Information.  Press Cancel to ignore the input.
  7. To test the Comparison Information, press the Test SQL button.

The example screen below shows the Comparison Information with data.  In this example, if the column name JobDueDate is < the column name EndDate, display the two columns with a red foreground and a yellow background