This document contains proprietary information that is protected by copyright and other intellectual
property laws. No part of this document may be reproduced, translated, or modified without the
prior written consent of QAD Inc. The information contained in this document is subject to change
without notice.
QAD Inc. provides this material as is and makes no warranty of any kind, expressed or implied,
including, but not limited to, the implied warranties of merchantability and fitness for a particular
purpose. QAD Inc. shall not be liable for errors contained herein or for incidental or consequential
damages (including lost profits) in connection with the furnishing, performance, or use of this
material whether based on warranty, contract, or other legal theory.
QAD and MFG/PRO are registered trademarks of QAD Inc. The QAD logo is a trademark of QAD
Inc.
Designations used by other companies to distinguish their products are often claimed as
trademarks. In this document, the product names appear in initial capital or all capital letters.
Contact the appropriate companies for more information regarding trademarks and registration.
Copyright © 2013 by QAD Inc.
Excelerator_UG_v0103.pdf/qgl/qgl
QAD Inc.
100 Innovation Place
Santa Barbara, California 93108
Phone (805) 566-6000
http://www.qad.com
Contents
What’s New? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .v
About This Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
What Is in This Guide? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Related Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
QAD QXtend Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Other Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Menu References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Typographic Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
QAD Support Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Chapter 1 QAD QXtend Excelerator Overview. . . . . . . . . . . . . . . . . .7
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Data Integration Process Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Chapter 2 Implementing QAD QXtend Excelerator . . . . . . . . . . . . .11
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Installing Excelerator and Prerequisite Components . . . . . . . . . . . . . . . . . . . . . 12
Setting Up QXtend Outbound Query Service . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Setting Up Authentication for Query QAD Requests . . . . . . . . . . . . . . 14
Configuring Excel Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Configuring Excelerator Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Designing Data Integration Worksheet Templates . . . . . . . . . . . . . . . . . . . . . . . 20
Modifying Worksheet Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Working with CSV Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Creating CSV Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Importing and Exporting CSV Files . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Performing Initial Data Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Performing Data Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Upgrading Excelerator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
iv User Guide — QAD QXtend Excelerator
Chapter 3 Using QAD QXtend Excelerator. . . . . . . . . . . . . . . . . . . .27
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Querying Data from the Source Application . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Troubleshooting Data Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Viewing and Modifying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Changing Workspace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Updating Data to the Target QAD Application . . . . . . . . . . . . . . . . . . . . . . . . . 32
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
What’s New?
The following table summarizes significant differences between this document and the last
published version.
Date/Version Description Reference
July 2013/1.3 Updated information on the Excelerator license page 9
Updated the steps for installing Exceler
ator and prerequisite components page 12
Added Excel 2013 in Configuring Excel Settings page 15
Added new configuration options: Debug Level
and Scope Transaction page 19
Added the use of Export Errors button in the update steps page 33
September 2011/1.2 Modified the introductory text to Excelerator page 8
September 2011/1.2 Updated Excelerator’s prerequisite co
mponents page 12
September 2011/1.2 Included steps to show the Developer tab in Excel 2010 page 15
September 2011/1.2 Added instructions on upg
rading Excelerator page 26
September 2011/1.2 Updated the section on configuring Exc
elerator settings to include new
features such as Enable Export and worksheet template type settings
page 17
September 2011/1.2 Updated the steps for designing data integration
worksheets to include new
worksheet template type settings
page 20
September 2011/1.2 Updated the section on worki
ng with CSV files page 24
September 2011/1.2 Updated the section on troubleshooting data queries page 29
vi User Guide — QAD QXtend Excelerator
2 User Guide — QAD QXtend Excelerator
What Is in This Guide?
Use this guide to configure and use QAD QXtend Excelerator, which is an optional data
integration tool within the QAD QXtend interoperability framework that provides additional data
visibility and manageability to the data integration process between QAD products.
For information about QAD QXtend, see User Guide: QAD QXtend.
Audience
This guide is intended for developers who design data integration worksheets and configure QAD
QXtend Excelerator settings, as well as end users who perform actual data integration activities,
including retrieving data from the source QAD application, editing data in the data integration
worksheet, and synchronizing data with the target QAD application.
The developer should be familiar with the QAD Enterprise Applications and QXtend
configurations. The user should have experience working with Microsoft Excel worksheets.
If you do not have this expertise within your company, contact QAD Support for information on
the customization offerings supplied by QAD’s Global Services.
Related Documentation
QAD QXtend Documentation
QAD QXtend Excelerator is designed to be used in conjunction with QAD QXtend to streamline
the data integration and communication process.
• For instructions on installing QAD QXtend, see Installation Guide: QAD QXtend.
• For information on configuring and using QAD QXtend, see User Guide: QAD QXtend.
Other Documentation
QAD QXtend Excelerator is designed to interoperate with QAD Enterprise Applications.
For information on installing QAD Enterprise Applications or converting to a more recent release,
refer to the appropriate installation guide for your system or the appropriate QAD Enterprise
Applications conversion guide.
For information on using the various modules of QAD Enterprise Applications, refer to the user
guides for your version.
For users with a QAD Web account, product documentation is available for viewing or
downloading from the QAD Online Support Center at:
http://support.qad.com/
About This Guide 3
You can register for a QAD Web account by accessing the Web site. Your customer ID number is
required. Access to certain areas is dependent on the type of agreement you have with QAD.
Most user documentation is available in two formats:
• Portable document format (PDF) files can be downloaded from the QAD Web site to your
computer. You can view and print them with the free Adobe Acrobat Reader.
• HTML files let you view user documentation through your Web browser and use search tools
for easily locating topics of interest.
Features of the Web site include an online solution database to help you answer questions about
setting up and using
the product. Additionally, the QAD Web site has information about training
classes and other services that can help you learn about QXO and QAD Enterprise Applications.
Conventions
Menu References
This guide applies to all releases of QAD Enterprise Applications from eB through to the current
release (QAD EE and QAD SE). Several menus have been reorganized between these releases.
Differences in menu numbers are noted, when necessary, using the following convention:
User Tool Maintenance (36.20.4; 36.20.2 in eB)
The initial menu number identifies the program in
the most recent release. The second menu
number applies to the release specified and all earlier releases.
Typographic Conventions
This document uses the conventions listed in the following table.
If you see: It means:
monospaced text A command, path, or f
ile name.
italicized
monospaced text
A variable name for a value you enter
as part of an operating
system command; for example, YourCDROMDir.
<italicized
monospaced text>
A variable for a system value such as a drive letter or machine
name; for example <hostmachine>.
indented
command line
A long command that you enter as one line, although it
app
ears in the text as two lines.
Note Alerts the reader to exceptions or special
conditions.
Important Alerts the reader to critical information.
Warning Used in situations where you can overwrite
or corrupt data,
unless you follow the instructions.
4 User Guide — QAD QXtend Excelerator
QAD Support Services
To take full advantage of the flexibility and potential of QAD’s interoperability framework in your
specific environment, contact your QAD Support representative for information on the installation
and customization offerings supplied by QAD Support Services. These offerings include
performance enhancements as well as technical and administration training. For details, contact
your nearest QAD office, or go to the QAD Web site and click the Global Services link.
8 User Guide — QAD QXtend Excelerator
Introduction
QAD QXtend Excelerator is an optional data integration tool within the QAD QXtend
interoperability framework that provides additional data visibility and manageability to the data
integration process between QAD products.
Excelerator helps you perform the following data integration tasks more easily and efficiently:
• Perform initial data load to implement QAD Enterprise Applications.
• Perform data migration to upgrade QAD Enterprise Applications.
• Transfer and convert business transaction data from one QAD application to another.
Excelerator is used with QXtend Inbound and Outbound, which must be properly implemented
before you can use this tool.
Seamlessly integrated with Microsoft Excel 2007, 2010, and 2013 as a document-level
customization, Excelerator provides the following functions all in a familiar and intuitive user
interface:
• Designing the worksheet template
• Populating the worksheet with data
• Viewing and modifying data in the worksheet
• Synchronizing data with the target QAD application
You first create an Excelerator data integration worksheet template by downloading and importing
a QDoc (QXtend API schema) from a QXtend Inbound instance. You can further configure the
worksheet to hide tables and columns to simplify data entry.
To prepare data to load into the target QAD application, you can import data into the worksheet
from three data sources: manual data entry, CSV files, and the Query Service in QXtend
Outbound.
You then update data from the Excelerator worksheet to the target QAD application through
QXtend Inbound. You select a QXtend instance and receiver to receive the data and can specify
whether to use SSL to call the QXtend Web service. The system performs authentication when
processing records using the QXtend Web service, and you can stop and restart the load process. If
errors occur during the load process, the tool displayf errors and highlights records with errors.
You can reprocess failed records. This data integration tool supports all QAD products that expose
APIs through QXtend Inbound.
When you save a worksheet, its state is also saved. That is, if you have ten errors when you save
and close the file, these ten errors are still there when you reopen the file.
QAD QXtend Excelerator Overview 9
Fig. 1.1
Excelerator Framework
QDoc
QXtend
Inbound
QXtend
Inbound
Target
QAD Enterprise
Applications DB
QAD QXtend
Excelerator
QAD QXtend
Excelerator
QXtend
Outbound
QXtend
Outbound
Web
Services
Source
QAD Enterprise
Applications DB
CSV file
Query
Import/
Export
Update
Excelerator dovetails into the Microsoft Excel user interface, and you access all of its functions the
same way you use Excel features. The tool’s configuration settings are grouped under the
Developer tab in the Ribbon, and all of its data integration features are grouped under the QAD
Tools tab. You can enable/disable some of its data integration features as needed.
Fig. 1.2
Excelerator Menus
Contact your Sales Representative to get an Excelerator license for initial data load and go-live
implementation. If you need more time than the license validity period for the implementation,
request a new license from your sales representative. If you need further use of Excelerator for
data integration and communication on a day-to-day basis, purchase a separate license.
10 User Guide — QAD QXtend Excelerator
Data Integration Process Map
The following diagram illustrates the basic data integration workflow using Excelerator.
Fig. 1.3
Data Integration Process Map
Install Excelerator
Install Excelerator
Configure
Excel settings
Configure
Excel settings
Configure
Excelerator settings
Configure
Excelerator settings
Design data integration
Spreadsheet
Design data integration
Spreadsheet
Create CSV template
(Optional)
Create CSV template
(Optional)
Configure
Excel settings
Configure
Excel settings
Design data integration
spreadsheet
Design data integration
spreadsheet
Query data from the
source application
Query data from the
source application
Edit data in the
worksheet or CSV file
Edit data in the
worksheet or CSV file
Update data to the
target application
Update data to the
target application
Developer User
Chapter 2
Implementing QAD QXtend
Excelerator
This section provides detailed instructions for developers on setting up Excelerator for data
integration.
Overview 12
Discusses required setup steps
Installing Excelerator and Prerequisite Components 12
Lists prerequisite steps and describes the Excelerator setup and installation procedure
Configuring Excel Settings 15
Lists the steps required to configure Excel settings
Configuring Excelerator Settings 17
Lists the steps required to configure Excelerator settings and discusses the different settings
available
Designing Data Integration Worksheet Templates 20
Lists the steps required to design data integration worksheet templates
Working with CSV Files 24
Explains how to import data from and export data into CVS files
Performing Initial Data Load 24
Explains how to perform the initial data load and discusses how to address potential errors
Performing Data Conversion 25
Lists the steps required to perform data conversion
12 User Guide — QAD QXtend Excelerator
Overview
This section assumes that you are a developer familiar with QXtend configurations. Before
Excelerator can be used to perform data integration and communication between QAD products,
use the following general tasks to set up the tool:
• Installing Excelerator and Prerequisite Components
• Configuring Excel Settings
• Configuring Excelerator Settings
• Designing Data Integration Worksheet Templates
Installing Excelerator and Prerequisite Components
To install Excelerator, make sure that you have administrator privileges. If not, you can ask your
system administrator to install Excelerator into your user account. If multiple users on the system
want to use Excelerator, the system administrator must install Excelerator for each user account
respectively. Installing once on the system cannot enable all users to use Excelerator.
1 Make sure that the following prerequisites are met before you implement Excelerator on your
client machine:
• The operating system on the client machine must be Windows 8, Windows 7, Windows
XP, Windows Server 2003, or Windows Server 2008.
• Microsoft Excel 2007, 2010, or 2013 is installed on the client.
• QXtend Inbound and Outbound (version 1.6.3 or later) have already been properly
installed and configured to successfully communicate data between source and target
QAD applications.
Note If you use Excelerator 1.3 with a QXtend version earlier than 1.8.4 and use a comma as
the decimal separator, contact QAD Customer Support to get a patch.
2 From the installation media, execute setup.exe to install the Excelerator workbook and all
prerequisite components. The components include Microsoft .NET Framework 3.5 SP1,
Microsoft Office Primary Interop Assemblies, and Microsoft Visual Studio 2010 Tools for
Office Runtime. Follow the on-screen instructions to complete the setup.
Important For Excel 2010, if Microsoft Visual Studio Tools for the Microsoft Office System
3.0 is installed on your client machine, uninstall it before you execute
setup.exe and remove
the registry key under
HKEY_CURRENT_USER\Software\Microsoft\VSTO\SolutionMetadata. Otherwise,
you cannot open any Excelerator worksheet.
3 When setup is complete, you can find a QXtendWorkbook.xlsx file in your My
Documents\QAD\QAD QXtend Workbook
folder. This file serves as a template, so do not
directly use this file but rather create new working Excel files from the file for data integration.
A default QXtendWorkbookConfig.xml file is also deployed to the My
Documents\QAD\QAD QXtend Workbook
folder. The file contains the global Excelerator
settings that are set in the QAD QXtend Excelerator Settings window. When the Excelerator is
launched, the configuration details (QXtend Location, Update Receiver, QAD Application
Settings) are loaded from the
QXtendWorkbookConfig.xml file. The tool searches the
following locations in order of preference when loading the configuration:
Implementing QAD QXtend Excelerator 13
• Local directory where the worksheet was opened from.
• The config subdirectory under the local directory where the worksheet was opened from.
• Your ClickOnce cache directory. The ClickOnce cache is where required DLLs are
installed to. The location is a subdirectory under your
Local Settings\Apps\2.0
directory.
• The config directory under the ClickOnce cache.
This mechanism allows you to decide whether to bundle the configuration file with the set of
worksheets you are working with. If the configuration file is not exposed in the local directory
where the worksheets are stored, the settings are stored in your ClickOnce cache. The
administrator can then restrict access to the cache directory, which would restrict a user from
tampering with the QXtend settings.
Setting Up QXtend Outbound Query Service
To use the data query function in Excelerator to retrieve data from the source QAD Enterprise
Applications, first set up the query service and configure QDoc APIs in QXtend Outbound.
Perform the following steps in QXtend Outbound.
Note For details on setting up the query service, modifying query profiles, and configuring and
deploying QDoc APIs, see User Guide: QAD QXtend.
1 Set up the query service.
2 Configure corresponding QDoc APIs.
• If you are working with standard QDoc APIs, some already have corresponding
synchronization profiles defined in QXtend Outbound, so you do not need to do anything
for them. For example, for the maintainCustomer QDoc API, there is a corresponding
profile called MaintainCustomerData already defined in QXtend Outbound.
• If the QDoc API being used does not contain the default profile, define the required profile
in QXtend Outbound.
• Customize profiles as needed. Depending on the QDoc API you are using, in some cases,
further configuring of the profiles for use with Excelerator may be required. For example,
when querying data containing transaction comments, define the foreign key fields in the
transaction comments object. For Analysis Code Master, the following fields have been
added to the Transaction Comments object:
• anCode contains a fixed value of “=$analysisCode.anCode$”
• antype contains a fixed value of “=$analysisCode.antype$”
14 User Guide — QAD QXtend Excelerator
Fig. 2.1
Customizing Profiles
This type of assignment uses the fixed value expressions functionality available in QXtend. In
this example, the value is replaced by the value in the parent record. It is required because the
foreign key definition in the maintainAnalysisCode schema references these values when
linking the child transaction comments data to the parent analysis code record.
Fig. 2.2
Reference Example
The standard replication profiles supplied with QXtend Outbound already include this field
assignment when dealing with Transaction Comments data. When designing profiles for use
with the Excelerator worksheets, it is important to ensure that this type of field assignment is
included in the definition.
3 To expose the query profiles to the worksheet, use the Deploy Query button to publish any
newly created or modified query profile definitions to QXtend Inbound. This allows QXtend
Outbound to accept query requests for the published profiles.
Setting Up Authentication for Query QAD Requests
Query QAD works for all QAD Enterprise Applications versions QAD supports, but Query QAD
requests are only authenticated for 2009 and later releases of QAD EE and QAD SE.
To enable this functionality, configure a connectio
n pool of type SIAPI in QXtend Inbound.
Ensure that the name of the connection pool is the same as the Update Receiver specified in the
Excelerator Settings panel.
For details on configuring connection pools, see Use
r Guide: QAD QXtend.
Implementing QAD QXtend Excelerator 15
Configuring Excel Settings
Use the following to configure Excel settings. Once done, the settings are applied to all the Excel
files on your machine.
1 Create a working Excel file by making a copy of the QXtendWorkbook.xlsx template file
located in the
My Documents\QAD\QXtendWorkbook folder. You can rename the new file
as you want.
2 Open the working Excel file.
3 Show the Developer tab in Excel.
For Excel 2007:
a Click the Microsoft Office Button and then click Excel Options.
b On the Popular tab, select Show Developer tab in the Ribbon under Top options for
working with Excel.
The Developer tab contains configuration options for Excelerator
, so you must display it
to access these settings.
Fig. 2.3
Excel Options—Popular (Excel 2007)
For Excel 2010 and Excel 2013:
a Click the File|Options|Customize Ribbon.
b Under Customize the Ribbon, select the Developer check box.
16 User Guide — QAD QXtend Excelerator
Fig. 2.4
Excel Options—Customize Ribbon (Excel 2010)
Note Display the Developer tab only for the users who are required to access the developer
functions.
4 On the Trust Center tab, click Trust Center Settings.
5 Click Macro Settings, and then select Trust access to the VBA project object model under
Developer Macro Settings.
Fig. 2.5
Excel Options—Trust Center
Implementing QAD QXtend Excelerator 17
Configuring Excelerator Settings
By default, the global Excelerator settings are stored in the QXtendWorkbookConfig.xml file in
the
My Documents\QAD\QAD Excelerator folder.
1 In the Ribbon, click the Developer tab.
2 In the QAD Excel Features group, select the features of Excelerator that you want to turn on:
• Enable Export: Lets you export data from the Excelerator worksheet into CSV files.
• Enable QAD Queries: Lets you query data in QAD applications through the QXtend
Outbound Query Service.
• Enable Data Grid View: Lets you view data in a data grid view.
• Enable Change Workspace: Lets you change the current domain and entity.
3 In the QAD Configuration group, click QXtend Settings.
4 In the QAD QXtend Excelerator Settings screen, set up proper settings for Excelerator to work
with QXtend and QAD applications.
Fig. 2.6
QAD QXtend Excelerator Settings
QAD Application Settings
Domain and Entity. Specify the domain and entity of the QAD Enterprise Application you
want to query data from and load data into.
Mnemonics Raw. specify whether allow QXtend to process the raw value of a mnemonic field
or the mnemonic itself. For example, the EMT Type field in Customer Maintenance is stored
as a mnemonic. The raw value is 01; however, the mnemonic displayed on the screen is
NON-EMT.
Encode Password. Specify whether you want to encode your password for accessing the
target QAD Enterprise Applications. Encode Passwords is used if the QXtend application is
running with <encodedPasswords> set to true. (This value is set in the
qxtendconfig.xml
configuration file in QXtend Inbound.)
18 User Guide — QAD QXtend Excelerator
QXtend Location
Tomcat Host and Tomcat Port. Provide the host and port information for the QXI server.
QXI Instance Name. Specify the Webapp name, which is the name of the Web service used to
connect to QXtend Inbound.
Use SSL. Select this option if using Secure Socket Protocol https encryption. Enabling this is
only applicable if QXtend has been configured to run under SSL.
Request Timeout. Enter, in seconds, the maximum wait time for responses from QXtend
Inbound.
Update Settings
Update Receiver. Specify the QXtend Inbound receiver to use to load data into the target QAD
application.
QDoc Name. Displays the name of the QDoc API the worksheet relates to. The field defaults
from the schema imported into the worksheet.
QDoc Version. Displays the version of the QDoc API the worksheet relates to. The field
defaults from the schema imported into the worksheet.
Menu Program. The menu program the QDoc API is mapped to. (Optional: this information is
used when retrieving menu-specific column labels and authenticating Query requests.)
QAD Release. Specify the QAD release the worksheet is interacting with.
Template Type. Select the default Excelerator worksheet template type to use when creating
worksheet templates.
There are two Excelerator worksheet template types:
• Data load template
It is the system default worksheet template type and used for loading or updating master
data into QAD Enterprise Applications.
When you use this template type, the Process Response QDoc option is cleared by default
since no message processing is required for QXtend responses.
• Business transaction template
Use this template type when you want to create from Excelerator new business transaction
records (for example, sales orders) in QAD Enterprise Applications that require the target
system to generate key field values or business document numbers (for example, sales
order numbers).
This feature is realized by using the key prefix. When processing the updates to QAD, the
temporary key prefix is removed and replaced with the system-generated key (e.g. the
sales order number).
Key Prefix. Specify a key prefix (for example, tmp#) to identify key fields or business
document numbers of business transaction records (for example, sales order numbers of sales
orders) in the worksheet that you want the target QAD application to supply values for. You
can then use the key prefix to create temporary key field values (for example, tmp#1, tmp#2,
tmp#3 ...) for new records in the worksheet. When the data is sent for processing through the
Update QAD function, QXtend replaces the temporary key field values from the requests with
Implementing QAD QXtend Excelerator 19
values generated by the target QAD application. When Excelerator receives responses
containing the system-generated key field values, it updates the worksheet with the new key
field values.
The prefix must and can only be defined when the worksheet template type is Business
Transaction Template.
The value you enter here defaults to
the Key Prefix field in the Design Template dialog box
when you design worksheet templates.
Debug Level. Specify the debug level for financial QDocs only. Refer to Table 2.1 for the
available level options.
Table 2.1
Debug Levels
Level Description
1 Logs the start and end of all entry-level business methods. Entry-level methods
a
re called from outside the business logic.
2 Logs the start and end of all methods and procedures,
internal and external.
4 Logs parameter values of business methods.
This level is only available when
level 1 or level 2 is active.
8 Logs all read or update datebase queries.
16 Logs details of all database update (create, mod
ify, or delete).
32 Analyzes performance and logs possible me
mory leaks in the business code.
Scope Transaction. Select this option if you want each update from Excelerator to be treated
as a whole transaction in QAD. If any error is encountered, the entire update will be backed
out.
Process Response QDoc. Specify whether you want Excelerator to process response QDocs
returned from QXtend and update the worksheet with any new data.
Yes (Selected): Excelerator processes response QDocs containing de
tailed business data (the
suppressResponseDetails attribute is set to false) returned from QXtend. Any new data in the
response messages will be updated into the worksheet.
No (Cleared): The Excelerator ignores
any response QDocs returned from QXtend. The
suppressResponseDetails attribute is set to true in the request sent to QXtend Inbound, which
limits the data contained in the response to processing status and exception messages,
excluding any business data to enhance the message processing performance of QXtend
Inbound. You should set the option to No for data load templates.
You can only specify this option for data load templates. For
business transaction templates,
the option is always set to Yes and cannot be changed.
Query Settings
Query Receiver. Specify the receiver to use to query data from the source QAD application.
The receiver must be a receiver listed under the Outbound module receivers defined in
QXtend Inbound.
Source Application. Specify the source application instance name.
Profile. Specify the name of the profile in QXtend Outbound that defines the format of the
queried data.
20 User Guide — QAD QXtend Excelerator
Note The format must be compatible with the request XSD that the worksheet was built
from. This is important because the format of the query response data must map to the table
and field names defined in the worksheet. The table name and field names are case-sensitive.
QDoc Version. Specify the QDoc version.
Max Rows. Enter the default maximum number of records to retrieve into a worksheet during
data query. Enter zero (0) to retrieve all matching rows.
Filter. Enter a valid Progress query to use as filter criteria to filter records. The filter must be a
valid Progress ABL WHERE clause and can only be defined against the top-level table of the
QXO business object; for example:
cm_site = 'SITE1' and (cm_addr >= '0100' and cm_addr <= '100')
Designing Data Integration Worksheet Templates
1 Under the Developer tab in the Ribbon, click Design Template. The pop-up window displays
worksheet details settings.
Fig. 2.7
Design Template
2 In the QAD Release field, select the version of the source and target QAD Enterprise
Applications.
The Query QDoc name and version in the screen map to a corresponding profile defined in
Q
Xtend Outbound. For example, a worksheet has been designed to work with the Customer
Data QDoc. It means that the maintainCustomerData QDoc API has been imported to the
worksheet. In QXtend Outbound, there is a corresponding profile defined called
MaintainCustomerData.
Fig. 2.8
Profile in QXO
Implementing QAD QXtend Excelerator 21
After the MaintainCustomerData profile has been deployed as a query to QXtend Inbound, it
is available for use with the worksheet.
The version of the query QDoc depends on what QAD release has been selected, as follows:
QAD Enterprise
Applications Release Default Version
QAD EE ERP2_1
QAD SE eB21_1
eB2.1 eB21_1
eB2 eB2_1
eB eB_1
The versions for QAD EE QDocs are displayed in QXtend Inbound as follows:
Fig. 2.9
QAD EE QDoc Versions
3 In the Display Tables On field, specify whether you want to display all data in a single
worksheet or create a separate worksheet for each table.
Note When custom add-ins are deployed in your Excel, the application may become unstable
and unresponsive when the number of worksheets in the file exceeds three. When this situation
occurs, use the Single Worksheet option or uninstall the custom add-ins.
4 From the Template Type list, select a worksheet template type. This defaults from the QAD
QXtend Excelerator settings. See "Template Type" on page 18.
If you select the business transaction template type, specify the Ke
y Prefix value and the
Process Response QDoc option. These default from the QAD QXtend Excelerator settings.
See "Key Prefix" on page 18 and "Process Response QDoc" on page 19.
5 Click Load QDoc Schema, and then locate a QDoc file to load into the worksheet.
Only QDoc 1.1 syntax schemas are compatible with
Excelerator.
The schemas are located in the
WEB-INF/schemas directory of the QXtend Inbound
application. To identify the correct version of the schema to import, view the schema
information displayed in the Schema View in Configuration Manager in QXtend Inbound.
For example, for the QAD EE maintainCustomerData QDoc, the
Schema View shows that its
version number is ERP3_2, so you need to retrieve
maintainCustomerData-ERP3_2.xsd
from the
TOMCAT_HOME/webapps/<QXI webapp>/WEB-INF/schemas/QADEE directory.
22 User Guide — QAD QXtend Excelerator
Fig. 2.10
Schema View
The tool processes the QDoc schema, imports all the tables and fields into the worksheet, and
automatically fills the appropriate values into the Worksheet Name, Update QDoc, and Query
QDoc fields based on the QDoc data.
The Menu Program Name field displays the menu program
the QDoc API relates to. This
information is also used when retrieving menu-specific column labels and authenticating
Query QAD requests (QAD SE and QAD EE only).
6 Optionally, click Configure Worksheet to customize the schema in the worksheet. In the Select
Tables and Fields window, select the tables and fields you want in the worksheet. You can use
the Filter to quick search for fields.The tool automatically select table primary keys.
Note Column labels are only retrieved for 2009 and later releases of QAD EE and QAD SE.
To enable this functionality, configure a connection
pool of type SIAPI in QXtend Outbound.
The name of the connection pool must be same as the Update Receiver specified in the
Excelerator Settings panel.
For details on configuring connection pools,
see User Guide: QAD QXtend.
Implementing QAD QXtend Excelerator 23
Fig. 2.11
Select Tables and Fields
7 Click Create Worksheet. If you chose to create a single worksheet, the tool creates a worksheet
with tables and fields from the schema translated into rows and columns. If you chose to create
multiple worksheets, the tool creates a separate worksheet for each table from the schema.
Primary keys in tables are marked with an asterisk (*). In the worksheet, operation and index
fields are displayed in the same sequence as in the QDoc; all other fields are sorted in
alphabetical order.
Fig. 2.12
Worksheet with Schema
8 Save the Excel file. You can now use the blank worksheets for entering data, querying data
from the QAD application, and updating data to the QAD application.
Modifying Worksheet Templates
You can use Configure Worksheet to modify the schema structure in the worksheet template you
have already created. So, if you forgot to include a field, you can simply go back to Configure
Worksheet to select the field you missed.
24 User Guide — QAD QXtend Excelerator
However, if you have already entered data in the worksheet, when you modify the template design
and update the worksheet, the system prompts you to retain existing data in the worksheet. If you
choose to retain the existing data, all the rows in the worksheet are marked as Processed and you
must use Update QAD|Process All later to update data to the target QAD application.
Working with CSV Files
Creating CSV Templates
After you design the data integration worksheet, you can create a CSV template from the
worksheet. You can then populate the CSV file with data from QAD or third-party applications
either manually or programmatically. You can import the CSV file into the Excel worksheet for
data synchronization with the target QAD application.
To create a CSV template:
1 Under the Developer tab in the Ribbon, click Create CSV Template.
2 Select one of the following:
• Single CSV Template: creates one CSV file for all the tables in the worksheet.
• Template Per Table: creates multiple CSV files, one for each table in the worksheet.
3 Specify a location to save the file.
Importing and Exporting CSV Files
Under QAD Tools, use the Import and Export functions to use CSV files for data entry.
You can import the CSV file—either a single CSV file containing all the tables or separate CSV
files each containing one table—into the Excelerator worksheet.
Using the CSV export tool, you can export data from the Excelerator worksheet into a single CSV
file or multiple CSV files, one for each table. The structure of the exported CSV file strictly
follows that defined in the imported CSV file.
Performing Initial Data Load
For go-live implementation of QAD Enterprise Applications, load some initial data into the system
to quickly get it up and running. Use the following general steps to perform the initial data load
using Excelerator:
1 Design a set of data integration worksheets for the initial data load.
2 Use the CSV template creation function to generate CSV templates that outline the expected
formats of CSV files for use with the worksheets.
3 Use the CSV templates to convert data from a legacy system into the structure defined in the
templates and load it into the worksheets. You populate data in the CSV templates either
programmatically or through data output from the legacy system. The format of the CSV data
must match the CSV template.
Implementing QAD QXtend Excelerator 25
4 Use the Import From CSV function to load the data from the CSV files into the worksheets.
Any errors that occur during CSV file import are captured and displayed in the grid view.
Correct these errors before you can proceed with data import.
Fig. 2.13
Data Import Errors
5 If necessary, modify the imported data in the worksheets.
6 Use the Update QAD function to load the data from the worksheets into target QAD Enterprise
Applications.
Performing Data Conversion
Use the following general steps to perform data conversion when you upgrade your QAD
Enterprise Applications:
1 Design a set of data integration worksheets for the data conversion.
2 Use the Query QAD function to query data from the source QAD Enterprise Applications into
the worksheets.
3 Modify the data in the worksheets as required.
4 Use the Change Workspace function to switch to the domain and entity of the target QAD
Enterprise Applications.
5 Use the Update QAD function to load the data from the worksheets into target QAD Enterprise
Applications.
26 User Guide — QAD QXtend Excelerator
Upgrading Excelerator
If you already have an earlier version of Excelerator installed, perform these simple steps to
upgrade it to the latest version:
1 Uninstall the previous version of Excelerator using either one of the following methods:
• Run setup.exe from the previous version of Excelerator and then choose the Uninstall
option.
• Uninstall programs QAD QXtend Workbook and QXtendWorkbook using the Windows
control panel.
2 Run setup.exe from the latest version of Excelerator to install the new program. Worksheets
created using earlier versions of Excelerator are compatible with the new version.
Chapter 3
Using QAD QXtend Excelerator
Overview 28
Discusses how QXtend Excelerator can be integrated with other programs
Querying Data from the Source Application 28
Lists and describes the steps required to use the Query function accurately
Viewing and Modifying Data 30
Explains how to use different view functions and delete data
Changing Workspace 32
Explains how to use the Change Workspace feature
Updating Data to the Target QAD Application 32
Lists the steps required to update data to other applications using QXtend Inbound
28 User Guide — QAD QXtend Excelerator
Overview
This section assumes that you are a user responsible for performing data integration between QAD
products using Excelerator. By importing QDoc schemas, you use Excelerator to translate database
tables and fields of QAD Enterprise Applications into rows and columns in the Excel worksheets.
You can then use the worksheets to hold records queried from the source QAD application, view
and modify data, and finally update them to the target QAD application.
Querying Data from the Source Application
Note The Query QAD function is disabled when the worksheet is designed to work with an EE
Financials component schema. It is because the query service is not currently compatible with the
EE Financials components.
1 Under the QAD Tools tab in the Ribbon, click Query QAD.
2 In the Query QAD Enterprise Applications window, define the query parameters.
Max Rows Returned. Enter the maximum number of records to retrieve into a worksheet.
Enter zero (0) to retrieve all matching rows.
Filter. Enter a valid Progress query to use as filter criteria to filter records. The filter must be a
valid Progress ABL WHERE clause and can only be defined against the top-level table of the
QXO business object; for example:
cm_site = 'SITE1' and (cm_addr >= '0100' and cm_addr <= '100')
Fig. 3.1
QAD Query Parameters
3 Click Execute Query. A warning message appears. Click OK to proceed.
Using QAD QXtend Excelerator 29
Fig. 3.2
QAD Query Warning
4 The query pulls records from the source QAD application into the worksheet. Save the file.
Troubleshooting Data Queries
No Data Returned from the Data Query
If no data comes back from the data query, examine the qdocResponses.log file. The
qdocResponses.log file records details on what data was returned and whether it matches the
format expected in the worksheet and Excelerator settings.
Make sure that worksheet and column
header names in the Excelerator worksheet match data
object and field XML names in the profile definition in QXtend Outbound and that the profile
name specified in the Excelerator settings is identical to the profile defined in QXtend Outbound.
All these names are case-sensitive. Map them exactly so that Excelerator knows where to place
queried data into the corresponding cells in the worksheet.
Fig. 3.3
Mapping of Data Object and Field Names
30 User Guide — QAD QXtend Excelerator
Fig. 3.4
Mapping of Profile Names
Unhandled Exception Error
When you run into the following error message, read the details to determine the cause of the error.
An unhandled exception occurred in your application. Contact your administrator
or the author of this document for further assistance
A couple of situations may cause to this error:
• If the detailed error message reads “Object reference not set to an instance of an object.”, this
error is caused by a mismatch between XML names of primary key fields and the field names
(not field labels in QEA) in the schema. Make sure that field names in the schema are identical
to corresponding XML names.
• If the detailed error message indicates that the string is not a valid boolean value, check
boolean fields in the schema to see whether they have the proper values. This error often
occurs to custom fields.
Viewing and Modifying Data
After you execute a query to import data from the source QAD application into your worksheets,
you can view and modify data just like you do with any other Excel worksheets.
The tool does not perform real-time validation of the data
you enter in the worksheet. If you enter
invalid data, error occurs when you update records to the target QAD application.
Alternatively, when the Enable Data Grid V
iew option on the Developer tab is selected, you can
click Grid View under the QAD Tools tab to view and edit the data in the data grid view. The data
grid view gives you a better view of the parent-child relationships among data.
Using QAD QXtend Excelerator 31
Fig. 3.5
Data Grid View
To delete records from the worksheet, select the rows and right-click; then from the pop-up menu,
point to Delete and select Table Rows. This operation also deletes any corresponding child records
in the worksheet. For example, if you delete a record in table routing, the tool automatically
deletes its child records that reference the primary key of the parent record in table
routingTransComment.
Deleting rows from the worksheet cann
ot delete corresponding records from the target QAD
Enterprise Applications when you perform data update. To remove records from the target
application, populate the “operation” column with R (for Remove) in corresponding rows to
indicate to QXtend Inbound to delete the records when processing Update QAD.
Fig. 3.6
Deleting Records
32 User Guide — QAD QXtend Excelerator
Changing Workspace
If the change workspace feature is enabled, you can switch the current domain and entity your
Excelerator worksheet is currently working with.
The current workspace for the works
heet is displayed in the Workspace group under the QAD
Tools tab.
Fig. 3.7
Current Workspace
Click the Change Workspace button to switch to another domain and entity. Any Query QAD and
Update QAD requests use the new domain and entity combination. The workspace group is
updated to display the current domain and entity of the worksheet.
Fig. 3.8
Change Workspace
Updating Data to the Target QAD Application
When you have finished viewing and editing data in the worksheet, you can update it to the target
QAD application through QXtend Inbound.
1 Under the QAD Tools tab, click Update QAD and select from one of the following options:
• Process Changes: Only submit records that have been modified.
• Process All: Submit all records in the worksheets.
2 A QAD Application Login window appears. Provide log-in information to access the target
QAD Enterprise Applications.
Fig. 3.9
QAD Application Log-In
Using QAD QXtend Excelerator 33
3 A QAD Update Status window appears with detailed update status information and progress
bar. You can stop and restart the process as needed.
Fig. 3.10
QAD Update Status
4 When the update is complete, any errors that occurred show in the Document Actions pane
below the worksheets. You can switch on and off the error information using the Show/Hide
Errors button under the QAD Tools tab. Double-clicking on a row in the errors panel sets the
focus in the worksheet to the row with the error. You can use the Export Errors button to
export errors to a text file and save it to your specified location for further investigation.
Fig. 3.11
QAD Update Status
34 User Guide — QAD QXtend Excelerator
36 User Guide — QAD QXtend Excelerator
