|
Overview User Authentication How to Use the Loader QAD Connection Setup Item Data Download Item Data Upload Error Lookup Screen Clearance |
Overview
Item Master Data Loader allows to massively create new and/or modify existing items in QAD’s Item Master.
By downloading existing items and using them as “templates” for new items Item Master Loader speeds up process of entering data into QAD database. It facilitates periodic item settings review and provides quick access to critical item parameters (item status, default site, etc.) in Item Master. It can also be used to synchronize master records between domains or databases
Item Master Loader is using Excel as a primary user interface. For more details on 32Soft Excel Loaders please go to /excel-loaders-for-qad/
User Authentication
Item Master Loader can only be used by users with valid QAD User ID and password and access to 1.4.1 Item Master Maintenance menu in QAD. User Authentication is based on native QAD Authentication logic (user id, password, menu and site security).
Every time user will open Item Master Loader template and at the first attempt to connect to QAD database to upload or download information he/she will be required to enter QAD user ID and password.
How to Use the Loader
QAD connection setup
After opening a loader user should click on Setup button to specify what QAD database Item Master Loader template should be connecting to.
In the pop-up window select a database and click on Select.
Note: If required database is not in the list, please contact IT department.
Connection needs to be setup only once, after opening the template, unless user will need to switch to a different database. If template is saved after database selection, connection step could be skipped next time loader is opened.
Information about QAD database user is connected to will be always displayed at the top of the template header.
Note: always verify database connected before making changes to it.
Item Data Download
To download items from QAD database user will need to specify selection criteria in the header to limit number of item that will be download. Header selection criteria include range of Item Numbers, Product lines, Item Types, Design Groups, Item Groups and Item Statuses.
Once selection criteria are specified user will need to click on “Download” button.
First time user connects to a database after opening a template QAD login prompt will show up. User will need to enter QAD user ID, password and database domain name (if applicable). User will need to have access to 1.4.1 menu in QAD to be able to upload and download data using Item Master Loader.
If user authentication is successful item master data will be downloaded into Excel.
Note: the more items are selected for download the longer it will take to who them in Excel. Number of item lines downloaded is limited to 40000.
Item Data Upload
Item Master Loader allows to create new items and/or modify existing items in QAD.
When creating new items user can first download existing items and use them as a template.
Once all item fields has been entered and reviewed user will need to click on Upload button to create/update items in QAD database.
Before attempting to upload data into QAD database Item Master Loader will run data validation. If it encounters any errors message “Error occurred!!!” will be displayed in the Status window and color of cells with incorrect values will be changes to red.
Each red coloured cell will have comment attached to it with details of the error message.
Once all errors are fixed user will need to click on Upload button again. If data upload is successful message “Loaded successfully” will be displayed in a Status Window.
The following fields are validated on data Upload:
- Item Number (length should not exceed 18 characters);
- Unit of measure (should be defined in QAD)
- Product Line (should be defined in QAD)
- Design Group (if not blank, then should be defined in 1.9.1.1 Group Maintenance)
- Promo Group (validated against Generalized Codes for field pt_promo)
- Item Type (validated against Generalized Codes for field pt_part_type)
- Status (should be defined in 1.1.5 Item Status Code Maintenance)
- Item Group (validated against Generalized Codes for field pt_group)
- Drawing (validated against Generalized Codes for field pt_draw)
- Revision (validated against Generalized Codes for field pt_rev)
- Drawing Location (validated against Generalized Codes for field pt_drwg_loc)
- Drawing Size (validated against Generalized Codes for field pt_drwg_size)
- Price Break Category (validated against Generalized Codes for field pt_break_cat)
- ABC Class (validated against Generalized Codes for field pt_abc)
- Lot/Serial flag
- Site
- Location (should be defined for a specified site)
- Location Type
- Auto Lot
- Lot Group (should be defined in QAD)
- Average Interval (validated against Generalized Codes for field pt_avg_int)
- Cycle Count Interval (validated against Generalized Codes for field pt_cyc_int)
- Commodity Code (should be defined in QAD)
- PO and WO receipt statuses (should be defined in QAD)
- Buyer/Planner (validated against Generalized Codes for field pt_buyer)
- Supplier (when not blank, should be a valid QAD supplier code)
- Routing (when not blank, should be a valid routing code)
- BOM (when not blank, should be a valid BOM code)
Note: when large number of items is updated we recommend updating them in chunks or scheduling update for the hours when load on QAD database is minimal. There are might be restrictions on the maximum number of database records that can be updated at once (database lock (-L) parameter). If this is the case, IT department should be contacted.
Error Lookup
This function helps to identify error on the spreadsheet.
When data validation is performed error messages are returned to Excel and recorded as cell comments for cells where error was found. Click on Error button will move cursor to the next cell with error, so user won’t need to scroll up/down a spreadsheet looking for errors.
This function is especially helpful when there are multiple lines on a spreadsheet and multiple errors that are not all visible on a screen without scrolling up/down or left /right.
Screen Clearance
This function will delete data from a spreadsheet.
Here is an example of spreadsheet before a click on Clear button.
And here is how it looks after user clicked on Clear button.
