button to enter the SQL query.
The interface consists of SQL statements that are defined at the company level or as a part of each document supported. The company fields are global to all documents. The document fields are used to retrieve a document and update it with shipping information.
This field is a drop down list of all of the actual database Fields from the Select SQL's defined in the SQL List. Select the field from this list that you are linking the Field list field to. Now the field name that will shows in StarShip Customize Interface will contain the value from the database field it is linked to.
Each SQL statement is entered with the following attributes:
Here you can view, add, or delete SQL statements.
This section defines the properties of and the fields that make up the SQL statement.
Enter a unique ID for this SQL statement.
Select the Named Connection that this SQL will use to get company information from the database.
When the SQL is run, StarShip will know which connection string to use for this SQL based on the Named Connection. If there is only one Named Connection, it will be the default setting when identifying connections for each SQL.
This select statement identifies which columns need to be returned to StarShip. The query is validated when you press Enter or press OK on the SQLBuilder dialog.
Click the button to enter the SQL query.
Note : Wild card characters (*) should not be used in the SELECT query, as all fields need to be defined in order to view them in StarShip.
By checking this SQL as the Key Provider, you are defining this query as the statement that will be executed first. This initial data is returned first so that the results can be used by other SQL statements.
If adding additional SQLs that are not the Key Provider, you must set a join field if the primary or current SQL will return more than one row. This is a field that is common to both SQLs. The field may have different names; the only importance is that the data is the same. Essentially, this field selection should be the same as the field you would use if you were using only one SQL and using SQL join statements.
Note : If both SQL statements will only return one row, you do not need a join field. Because this cannot be validated at design time, you should know if a particular query could ever return more than one row. An exception will be returned if a join field is not set up and one of the related SQL's returns multiple rows.
Join Field ID gets its values from the SQL Field List. The field you specify for Join Field ID should be a Select field from another SQL that you want to join to the current SQL. It does not have to be the key provider, but the SQL you are joining to has to eventually itself be joined to the Is Key Provider SQL.
By SQL Field Name gets its values from the Select defined in the SQL Select statement.
See Joins for more information.
Notes:
There are two types of internal fields that can be used to reference fields from other SQL statements : Select and Write Back. The SQL Interface Builder supplies you with pre-defined default internal fields that cannot be deleted. You cannot, for the most part, change the properties of these fields. "Select" type fields do allow you to specify the field type and the padding, but no other properties. As for" Write Back" type fields, because the data is being written back from the financial system, field properties cannot be altered.
This type of field defines what data is being sent TO the StarShip shipment. You are selecting the fields and information from the ERP interface to import into the StarShip shipment.
These are the pre-defined internal fields for "Select" :
{Company.Company Name}, on the Company Fields tab. Its value is determined from the Company that is set up in the StarShip client (or the Interface Builder) when an order number is selected. Unlike {Header.Document Key} it is not defined by any SQL or mapping.
{Header.Document Key}, which appears on the Header tab but is actually defined on the Browser tab. This internal field must be set up to point to one of the fields within the “Is Key Provider” SQL query. {Header.Document Key} will get its value from the Document Key of the document requested by the user (either entered in the document key edit box or by selecting a row when browsing).
An example of using an internal field in a SQL query is using {Company.Company Name} in a SQL on the Company Fields tab, for example, to uniquely identify the company.
Write-Back fields are fields that are dependent upon and generated from the result of processing the shipment and can then be returned to the user's ERP interface. For example, freight cost, package information, or tracking number.
For more information, see Write-Back.
User-Defined Internal Fields
You can also define your own internal fields by adding them to the Fields List. Then, in the SQL statements, enclose the field with curly brackets and use the header.name or wbsourcedocument.name construct. When you type the open curly bracket, all available internal fields and conditional constructs are automatically listed in a drop-down field. Only the fields available in this list are valid for the current selection.
You can see what the Internal Field Name will be for a specific field by looking at the "Internal Field Name:" property of that field.
Any user-defined field within the SQL query can be referenced in other queries by its internal field name.
For example :
{Header.City} would be the internal field name for City where City appears in the Field ID for that field on the Header tab.
{LineItems.Quantity} would be the internal field name for Quantity where Quantity appears in the Field ID for that field on the Line Items tab.
Start from the top left by adding a SQL Title to the list......and using the definitions above, add your SQL statements to the SQL List, and define the SQL Details.
Then, add fields to the Fields List in order to make them available in StarShip. For each field, add the Field Details to the right. See Define Fields for more information about setting up fields.
