w450 - hands on: managing data with acclaim imports and ... · summit 2007: get connected 6...

Summit 2007: Get Connected

W450 - Hands On: Managing Data with Acclaim Imports and Exports

Mark WalshSoftware Support

See how to take data manipulation to a whole new level using the Acclaim Import/Export feature. In this hands-on session, you can experiment with this feature and practice importing flat files right into your Acclaim maintenance screens. You’ll also get an opportunity to practice exporting data from Acclaim. This session will provide you with the skills you need to 'manipulate' your data throughout the system.

Pre-requisites: Basic Understanding of the Acclaim File Structure

Wednesday, 1:15 p.m.

© 2007 Activant Solutions Inc. All Rights Reserved

2

Session Objectives

Create an Import/Export templateExport data from the Acclaim systemImport data into the Acclaim system



3

Additional Resources

Solutions & documentation on the Activant Customer Web Site:

www.distribution.activant.com

Activant Web-Based TrainingsTaking Control of Your Activant Data with Acclaim’s Import/Export Feature


4

Thank You for Attending

W450 – Hands On: Managing Data with Acclaim Imports and ExportsMark WalshPlease submit the Session Feedback Form

To receive NASBA credits, please be sure to complete the Session Feedback Form and sign the class roster in the back of the room


W450 - Hands On: Managing Data with Acclaim Imports and

Exports

Wednesday, 1:15 p.m.

Summit 2007: Get Connected 2

This manual contains reference information about software products from Activant Solutions Inc. The software described in this manual and the manual itself are furnished under the terms and conditions of a license agreement. The software consists of software options that are separately licensed. It is against the law to copy the software on any medium, or to enable any software options, except as specifically permitted under the license agreement. In addition, no part of this manual may be copied or transmitted in any form or by any means without the prior written permission of Activant Solutions Inc.

From time to time, Activant makes changes to its software products. Therefore, information in this manual is subject to change, and the illustrations and screens that appear in the manual may differ somewhat from the version of the software provided to you.

Created by: Yardley Educational Services

©2007, Activant Solutions Inc. All rights reserved. Activant and the Activant logo are registered trademarks and Activant Prophet 21 is a trademark of Activant Solutions Inc. All other company or product names are the trademarks or registered trademarks of their respective companies.

Activant Solutions Inc. 7683 Southfront Road Livermore, CA 94551


Lesson 1: Creating a Template After completing this lesson, you will:

♦ Know the databases with which Import/Export can be used ♦ Understand how to access the Import/Export feature ♦ Understand the function of the fields contained within a template

Creating a Template


Import/Export Databases

There are over 94 different Acclaim databases that can be accessed using the Data Import/Export feature. Using this feature, information can be exported from or imported into any of these databases. Furthermore, the databases that can be accessed are both Progress code and XL code databases.

To view all the different databases that are available for use within the Import/Export function, jump to System Admin…> Utilities… > and then choose Import/Export.

Creating a Template


The following screen may display that is a list of all the different areas of the system where Import/Export will work:

Note: If you are not on the screen titled “Import/Export Templates Browse By Screen,” you will need to reindex your browse. To do so, press the [Tab] key and choose the “Screen…” reindex option. Once chosen, the above screen will display.

Creating a Template


Accessing the Import/Export Feature

You can access the Import/Export function in three different areas of the system. All three methods will allow you to use the same functionality.

One way to access the feature is to press the [F7] key within a maintenance screen. For example, pressing [F7] at the Customer Code prompt within the Customer Maintenance option will display the Import/Export choice:

Import/Export can also be accessed from within the Utilities module. Jumping to the Utilities module will display the Import/Export choice:

Creating a Template


The last way to access Import/Export is from within the Company module. Jumping to Company > Options… > Acclaim Screens will place you on the “Screen Enable Browse.” Pressing [F6] on any of the databases that functions with Import/Export, such as Customer Maintenance, will display the Import/Export choice.

Creating a Template

Within Import/Export, a template is used as the “blueprint” between a file that resides on the RISC box and the Acclaim system.

Note: All files used or created with the Import/Export Function must reside on the RISC box in AIX. If you export a file, a file will be created in AIX. If you import a file, that file must also reside in AIX.

The template tells the system a lot of information: What fields are being imported or exported Where the file is located (import) or where the file is being created (export) What type of file is being created or imported If the records that will be imported or exported are going to be limited

Creating a Template


IMPORTANT!

Before you start to create a template, make sure that the Data Import/Export screens have been enabled. To do so, jump to Company > Options…> Acclaim Screens. The Data Import/Export Template and the Data Import/Export Field options will need to have their “Browse” fields set to “Enable” before a template can be properly created.

Jump to Customer and choose Customer Maintenance. Press [F7] at the Customer Code prompt and then choose the Import/Export option. This will place you in an “Import/Export Template Browse by Name.”

Any existing templates that were created in that module are displayed. Press the [INSERT] key to add a new template. This will place you on the template screen in “Add” mode.

Creating a Template


Template Header

Fill in the “Name” and “Project” field for your template. The “Screen” field is filled in automatically for you. The combination of these three fields makes up a unique template header. You could therefore use the same Name and Project for two different templates, as long as those templates were in different areas of the system. Press [ENTER] to move to the template “General Information” section.

Note: If accessing a template and creating it through the Import/Export option listed in the Utilities module, you may be prompted to specify the Screen with which you will be using Import/Export.

Template “General Information” ♦ Scheme – This field specifies what type of file you will be using with this

template. There are four different file types available: DBaseIII – File type readable by many data manipulation applications,

such as Microsoft Excel.

XML – File type that defines each piece of information that it holds on a record-by-record level.

Delimited – Flat text file type (ASCII) where a delimiter separates each record and each field within a record is separated by a delimiter.

Fixed – Flat text file type where a delimiter separates each record and each field within a record has the exact same length. The length of every record, therefore, is the exact same length.

♦ Desc – informational field only describing the template ♦ Enable Export – determines whether or not the template can be used for

exporting data ♦ Enable Import – determines whether or not the template can be used for

importing data ♦ Quoted – determines if extra double quotes are removed from source file

when importing data Choose a scheme type of “Delimited” for your template (leave the “Col Delim” and “Row Delim” fields at their default values) and give the template a description. Leave both the “Export” and the “Import” fields set to Yes, and leave the “Quoted” field at No. Press [CTRL][N] to access the “Mapped/Screen Columns” section of the template.

Creating a Template


Mapped/Screen Columns

The “Mapped/Screen Columns” section of a template can be displayed in two ways; in “Mapped Columns” sequence where only those fields chosen to be on the template show, or “Screen Columns” sequence where all fields available for the template are shown and only those fields chosen to be on the template will be marked with a “Col”umn number.

Adding Fields to a Template

While the cursor is in the Mapped/Screen Columns section, press the [TAB] key and sequence the browse by “Screen Columns…”

When sequenced by Screen Columns, the Mapped/Screen Columns section will look as follows:

All of the fields that are available within this template will be displayed in the browse. Highlight a field that you want to add to the template and press [ENTER] on the field. A “Col” number will then display next to that field.

Note: When you first create a template, you will notice that at least one field is always automatically chosen to be in the template. The field that is automatically chosen is the field that is required to be in the template if the template is going to be used for importing purposes. It is typically the key field for that table.

Add any other fields to the template that you would like to import or export and then press [F2] to save the template. Then press [F4] to return to the “Import/Export Template Browse By Name” screen.

Creating a Template


Editing a Template

Access your template again by highlighting it in the browse and pressing [ENTER]. Press [CTRL][E] to edit your template and then [CTRL][N] to bring the cursor to the Mapped/Screen columns section again.

To delete a field in the template, highlight the field you want to delete and then press [F6] or [ENTER] and the following screen will appear:

Choose the “Delete” option and then answer Yes to deleting the screen column.

Note: The “Delete” option in the PowrDrill window will only appear if the browse of fields is set to “Screen Columns…” In order to delete a field when the browse is sequenced by “Mapped Columns…,” you must press [CTRL][D] on the field that you want to delete. A prompt will appear asking you if you want to delete the field.

In order to change the sequence of the fields in your template, you will take the same steps as above but will use the “Move” option instead. When the “Move” option is chosen, the following screen will appear:

Press [F3] on the “To:” field and then choose the column number to which you would like to move the current field. Press [ENTER] to make the change.

Creating a Template


Note: The “Move” option within the PowrDrill window will appear, regardless of how the browse is sequenced.

To access Column Maintenance, you must first change the sequence of the Mapped/Screen Columns browse. Press the [TAB] key and choose “Mapped Columns…” Highlight a field and press [F6] or [ENTER]. Then choose the “Maintenance” option. The following screen will appear:

Here, the Name, Desc, and Width fields can all be changed.

Name Title of a given field. When importing a dBase III or XML file, the Name field on the Maintenance screen must match the name of the field in the file that you are attempting to import. Otherwise, the field will not be recognized by the template and therefore will not be imported.

Desc Informational field describing a given field.

Width Length of field in character spaces. Editing the Width field is very useful when importing a fixed file. Always be careful, though, not to truncate any data that you are importing or exporting.

If any changes are made, press [F2] to save the Column Maintenance screen, then press [F4] to return to the template edit screen, and finally press [F2] once more to save all the changes that were made to the template as a whole.

Creating a Template


Workshop 15 minutes

1. Find in the system where there is a complete list showing all of the areas with which the Import/Export feature can be enabled.

2. Create a new template in Customer Maintenance that has your initials and company indicator as the template name. Give the template the following fields: Customer Code, Bill-To Address 1, Bill-To Address 2, Bill-To Zip, and Class. Use the scheme type of “Delimited” (Leave the “Col Delim” field set to “Comma” and the “Row Delim” field set to”CRLF”). Then, change the name of the Bill to Address 1 field.

3. From the Item module, create a new template for the Item Maintenance screen. Set the “Scheme” to “XML.” Add the fields “BaseCost” and “MovingAvgCost” to the template. Try to choose the fields as efficiently as possible, utilizing the different browse sequences that are available.

Creating a Template


Lesson Summary

In this lesson, you learned how to access and enable the Import/Export function. You created and edited a template and understood the functions of the various fields contained within it.


Lesson 2: Exporting After completing this lesson, you will:

♦ Export data from the Acclaim system to an AIX file ♦ Understand how to limit the records that are being exported

Exporting


Starting the Export

To begin any export of data on the Acclaim system, you must first access the template that you are going to use to export data. Again, this can be done in one of three ways, but this exercise will step you through how to do it using the maintenance screens.

Jump to the Customer module and choose Customer Maintenance. At the Customer Code prompt, press the [F7] key and then choose the Import/Export option. Find the template that you created in the Workshop from the first lesson, and press [ENTER] on it. From the resulting template screen while the cursor is on the “Name” field, press the [F6] key and choose the “Export” option in the PowrDrill menu:

Exporting


Exporting Options

Once the Export option is chosen, the following screen will appear:

Format – shows what type of file you are going to create

Export – setting this field to Yes tells the system to continue with the export of data. Setting it to No allows you to save changes to this screen, but not continue with the export

File - holds the name of the AIX file that will be creating with your exported data

Note: This field will assume your AIX home directory. For example, if AIX user “mw” enters the file name “CUSTOMERDATA,” the file will actually be created in that person’s home directory, or /home/mw/CUSTOMERDATA.

Sort By, Start and End – these fields together allow you to limit the records that you are going to export to your AIX file

These fields do NOT determine the sequence of the fields within a given record in your export file. If you wish to order the fields for all records that are being exported, you will need to change the column sequence of the fields within your template (see Editing a Template on page 11). The sort fields will, however, determine the sequence of the records within the file.

Exporting


Change the “File” field to be your name, and then limit the export to only those customer codes between “BES100” and “FLUAIR”:

Press [F2] to begin the export. An “Export Status” screen will appear and you will see a “Finished” message when the export is complete:

Exporting


Viewing the Export File

Press [F4] until you are returned to the Customer module. Then, to view the file that you just created, press [F7] to access the Toolbox, choose “Accessories” and then “Viewer.” Type the name of your file at the “File Name” prompt and press [ENTER]. Your file should look similar to this:

Press [F4] to return to the Customer module.

Note: The File Viewer does not support the viewing of dBaseIII files. The file can be easily viewed, however, if transferred to a PC and opened in Microsoft Excel.

Exporting


Workshop 15 minutes

1. From the Customer database, export a file using your template and give the file a name similar to “<YOURNAME>#2”. Given your terminal number, limit your export to only those customers according to the following table:

Terminal #1 Customer Codes ASJIND – BES300

Terminal #2 Customer Codes BES500 – CHEFMA

Terminal #3 Customer Codes CAOELE - DAVIND

Terminal #4 Customer Codes DELSTA – ECOM2

Terminal #5 Customer Codes ECOM4 - FROELE

Terminal #6 Customer Codes HVASAL - KIRKPLU

Terminal #7 Customer Codes MACPAR - MURELE

Terminal #8 Customer Codes NORSTA – PR21

Terminal #9 Customer Codes RAHELE – TRAPRO

Terminal #10 Customer Codes TRISTA – ZZ0001

Terminal #11 Customer Codes ZZ0003 – ZZ0007





2. View your file using the file viewer.

3. Export another file, but enter the following as the “File:” name:

a. /customercode.txt.

Were you able to export the file? Why or why not?

Exporting


Lesson Summary

In this lesson, you learned to export a file to an AIX file as well as limit the records that were exported to that file. You also learned how to view your export file using the File Viewer.


Lesson 3: Importing After completing this lesson, you will be able to do the following:

♦ Import a file into the Acclaim system ♦ View import data before and after they are imported ♦ Understand import errors

Importing


Starting the Import

To begin any import of data on the Acclaim system, you must first access the template that you are going to use to import data. Again, this can be done one of three ways, but this exercise will step you through how to do it using the Maintenance screens.

Jump to the Customer module and choose Customer Maintenance. At the Customer Code prompt, press the [F7] key and then choose the “Import/Export” option. Find the template that you created in the Workshop from the first lesson, and press [ENTER] once it is highlighted. From the resulting template screen while the cursor is on the “Name” field, press the [F6] key and choose the “Import” option in the PowrDrill menu:

Importing


Once the Import option is chosen, the following screen will appear:

Format Shows what type of file you are going to create

Import Setting this field to Yes tells the system to continue with the import of data. Setting it to No allows you to save changes to this screen, but not continue with the import

File Holds the name of the AIX file from which you will be importing data

Preserve Allows a user to test the import, only update existing records, only import new records, or update existing records and create records at the same time

Test– will “test” the import and show how records will be updated if the import was to be run

New – will only create new records

Update – will only update existing records

Both – will update existing records and create new records during the same import

View Data

Determines how you would like to view the changes that are going to be made or that have already been made to the records affected during your import:

No – no records will be listed during the import

Before – the system will display how records will be changed if the import is continued

After – records and the changes made to them will only display after the import has completed

Importing


Both – records and the changes made to them will show twice; once before the actual import occurs and then again after the changes have been made to the database

Editing the Import File

Before beginning the import process, we will edit our import file first so that later we can better understand some import errors. Access the file that you exported in the workshop from Lesson 2. Press [F7] to access the Toolbox, and then choose “Accessories” and “Viewer.” Enter the name of your file and press [ENTER]. We’ll make four edits to the file:

1. Arrow to the end of the first record (ABRTOO) and change the class field to “AA”:

2. Arrow to the second record (ACMJEA), and change the zip code field to “99999”:

3. Arrow to the third record (ALCTOO), and backspace over the customer code:

Importing


4. Arrow to the fourth record (ALLFOR), and change the address to read “19 WEST COLLEGE AVENUE”:

To save your changes, press [F2], answer Yes to the “Save?” prompt, and then press [ENTER].

Continuing with the Import

Now that the import file has been edited, access your template in the Customer module again. On the template screen from the “Name” field, press [F6] and choose the Import option. The following screen will appear:

Arrow to the “File” field and edit it to show the name of the file that was edited in the previous page. Change the “Preserve” field to “Both” and change the “View Data” field to “Both.” Press [F2] to begin the import. The following screen will display:

Importing


Understanding Import Errors

This resulting Import Status screen shows summary information about the import. Most importantly, it shows the kinds of errors that we would receive if this import were to be executed in full. Note that for this import, the “Preserve” option was set to “Both,” and therefore no changes will be made to the Customer database whatsoever until we choose the “Proceed” option.

Data Errors These errors are created when the import attempts to place alpha characters in a field that only allows numeric characters.

Key Errors These errors occur when the key field of a record is missing. For example, the system is not able to update a given Customer record from a file if the corresponding record in the file does not contain a customer code.

Dupl Keys These errors will occur when there is more than one key field in a file. For example, if there are two records in a file with the same customer code, the second will not update the Customer database since it is a duplicate record.

Validate Errors

These errors can only occur when a system’s Auxiliary Tables are set to validate the information that is entered into a database. If the value being imported does not already exist in the corresponding Auxiliary Table, the error will occur.

Update Errors

These errors will display if an import record is not updated to the database for a reason other than the four aforementioned errors.

Importing


From the Import Status screen, you can see that there is one data error, one modified validate error, and one new validate error. To view the errors, press [ENTER] on the “Rows” option in the “View Data Import” window. Then from the “Rows” window, choose “All.” The following screen will display:

Importing


The Status field shows that the first three records hold the different errors. Press [ENTER] on the first row (customer ABRTOO’s information). The resulting screen will appear:

The system shows that the error (Data Error) occurred on the Class field. The system cannot accept the alpha characters of “AA” into the Customer Class field since it is a numeric-only field.

Importing


Press [F4] to return to the previous screen (“Imported Rows Browse by Row” screen). Arrow down to the second record and press [ENTER]. The following screen will appear:

For the second row (customer ACMJEA’s information), because the error reporting is even more specific, there is an “Update Errors” choice on the PowrDrill menu. Choose the “Update Errors” option and the following screen will display:

The error that is displayed here is what was summarized as the “Modified Validate Error.” Because the zip code 99999 is not found in the Zip Code Auxiliary Database, the system will not allow it to be updated into the existing Customer record.

Importing


Press [ENTER] and then highlight the third record and press [ENTER] and then choose “Update Errors.” The third record holds information for the customer ALCTOO, but the customer code had been removed from the import file. Therefore, the system tried to create a record, but could not since there was no customer code available to create. Furthermore, there is no Salesman field within the import file either, and along with the Customer Code, the Salesman field is a required field when creating a new customer.

The “Create-err” (i.e., the “New Validate Error”) is therefore generated since the system cannot determine what the salesman is for the new customer code that it is attempting to create. Remember that for this record, even if a salesman was specified, the Customer Code was removed from the import file, so the system has no way of determining that the information in the file related to a specific customer code.

Importing


Press [ENTER] and then highlight the fourth record and press [ENTER]. The following screen will appear:

Importing


For ALLFOR’s record, there was no error, but since the “Status” of the record was “Update,” the system will allow us to drill in further to see the actual data that was changed. Highlight the row where the “Status” field is “Update” and press [ENTER]. The following screen will appear:

The system will display both the new value and the old value for the field you specified.

Completing the Import

Now that we have examined the errors, we can continue with and complete the import. Press [F4] four times to return to the “View Import Data” window. Because when we started the import we set the “View Data” field to “Both,” there is a Proceed option available.

Note: At this point, no records in the Customer database have been affected. Only once the “Proceed” option is chosen will the import actually occur. You can see that the “Status” field on the Import Status screen still shows “Importing” indicating that the import has not yet completed its changes.

Importing


Choose the Proceed option and the import will finish and leave you on the following screen:

Now, you can see that the “Status” field shows “Complete.” At this point the changes have been made to the Customer database. The “Updated” field shows that one record has been updated. That record was the information for customer ALLFOR where the address was changed to “19 WEST COLLEGE AVENUE.” The “View Import Data” window shows again since the “View Data” option when the import was begun was set to “Both.” The records can be viewed now that the import has been completed and our database has changed.

Importing


Workshop 20 minutes

1. Using the File Viewer, perform the same steps in the “Editing the Import File” section on page 25. Make the changes to the file that you exported in Lesson 2’s workshop (i.e. the <YOURNAME>#2 file). Save your changes.

2. Begin the import of your edited file back into the Acclaim system using your template. Set the “Preserve” and “View Data” fields to “Both” and press [F2] to start the import process. Examine and understand why your changes were or were not accepted by the import. DO NOT proceed with the import. Instead, press [F4] and cancel the import.

3. Begin the import of your edited file again. This time, set the “Preserve” field to “Both” and the “View Data” field to “After.” Press [F2] to start the import process. What difference do you notice about the “View Import Data” window? Why has that change occurred? What does this signify about your import process?

Importing


Lesson Summary

In this lesson, you learned how to import data into an Acclaim system. You understood how to view the errors during an import and understood what those errors meant.

Session Feedback Form Summit 2007 – Las Vegas, NV

Please take a moment to evaluate this session and offer feedback. Activant uses your input to understand your needs and to determine future Summit sessions.

Session Name: Session Number:

Presenter’s Name:

How important is this topic to your job/company? 1 2 3 4 5

Not Important Important

Please rate the educational value you received from this session. 1 2 3 4 5

Low Value High Value

1. What software are you currently using?

2. How long have you personally used this software?

3. Describe the effectiveness of your instructor.

4. What is your overall evaluation of this session?

5. What could have been done to improve this session?

6. What sessions would you like to see presented at future conferences?

7. What issues will be critical to your business in the next 12-24 months?

Check here if you would like CPE credits. To receive credits, be sure to sign your name at the bottom of this form and sign the roster in the session room.

Answering the following questions is OPTIONAL (but required for CPE Credits).

Yes No

Did this session meet your expectations, based on the description/objectives in the registration materials?

Were the pre-requisite requirements stated in the course description appropriate?

Did the session materials contribute to achieving the learning objectives?

Did the equipment (screen, microphone, projector, etc.) in the room enhance the instruction?

Was the time allotted for the session appropriate for the topic?

Name:__________________________________________________________________________________________________

Company:_____________________________________________________ E-mail:___________________________________

w450 - hands on: managing data with acclaim imports and ... · summit 2007: get connected 6...

Documents