Open topic with navigation
Use the Database Setup dialog to configure a database connection for your . You can set up the database connection for several uses in BarTender, depending on the requirements of your document, as follows:
-
Set up a database connection for the document. When you do this, BarTender reads records from the databases and sources the data into the appropriately configured objects on the at print time.
To open the Database Setup dialog for a document
Use one of the following methods:
-
On the File menu, click Database Connection Setup.
-
On the main toolbar, click the 
icon.
-
In the Data Sources pane of the Toolbox, open the for Database Fields, and then click Database Connection Setup.
-
Set up a database connection for a . The dropdown list, list box, and radio button list support a database connection. When these controls are connected to a database, they can display values from the database that the print operator can use to complete the .
To open the Database Setup dialog for a data entry control
-
On the data entry , double-click the control that you want to create a database connection for. The Control Properties dialog opens.
-
In the navigation pane, click List Items.
-
In the Source list, select Database.
-
Click Database Setup.
-
Set up a database connection for a document or form action. You can add database actions to document and form events and to the Control Clicked event of the button control and the hyperlink text object. For more information about the available database actions, refer to Database Actions.
To open the Database Setup dialog for a document or form action
-
On the document or form, open the Actions page, click the action that you want, and then click Database Connection Setup on the action property page. For more information, refer to Using Document and Form Actions.
|
|
If you have not previously connected to a database, these procedures start the Database Setup wizard. Complete the wizard to display the Database Setup dialog.
|
The database connection appears in the left navigation pane of the Database Setup dialog. When your connection includes more than one database, each database is listed in the navigation pane. Beneath each database name are properties that are specific to the database, such as tables and fields, custom Structured Query Language () statements, filter criteria, and so on. The following property pages may be available.
The Multi-Database Join property page is available after you create a second database connection for the document.
If you want to use multiple databases, you must join them by identifying the relationship between them. Use the Multi-Database Join property page to specify these relationships by using database joins. A database join (or join) tells BarTender how data in multiple tables or databases is related. Typically, this means identifying a field that exists in both databases. For more information, refer to About Table and Database Joins.
|
|
Multi-database joins are not supported for database connections that are used by database-related document or form actions.
|
To define a multi-database join
-
Click the Join selector, and then select the type of join that you want to use. For more information, refer to Database Join Types.
-
Click [Left Operand], and then select the database field that you want from your left table.
-
Click the Operator, and then select the operator that you want to use. An operator determines the relationship between the two tables.
-
Click [Right Operand], and then select the database field that you want to use from your right table.
-
Optionally, to add another join, click the 
icon, and then repeat steps 1-4 for the new join condition.
The Connection Properties property page displays information about the database connection. To access this property page, click the database connection name in the left navigation pane.
The options that are on this page vary depending on the type of database connection that you configure. The following options are available.
On the Connection Properties property page for a database connection, you can view the details about the connection, such as the database type or the file, database, or server name. You can also configure the database connection and create a named database connection that can be referenced by other documents and applications in the BarTender Suite.
-
File Name: Displays the path and file name for the connected database. You can click Browse to browse to and select a different database file.
-
Configure Connection: Starts the Database Setup wizard, which you can use to update configuration settings for the current database connection or to select and configure a different database file.
-
Create named database connection: Opens the Create Named Database Connection dialog, where you can enter a name for the current connection so that it can be used by other documents and applications in the BarTender Suite.
On the Connection Properties property page for a named database connection, you can configure the named database connection or stop using the named database connection, as follows:
|
|
When you edit the configuration settings, all other documents and connections in other BarTender applications that use the named database connection are affected.
|
For more information about named database connections, refer to Using Named Database Connections.
|
|
You can also view and manage existing named database connections by using . For more information, refer to Named Connections Page in the Administration Console help system.
|
Use the SQL Statement property page to write custom SQL statements for the connected record set.
By default, this property page displays the SQL statement that is generated automatically by the user's settings on the Filter, Tables, and Sort Order property pages of the Database Setup dialog.
For some database types, the Specify a custom SQL statement (Advanced) option is available. This option is intended for advanced users who are experienced in writing their own SQL statements. To create a custom SQL statement, click to select Specify a custom SQL Statement (Advanced), and then enter your SQL statement in the input field.
|
|
SQL is an advanced programming tool that is used by database programmers. If you decide that you want to learn SQL, we recommend that you obtain qualified SQL instruction or study with an SQL programming book.
|
The Tables property page displays a Tables list where you can view the tables that are available for use in your document. Click in the Alias column for a listed table to specify an alias, or alternate name, for the table. An alias lets you refer to a specific table, even if the document is set up to reference it by a different name. When you assign an alias for the table name, all references to the table are maintained. For more information, refer to Assigning Aliases to Tables and Database Fields.
If your database connection includes two or more tables, you must join them by identifying the relationship between them. Use the Join Conditions section to specify these relationships by using database joins. A database join (or join) tells BarTender how data in multiple tables or databases is related. Typically, this means identifying a field that exists in both tables. For more information, refer to About Table and Database Joins.
The Fields property page displays all of the database fields that are present in the selected tables of your database together with detailed information about the and the length of the data that is contained in that field. Click in the Alias column for a listed field to specify an alias, or alternate name, for the database field. An alias lets you refer to the database field even if the document is set up to reference it by a different name. When you assign an alias for the field name, all references to the old field are maintained. For more information, refer to Assigning Aliases to Tables and Database Fields.
Use the Sort Order property page to sort the records by one or more fields in your database. To select a field to sort by, move it from the Available Fields column to the Fields to Sort By column by dragging or double-clicking the field name. When you add a field to the Fields to Sort By column, you can choose to sort in ascending (1-9, A-Z) or descending (9-1, Z-A) order.
You may want to sort your records by more than one field. When you do this, any records that have identical data in the first field are sorted by the second field. For example, you may have two fields in your database: Department and Employee. You can first sort by Department, so that all employees of the same department are grouped together. Then, you can sort by Employee to place the names in alphabetical order within each department.
Use the Filter property page to specify search criteria that determine which records from your database are printed. Filter statements are structured like a sentence that compares a database field to a value, such as "First Name begins with J". In this example, only those records in which the first name begins with a "J" are printed. You can also define , with which you can enter the filter criteria at print time. For more information, refer to Creating a Query Prompt.
|
|
Query prompts are not supported for database connections that are used by database-related document or form actions or when you open the Database Setup dialog from the List Items property page when the list source type is Database.
|
Any filters that you define by using this dialog are applied before you print your items. If you want to view and filter all records at print time, use the record selection tool to provide interactive filtering for the user. We recommend that you filter the records on the Filter property page in the following circumstances:
-
Your filter remains the same for every print job.
-
You want to limit the results that appear in the interactive record picker at print time.
-
You are using a large database, and performance will improve if you filter the records before printing.
For more information about interactive filtering, refer to Filtering Data.
Use the Options property page to set the following row options for the database:
-
Distinct rows only: Specifies that BarTender returns only the first instance of a row if there are duplicate rows in the database. Click to select this option when you do not want duplicate rows to be returned in the result set. Not available for all database types.
-
Limit number of rows to: Specifies the maximum number of rows that you want to be returned. Click to select this option when you want to limit the number of rows that are returned in the result set.
|
|
This feature is not supported for database connections that are used by database-related document or form actions.
|
Use the Records Per Item property page to print multiple database records on a single printed item. This feature is available when you specify the maximum number of records that you want to be printed on each item. With this feature, you can configure the breaks that cause printing to begin on the next item and specify how the records on each item are displayed.
The following options are available:
-
Maximum number of records per item: Specifies the maximum number of records that are returned per item. The maximum value for this option is 99.
|
|
When you indicate the maximum number of records that you want to be printed on each item, the database fields that are included in your database connection appear as numbered data sources in the Database Fields node of the Data Sources toolbox tab.
For example, if you specify a maximum number of 4, each database field that is included in your database connection appears as four separate data sources ( "fieldname", "fieldname [2]", "fieldname [3]", and "fieldname [4]". Each of these field data sources can be linked to a separate object on the template. For more information, refer to Records Per Item Example.
|
-
Break when field changes: Specifies one or more database fields as break points, so that when the value changes for the field, that record and subsequent records continue to be printed on the next printed item.
-
Combine multiple records into single field: Specifies database fields that can display the values of multiple records in a single object on the template.
|
|
The fields that you specify subsequently appear as "fieldname [+]" in the Database Fields node of the Data Sources toolbox tab.
|
-
Fields: Specifies the database fields that contain data that you want to be combined into a single object on your template.
-
Separator: Specifies the type of separator that you want to use for the data that is being combined, as follows:
-
Line Break: Specifies that the returned values are separated by a line break.
-
Tab: Specifies that the returned values are separated by a tab.
-
Custom: Specifies that the returned records are separated by a custom separator. For example, you can separate the values by using a comma followed by a space (, ).
|
|
Each that is defined with the plus sign "[+]" or a number "[n]" is a virtual field, meaning that it is simply a representation of a database field. When one of these virtual fields is linked to an object, the Data Sources property page does not include the options Update record when data source changes and Lock during print job.
|
The Record Browser property page displays the data in the result set. By viewing the records, you can verify that your table joins, filters, and sorting options are correctly specified.
The Record Browser (Joined) property page displays the data that is available from your joined connected databases. By viewing the available records, you can verify that your database joins, filters, and sorting options are correctly specified.
|
|
This property page is not available for database connections that are used by database-related document or form actions.
|
Database Setup Dialog Toolbar
The Database Setup dialog toolbar is at the bottom of the left navigation pane and includes the following icons:
Opens the Database Setup wizard, which you can use to create a new database connection or connect to a database by using an existing named database connection.
Deletes the database connection.
When you click this icon, the No Record Sets page of the Database Setup dialog appears. Click or Add a new record set to create a new database connection or to connect to a database by using an existing named database connection.
Refreshes the database connection.
Refreshing the Database
Sometimes when you design items to be printed, you need to make changes to your database. For example, an database may require changes to the structure of the database or to the configuration of the ODBC driver that is used to read the database. A text or Excel file may need changes to the field names. Any such changes to any database type are automatically detected when a document is opened. However, if changes are made to the database while a document is open, BarTender has no way of automatically detecting these changes.
To refresh the database, click after you perform any function in the Database Setup dialog that might cause important changes. BarTender reloads the structure and configuration of the named ODBC databases and refreshes the field names of any text files.
Related Topics
Multi-Database Join
SQL Statement
Tables
Fields
Sort Order
Filter
Options
Records Per Item
Record Browser