Upload Data Tool – Technical Configuration Details

July 4, 2017 in Online Tools

Purpose

This function can be used to upload data into an Online Advantage file from a csv format spread sheet. Before using this function a system administrator must have previously configured the upload parameters to match the required spread sheet data. There are limitations on what can be uploaded. Typically, static master file data that is single/multi-value and non-dependent may be uploaded, for example, the BPAY Customer Reference number on the Customer Master file could be updated as it is a single value and it does not have any associated data, whereas Group Related Discount Percentages could not be uploaded because these are multi valued and require an associated Product Group. Fields that are multi-valued and with no associations or multi-valued with fixed value positions (e.g. address fields) may be uploaded.

Be very careful when using this tool. If it goes wrong, you may need to restore from back up. We recommend you have a back up available, and that uploads be done while users are off the system. Any time required to fix data corruption due to invalid uploads will be charged for on a Time and Material basis.

Note that internal data Indexes and Masterfile Audits records are automatically updated when a data upload is completed.

Set Up

Before configuring upload parameters, you must carefully consider if the data fields being updated have any other data dependencies. If in doubt, contact Online Advantage Support for advice.

To configure an upload you simply add an item to the menu system via the Software Utilities > Menu Tools > Menu Details function. A good menu to add uploads to is the Module > Setup > Tools menu. These menus are usually restricted to users with a high security level.

What you’ll need to know before configuring the menu item is:

  • The file being updated e.g. CMF
  • The dict items of the fields being updated e.g. f6 = reps code, f13 = price level, f9 = delivery area code

Pay particular attention to these entries when adding the new menu item:

  • Choose a suitable procedure code for the menu. It should be prefixed with the first 2 characters of the menu you are adding to. e.g. if the menu is ARTOOLS, the procedure code should start with AR.
  • Enter a meaningful caption as this will appear on the menu. If you just enter ‘Upload’, then chances are in a couple of months time you won’t remember what upload it is. Add more uploads and caption them with ‘Upload 1’, ‘Upload 2’, ‘Upload 3’ etc. and real confusion will be created.
  • Choose a type of ‘Procedure’
  • Enter documentation code ‘7917’. This will cause the Help button for the upload to navigate to generic documentation for uploads targeted at the user level.
  • Choose a Procedure of ‘Browser Control’
  • In the Webform Parameter, enter the configuration string:
    • UploadField.aspx?FileName=file&FileKey=fn&Field=fn1;fn2;fn3
    • Where file is the name of the file being updated
    • Where fn is the key field on the file
    • Where fn1;fn2;fn3 are the field or fields to update. Note: The number of columns is limited to 19 but that can be increased if required. Having too many may make the spread sheet and upload screen unmanageable.

Below is an example of how the menu configuration should look (click on it to enlarge).


Example of menu configuration

 

Data validation

If data being uploaded should exist on some other database file (e.g. if you are uploading a rep code to the customer file), then the rep code should exist on the REPS file. So that the upload routine can validate data on other files, there must be a file validation mask set up in the Table Details of the file being updated against the fn dictionary item. You can also set up a Dictionary validation with a file code item code to validate data against a file code list. E.g. Price List Levels 1 to 5.

For numeric data validation, the dictionary code must contain a conversion code starting with ‘MD’.

For date validation, the dictionary code must contain a conversion code starting with ‘D’.

For mandatory validation, the table dictionary item must be flagged as mandatory.

Custom Data (UDD)

If you want to update UDD fields attached to an associated file, configure the associated file e.g. PMF and the UDD dict items that are created when you create the custom data set. e.g CERT.Certificate. The dict item can have a file validation mask if the data should be validated against a foreign file.

Clearing Data

You may clear data by leaving the cell in the spread sheet empty. This is provided the ‘mandatory’ flag is not set against the dict item in the Table Details.

Multi-value Fields

If you want to upload a multi-value field, create a synonym dictionary with an ID in the format of fnn.MV
Add the fnn.MV field to the table and add validation mask and details if required.
In the spreadsheet, set up the data so that each row contains one multi-value and the file key is repeated.

e.g

Supplier,purchase type
HOTS,FA
HOTS,SP
HOTS,CL
CHKNF,FOO
PORKF,FOO
JACK,FOO
CASS,BA
CASS,BS

When uploaded, repeated data is not duplicated in the database. Note: data is always appended and the original multi-value list is never cleared. You could work around this by doing 2 uploads; the first with no data on the spreadsheet to clear existing data, the second with the new data to upload.

Fixed position Multi-values

For multi-value fields where the position is fixed, for example in address fields, m/v position 1 is the first line of address, m/v position 7 is the country code, you may upload these by setting up dict items to correspond to each m/v position.

Example: CMF  f4.1 f4.2 f4.3 f4.4 f4.5 f4.6 f4.7  where the attribute on the dict item is 0 and the T-correlative sets the m/v position

     f4.5
0001 A
0002 0
0003 Postal Postcode
0004
0005
0006
0007
0008 TCMF;X5;;4
0009 R
0010 4
0011
0012 OL2K-STD

New File Items

You can upload new items to a file by setting the table flag ‘Allow upload of new items with field upload tool’ to true.

Capturing Errors

If there are many errors that you need to view, these are recorded in the CTL WF.UPLOAD item for inspection if required.

Limitations

There is a limit of 19 attributes that can be uploaded (19 columns in spreadsheet)

Data Input

­­­

Step 1 – Upload File

Upload File

There is a brief description showing the file to be updated.

Use the Browse button to find the csv file on your local PC to upload.
Return to the Top
­­

Step 2 – Verify file details

Submit

Each row in the spread sheet is shown as a row in the data grid. The right most column is a space for validation messages to appear. Click Submit to upload the data. If there are validation problems, the rows with problems are highlighted and the problem found is shown in the Messages column. You need to fix any problems on the spread sheet and go back to the first step to upload the spread sheet data into the form again.

If no validation problems are found in the data, the file is updated with the new data.
Return to the Top

Spread sheet Format

The spread sheet should be in a csv format. The first row must be a heading row.

The first column must contain the key data of the file being updated i.e. data corresponding to the FileKey configured in the menu parameters.

Subsequent columns must correspond to the fields configured in the Field definition of the menu parameters. There must not be more columns in the spread sheet then are configured on the menu. Any extra columns will be ignored.

{ 0 comments… add one now }

Ask a Question or Leave a Comment

Previous post:

Next post: