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 something goes wrong, you may need to restore data from a 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 with this tool.
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 only.
What you’ll need to know before configuring the menu item is:
- The file being updated e.g. CMF for Customers
- The dict items of the fields to upload 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 hard to read.
Below is an example of how the menu configuration should look (click on it to enlarge).
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.
Multi-part file keys
For multi-part file keys e.g. FGI where the key is made up of “Location * Product * Size * Colour”, you can either use the f0 dict, and the spreadsheet will have to contain the * delimited data e.g. SYD*WIDGET*12*BLUE, or you can use individual components of the file key like this example:
UploadField.aspx?FileName=FGI&FileKey=LocationCode&Field=ProductCode;SizeCode;ColourCode;f10;f60
which allows the spreadsheet to be in the format of “Location, Product, Size, Colour, Primary bin, Secondary bin”. Note that the dict items must have a G* correlative for this to work e.g.
LocationCode G*1 ProductCode G1*1 SizeCode G2*1 ColourCode G3*1
Capturing Errors
If there are many errors found, 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.