The Interface Setup Wizard provides a means of configuring and testing an interface to your business system database(s). You can define database connections and specify the table and field mappings between an external system and PlanetTogether using the Wizard. You can also extend the flexibility of the Interface by using a custom-programmed Customizer DLL.
- To launch the Interface Setup Wizard, go to the Settings | Import Mappings tab.
Note: The APS Interface Service must be running and accessible in order to run the Interface Setup Wizard otherwise an error will be displayed when you attempt to run the Wizard. Only one user can run the Interface Wizard at a time.
Interface Setup Wizard Screens
These are some sample screenshots from the Interface Setup Wizard. These settings could be used to import from the APS.net SQL Database and are contained in the file APSInterfaceSettings for PT SQL database.xml. (This file is installed along with the Interface program files.)
Note: These windows have an Object Help button that will bring you to the appropriate import mapping article. A complete list of the relevant object and field mappings required for import can be found here.
The Interface Setup Wizard will guide you through these steps:
- Select which types of objects to import.
- Setup the "mappings" between your business system database(s) and PlanetTogether's objects.
- Test the interface to ensure the configuration is working.
The Welcome Screen presents the option of using a Custom Interface instead of importing from a database. Note: This option requires creating a custom program file named CustomInterface.dll in the ProgramFiles folder.
Note: All changes made in the Interface Setup Wizard will be saved if the "Save Automatically" option is enabled when moving from one page to another.
Resource Objects To Include
Choose which types of resource information to import. Unchecked mappings will not import or delete related objects.
Inventory Objects to Include
Choose which types of Inventory information to import. Unchecked mappings will not import or delete related objects.
Job Objects To Include
Chose which types of Job information to import. Usually, the options included are Jobs, Manufacturing Orders, Operations, and Activities. Note: Using the Operations.AutoCreateRequirements and AutoCreatedCapabilityExternalId options create the Resource Requirements and Capabilities.
Note: The items that cannot be unchecked are object types that are required and cannot be manually entered.
Note: Before being able to map your data to PlanetTogether fields, you have to set up a database connection using the Instance Manager.
(Optional) Set up an Interface Customizer
This can be used when more flexibility is required. Note: This is a more advanced option that is not usually used unless the database does not support the necessary SQL functions to manipulate the data as needed.
Interface Mapping Dialog
This dialog is used to specify the mappings between PlanetTogether Properties and the fields of an external database. It is these mappings that the Interface Service uses to retrieve data from the database to populate PlanetTogether. Each type of PlanetTogether object has one or more mappings screens.
The properties of each mapping are:
- Select FROM: If the Selected Table list is empty or you need to query more than one table to get the fields for the mappings, then you can enter a SQL Select FROM expression here. Some simple FROM expressions are listed below. Note that the "FROM" itself should NOT be included.
- Workorder_Table INNER JOIN Operation_Table ON Workorder_Table.Id = Operation_Table.WoId
- Filter (SQL WHERE Clause): A WHERE expression can be specified here to limit the records that are returned from the database. For example, this can be used to filter out completed jobs so that they do not appear in the schedule. This is also useful when testing to reduce the size of the dataset returned, thus speeding up the tests. This WHERE Clause is applied to the actual import, the Browse Source Data function, and the browse of field data. Some simple WHERE expressions are listed below. Note that the "WHERE" itself should NOT be included.
- Workorder_Table.Complete=False AND Workorder_Table.DueDate>1/1/2004
- Browse Source Data: This displays the result set of selecting all fields in the Selected Table or FROM Expression, filtered by the WHERE Clause. This can be useful when trying to determine which fields you would like to use or to test your FROM or WHERE expressions.
- Mappings for PlanetTogether: This table is where you specify the fields and/or expressions to be used to fill the PlanetTogether Properties:
- Property: The name of the property that will be filled with the data specified in the "Source" column.
- Type: The type of data that is expected by PlanetTogether for that Property. If possible, PlanetTogether will convert the data provided to the target type. However, this is not always possible so errors can sometimes result if the returned data is an invalid type.
- Required: Properties that are marked as Required must have their Source column filled in or else an error will result. The other values can be left blank and default values will be used.
- Source: This is where you specify the value, usually a field name from your database system, to use for the corresponding PlanetTogether Property. This value can be any valid SQL expression that returns a value compatible with the Type of the Property. For example, it can be a hard-coded value such as 100or a field name such as WorkorderQty. To specify a hard-coded text value, surround it with single quotes such as 'New York'. You can drag and drop field names from the Database fields for __ Field column here. You can also use SQL Functions to convert or calculate values.
- Source Test Button: The button in the Source column cells allows you to test your Source expression. The test uses the Selected Table or FROM expression, the WHERE clause, and the Source expression to query the Connection. The results are displayed in a popup dialog for browsing.
- Refresh Field List and Sample Data: Populates the Database fields for __ grid with a list of all of the fields returned by the Selected Table or FROM expression. This also loads up to the first 100 records to be used as sample data.
- Next Sample: Each click of this button advances one record through the (up to 100) records loaded by Refresh Field List and Sample Data. This is intended to help you in determining which database fields to use for which APS.net Properties.
- Database fields for ____: This grid is populated with a field list when the Refresh Field List and Sample Data button is clicked. Note: If you position your mouse over various columns, a tooltip will provide context-sensitive help information. Additional help information can be accessed by clicking the "Object Help" button found in the lower-left corner of the screen.
- Field: This column contains the names of all of the fields returned by the FROM expression or Selected Table. Note that these Field names can be dragged and dropped into the Source column in the mapping grid.
- Type: The type of data that is stored in this field. If the data type here differs from the Type of the APS.net Property, you can use a SQL CAST or CONVERT function to convert it to the target type. For more information about SQL functions see Note.
- Sample: These are actual values out of the database. When the Refresh Field List and Sample Data button is pressed, it loads up to the first 100 records to be used as samples. You can scroll through the samples using the Next Sample button.
- Test: This performs the same action that will be performed during an actual run of the interface except no data is sent to PlanetTogether. Note that this test may use values that are not on the current screen but instead are on other mapping screens. For example, when you press Test on any of the Job-related screens (Job, ManufacturingOrder, Operation, etc.) then all of these Job-related settings are being tested, not just the screen you happen to be looking at. If you will be querying a large number of records then you may want to place a more restrictive filter on the test by temporarily modifying the WHERE clause. For example, you could specify a maximum job number to include.
- Perform Import: This performs the same action as the Test function above, but also sends the data to PlanetTogether.