The Header tab under Documents is where you will enter one or more joined SQL statements and the fields that make up the document at the header level. The header represents fields that occur once per document. For example, if the document is an Order or Invoice, these are fields such as PO Number, Ship To Address, Ship Via, etc.
First, enter one or more SQL statements in order to retrieve the Document Header fields. One of these must be the Key Provider, which is run first and determines the number of rows returned. The SQL statements on the Header tab can use the following input parameters :
The Document Key field displays in the Fields list on the Header tab by default. This field is used to store the “key” field data that identifies the record for the document type, when a user selects the record in the StarShip browse window, scans it from barcode, or types it in. If the user types it into the field, then padding will be applied as defined in Document Key Padding.
Note :
In some databases the field you will choose for the Document Key may be a numeric data type (e.g. Integer). When setting up your SQL condition statement:
WHERE O.OrderID = {Header.Document Key}
The default field type setting “IsString” will result in quotes being used around the field value in the resulting SQL clause.
WHERE O.OrderID = “345048”
If you prefer the value to be tested as a number instead of a string (no quotes in clause), you will need to choose: “IsNumeric” for field type.
For each SQL statement, add fields to retrieve, so that they are available to be used by other Header queries, document queries, and be mapped by default or by the user to StarShip fields. 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.
See Joins
The Demo 1 interface > Documents > Orders has 2 SQLs for the Header : order_header and ship_addr. One of the fields created by order_header is ShipAddressID. This field is then used by the ship_addr SQL to determine the address to use for shipping. You can see that order_header SQL must be executed before the ship_addr SQL, and is therefore set as the Key Provider. For a more detailed description of this example, see IF/THEN/ELSE.
When one SQL has been identified as the Key Provider, any additional SQLs must set a join field. 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. See Joins.
This is our first SQL statement, for order_header :
This is our second SQL statement, for ship_addr :
Note that ‘DocId’ is linked to ‘Order Number’ Field in Field List :
The next screen shows the results of the join :
To add padding characters, select Document Key from the Fields List on the Header tab.
Padding Char : Enter the padding character (typically '0' or space).
PaddingToLength : Total number of characters the document key should be after padding.
Example
If Document Key = 123456 and is stored in database table as = 00123456
"Padding Char" value would be = 0
"PaddingToLength" value would be = 8