Adding External Data Sources

Follow the steps below to add external data sources.

From the SmartSolve Administrator Tools tab, select Utility > Configure Data Sources.
Result: Initially no data source is defined
Click the Add button to display a blank row.
Enter information into the following fields:

Work Area Fields	Description
Export	See Exporting External Data Sources.
Browse	Click this button to look for a file to export or import.
Import	See Importing External Data Sources.
Source	Enter a unique value for the name of the external data source. NOTE: This data source name is used in Form Configurator or Forms Designer. NOTE: Since this value will be used during forms configuration the name should be meaningful.
Description	Enter a description of the external data source. NOTE: This description should be meaningful to the system administrator.
Connection String	Enter the connection information for the ODBC and database. NOTE: The sonnection string syntax will vary based on the database type. This information must match the ODBC System DSN information you defined on the SmartSolve server. Example MSSQL Data Source DSN=ODBCDataSourceName; UID=DatabaseUserID; Database=ExternalDatabaseName; PWD=DatabasePassword Example: DSN=QMIS490MSQ;UID=QMIS;Database=QMIS490;PWD=pilgrim MS SQL Database Syntax » enter values for the following properties DSN, UID, DATABASE, PWD Oracle Database Syntax » enter values for the following properties DSN, UID, PWD
SQL Query Statement	Enter a valid SQL statement. NOTES: The syntax will vary depending on the database type. We do not provide support on the syntax. When you zoom from a field the system displays the data specified in the SQL statement. Consider performance issues when defining the queries. Avoid using “Select * from..” statements. “Select * from ACT_ACTOR” will display all the columns in the table. Consider filtering the results by using “where” clause.Select ACT_FIRST_NAME, ACT_LAST_NAME from ACT_ACTOR where ACT_LAST_NAME = ‘SMITH’ Use alias column names (user defined names to represent the raw column names) in the SQL statement to present the data in a more user friendly view. See examples below. Examples of SQL Query Statement with alias column names: MS SQL Statement Example: Select PF_NO ‘QMIS Part Number’, PF_NAME ‘QMIS Part Family’ from PF_PART_FAMILY “QMIS Part Number” is the alias name for PF_NO. “QMIS Part Family” is the alias name for PF_NAME. Oracle Statement Example: select PF_NO PartNumber, PF_NAME PartName from PF_PART_FAMILY “PartNumber” is the alias name for PF_NO. “PartFamily” is the alias name for PF_NAME.
Title	Any user defined name. the name is used as the page title of the zoom page (look up page).
Column to Return	This is the column name. Its value will be stored in SmartSolve. NOTES: If the SQL query statement contains an alias column name then you must enter the alias name. Multiple column names can be defined. If entering multiple columns, separate with a semicolon (e.g., Part Name;Lot Number;Unit Cost)

Click the Save button.