(Please note that certain relevant content from the IQ Retail Training Team was used to develop the knowledge base article.)
The IQ Stock Price Update module has been designed to allow users to import their own stock master file details (stock
details) without having to enter each stock item one by one under Stock Maintenance.
The user will also be allowed to make additional amendments to t
This module provides the functionality to import a CSV (comma delimited values) text file of predetermined format, into the IQ system.
NOTE [!]: No Style Management Stock Items can be imported.
SUGGESTION:
For fields where the user is not sure what the import value should be, a stock item that needs to be updated can be changed manually in Stock Maintenance.
The user can in Stock Maintenance module; select the specific field to be visible and see what the new value has to be.
A CSV file can be created to change the value in all the other selected stock items.
Some of these values are calculated according to the specific modules selected.
TIP: The user has realised that there is a problem with the discount allowed, the system allows discount where it
should not. Stock Price Update allows the user to import a CSV file to correct the problem, instead of editing each stock
item and correcting the module selection.
Steps:
Navigate to Stock --> Utilities --> Stock Price Update
Stock CSV Import / Update form will display like below:
To import the stock file click on Load Import File button.
The Import option will be used to import the CSV text file with the predetermined format. Only fields
determined in the Example File can be imported..
What is the generate example file?
The example CSV file is created in which will assist the user in the creation of the new stock items to be imported.
Once the user has clicked on the Generate Example File option, the user will be prompted for an Export
location where the newly created CSV file should be saved.
Once the CSV file has been saved, the user can close the import screen.
How to create your own CSV import file?
The CSV file which was created as an example, can be imported into Excel or Notepad (Or any text type editor).
TIP: It is easier to manipulate data in an Excel spreadsheet.
Also note when you save the file in Excel, please save the file as CSV (Comma delimited) (*.CSV).
Example of CSV file to import.
The format of the import file is defined as follows:
• First Line: Header Information. If the import file is opened in Notepad, each field is displayed and
separated by a comma – e.g. Code,Barcode,SellPrice1.
• All the subsequent lines are relevant information for each defined field for each stock item.- e.g.
ABC,600412343332,47.50,,0.00.
• Example of a correct Stock CSV Import file:
When new stock items are to be imported, there are at least four (4) mandatory fields which have to be completed
for the file to import correctly:
Code
ItemCategory
Department (Major Department)
VatRate
NOTE: Depending on the links set up on Stock in the Module Parameters, more fields might become mandatory.
If the major departments are linked to sub-departments (minor departments) in the Module Parameters, the sub-department also becomes mandatory and should be included in the import CSV file.
NOTE: It is not compulsory to import all the fields Generated in the Example File to create or maintain stock items. In
other words, if the user doesn’t use all the fields, the ones that are not necessary can be removed or deleted.
The user also has the option to make changes to existing stock items, by importing the item code and only the fields of
the stock items which need to be changed.
NOTE: Ensure that the header information for each import is correct, as provided in the Generated Example File.
NOTE: In Excel, if any code in anyone of the stock item fields needs to start with a zero (0), set the field format for
those columns to be text. Excel doesn’t except leading zeros (0). It is advisable not to use departments, etc. that start
with zeros (001), because if the CSV file is opened in Excel the zeros will disappear, unless they are specified as TEXT
columns.
NB[!]: The created file has to be saved in the CSV (comma delimited) file format.
The field format should never be changed. When the file is opened in Notepad, the
field separator has to be a comma (,) and should not have any double quotation marks (“ “).
When updating the stock master details, a lot of the fields can be updated by entering a new value which will overwrite
the already existing value in the field.
It is recommended to only import what is required and not all the fields.
NOTE: If changes are made on some of the fields in the stock master details, it may have major implications in other
modules, for example the Vatrate and ItemCategory of an item.
TIP: Edit an existing stock item in Stock Maintenance and make the changes where required. Add those fields
that were changed to the select visible fields. The values in those selected fields can then be used in the Stock Price
Update module to be imported for all stock items with the same scenario.
STOCK FIELD TERMINALOGIES
CODE
(Important, required).
The Stock Code is a mandatory field (may not be BLANK). The user will not be able to update or add any stock item,
without the stock item code. The code is limited to 30 alpha-numeric characters.
DESCRIPT
(Important, but not required).
The Stock Description will always be updated if provided. If a new item is created and you do not complete this fields
(i.e. leave it blank) the new stock description will be updated with a blank value. Thus, always provide the required
description for any new item to be created. The Stock item description is limited to 50 characters.
If the item already exist, the newly imported description will overwrite the old description.
ALT_DESCRIPT
Any additional information about the item and an extended description may be entered. The field will be updated
with the provided value.
ITEMCATEGORY
(Important, but only required when adding non stock items).
The ItemCategory field has an INTEGER value. The value will default to one (1), which is used for Stock Items. For all
Non-Stock items the value is two (2), when the stock csv file is imported.
NOTE: Changing of the ItemCategory from Stock (1) to Non-Stock (2) will have a major effect on the stock values and
quantities.
DEPARTMENT
(Important and required).
The Stock Major department is a mandatory field for new stock items and may not be BLANK. The values added in this
field must be as they were created in the Stock Major Department Table in Company Details. If the value of this field
doesn’t exist on the Major department table or is blank when creating a new item, an error will be generated.
SUBDEPARTM
(Import based on Company --> Defaults configuration).
If the Stock Sub Department is provided, the value must also exist in the Stock Sub Department Table (Groups Table).
If the Major and Minor Departments are linked in the Company Details, the field is required (may not be BLANK) for
any new items.
If the Sub-departments (minor departments) are linked to stock categories in the Module Parameters, both these
fields need to be included in the import CSV file.
If the Sub-departments (minor departments) are linked to stock ranges in the Module Parameters, both these fields
need to be included in the import CSV file.
GENCODE
The Stock General Code will be updated if provided.
The general code can be used as a secondary stock item code or
barcode for use when integrating stock files.
An example of a general code is the ISBN numbers of books.
BARCODE
Duplicate Stock Barcodes will not be allowed.
The stock Master file AND the temporary import table is checked for
duplicated barcodes during the Import process.
A barcode is an optical machine-readable representation of the data
relating to the stock item to which it is attached.
NOTE: It is suggested that if there are no specific barcodes on the item that can be entered or scanned in, to use the
stock item code as a replacement barcode.
COLORMATRIX
The ColorMatrix is a number identifier for a Colour in the colours table, as created in Module Parameters. This field
will be updated if the ColorMatrix is provided. An error will be generated if the identifying number is not found in the
colours table.
SIZEMATRIX
The SizeMatrix is a number identifier for a Size in the Sizes table, as created in Module Parameters. This field will be
updated if the SizeMatrix is provided. An error will be generated if the identifying number is not found in the Sizes
table.
CYCLE
The Cycle field will be updated if provided (even if BLANK). Example: Summer, Autumn, Winter and Spring.
LINECOLOURTYPE
The Stock Line colour Indicator field also has an INTEGER value. This value will only be updated if Stock Custom Line
Colours have been enabled in Default Settings (1) and if a value is provided. If the value is BLANK or equal to “0”
(zero), it will be ignored. In this way items on special, cascading items, non-stock items, serial number items, etc. can
be “visually” distinguished from other stock items.
To add / edit the line Colours select Utilities Setup Module Parameters Stock Item Line Colour.
PACKSIZE
The Packsize field will be updated if provided, even if it is BLANK.
Example: When cascading items are used for Cokes, there are different stock codes for the single Cokes and the case
of Cokes. The pack size for the single will be 1 and for a case of Cokes it will be 24.
PACKDES
The Pack Description field will be updated if provided, even if it is BLANK.
Example: Coke Single Can 330ml.
AVRGCOST
The Average Cost field will be updated if provided and when adding a new stock item.
When editing, these values will be adjusted by the system via a Stock Adjustment transaction based on the AVRGCOST provided in the import file.
When importing average costs for items, the system will prompt the user to enter an order number.
There is a list available for all average cost adjustments made on stock items, in Stock --> Reports --> Stock Adjustment Report.
SELLPRICE1 - 10
The SellPrice 1 – 10 are the current exclusive selling prices of the stock items. They will be updated if they are
provided.
IMPORTANT NOTE [!]:
When importing Sell Prices, the system will prompt the user to select if the prices are inclusive or exclusive of VAT.
REGULAR_SU
The regular Supplier will only be updated if provided.
If provided, the value has to match a Creditor’s Account in the Creditors Master File.
SUPPLIERCO
The Supplier Stock Code will always be updated if provided. The Supplier Code is the item code used by the supplier for the item.
NOTE [!]: If the supplier’s code for a stock item is different from the users stock code but there is only one main supplier, the code can be added to the system in this field.
MEMO
The Memo Field will be updated if provided, even if it is BLANK. One of the examples of using the Memo field, is to use it for an extended description for the stock item.
STATUS
The Item’s Status is a descriptive text field and will be updated if provided. This field is mainly used for filtering and reporting purposes.
Example:
• U = Unrestricted-use stock
• Q = Quality inspection stock
• B = Blocked stock
• S = Slow Moving
VATRATE
(Important and required)
The VATRATE is an Integer field which specifies the Vat Rate of the Stock Item.
It will be updated if it is provided.
If the VatRate doesn’t exist for the selected item on the system, an error message will be generated when importing.
BINL
The BinL (bin location) will be updated if provided, even if it is BLANK.
(The fields below refer to auto ordering.)
MIN_LVL
The Min_Lvl (Minimum Level) will be updated if it is provided
MAX_LVL
The Max_Lvl (Maximum Level) will be updated if it is provided.
ORD_LVL
The Ord_Lvl (Order Level) will be updated if it is provided.
ORD_QUAN
The Ord_Quan (Order Quantity) will be updated if it is provided.
ORDERMETHOD
The Ordermethod value will only be updated if provided. If the value is BLANK it will be ignored and no changes will be made to the Stock Master.
The user can use one of the three (3) available ordering methods:
0 | Normal Ordering Method |
1 | Replenishment Ordering Method |
2 | Customized Ordering Method |
ORDERFORMULA
The Orderformula specifies the Ordering formula to use if the Order Method has been set to Custom Ordering. This is the number value of the Ordering Formula as it was created in the Module Parameters. An error message will be generated if the Ordering Formula doesn’t exist in the Ordering Formula table.
MAXDISCOUNT
The MaxDiscount field is used to specify the maximum discount percentage allowed on the selected stock item. The MaxDiscount field will be updated if a value is provided.
REPORTITEM
Reporting Item is the item code of the main item, in other words the selected item reports to the main item. It will be updated if the Cascading items were enabled in Default Settings (1) and if it is provided. An error message will be generated if the Stock Item does not already exist in the Stock Master.
REPORTCFACTOR
The ReportCFactor is the conversion factor for Cascading Items. It will be updated if the Cascading Items were enabled in Default Settings (1) and if it is provided.
NEWPRICE1 - 10
New Selling Prices will update if provided. New selling Prices can be imported for items and the system will automatically start on the indicated Pricechange1 date to use these new prices as the new Retail Prices. The system will prompt the user to select if these selling prices are VAT excluded or included.
PRICECHG1
The Price Change-1 field will be updated if provided. This date will indicate when the system should start charging the new Selling prices, as they were imported.
FUTURECOST
The Future Cost field will be updated if provided.
Future Cost will work for stock purchasing, if the “Default Cost For Goods Receiving And Purchase Orders” has been set to “Future Cost” in Default Settings (2).
The system can also be set in Defaults Settings (2) to “Check The Selling Of Stock Under Cost”, to do the checking on future cost.
When the Future Cost needs to be imported, the user will also have to import a FutureCostDate, to tell the system on which date the future cost should be activated.
GENERICTYPE
The GenericType can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
BRAND
The Brand can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
MODEL
The Model can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
TECHNICALSPEC
The TechnicalSpec can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
SUPPHIERARCHY
The SuppHierarchy can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
SUPPDEPARTMENT
The SuppDepartment can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
IMPORTTYPE
The ImportType can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
TARIFFCODE
The TariffCode can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
MATERIAL
The Material can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
ASSEMBLYTYPE
The AssemblyType can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
GUARANTEETERM
The GuaranteeTerm can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
COLORDETAILED
The ColorDetailed can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
INCOTERMS
The IncoTerms can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
MEMO
The Memo can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
BUYER
The Buyer can be used to Auto Generate Item descriptions, if it is enabled in Module Parameters. This field will update if it the correct data is provided.
CATEGORY
If the Sub-Departments (minor departments) are linked to Stock Categories in the Module Parameters, both these fields need to be included in the import CSV file. If the Stock Categories are linked to Stock Ranges in the Module Parameters, both these fields need to be included in the import CSV file.
RANGE
If the Stock Categories are linked to Stock Ranges in the Module Parameters, both these fields need to be included in the import CSV file.
ABCCLASS
Specifies the A, B, C class of stock items. The field will be updated if provided.
1 | A – Top Products |
2 | B – Top KVI Lines Per Category |
3 | C – Non Sensitive Lines |
0 | Not Defined |
ABCCLASSGP
The GP percentage is used in conjunction with one of the above mentioned ABC Classes. The field will be updated if provided.
ALLOWDISCOUNTBITS
0 Not Defined (No Discount Allowed)
1 Invoices and/or Recurring Charges
2 Credit Notes
4 Goods Receiving
8 Returns
16 Purchase Orders
32 Sales Orders
64 Quotes
128 Job Cards
256 Point of Sales
Each of the codes above represent a module in the system.
The user can select one or a combination of the codes above to allow discount.
When a combination is used, the codes for each of the modules have to be added together.
In other words if discount will be allowed on the stock item in the Point of Sales, the Sales Orders and the Quotes modules, the code to use will be 256 + 32 + 64 = 352.
If all the modules are selected the code will be 511.
DISALLOWDECIMALS
Import the DisallowDecimals field as “True” for all solid or pre-packed products, which need to be sold as a whole unit.
Change the DisallowDecimals field as “FALSE” when the units to measure are in meters, liters or kilograms.
NOTE: If the field is not provided, the system will default the field to FALSE.
ENTERSER
This field specifies if Serial Numbers are enabled for this stock item. In other words, should we store serial numbers for this stock item. The “Enter serial number” will be updated if provided. If serial numbers are enabled, the user will use True for the Serial Number items and False for all other items.
VOLUMETRICS
The Volumetrics values will be updated if the correct values are provided.
NOTE: The Volumetrics quantities and rounding figures have to be entered manually via the Stock Maintenance module.
The user has the option of one of each of the following volumes per item:
0 Units (Default setting)
1 Square Meters – Length x Width in meters
2 Cubes Meters – Length x width x height in meters
3 Linear – used to measure the perimeter of a rectangular. (2 x Length) + (2 x width) = 2 x (length + width)
4 Grams – the item’s weight in grams
5 Kilograms – the item’s weight in kilograms
6 Millimeters – length in millimeters
7 Meters – length in meters
8 Millimeters – volume in millimeters
9 Liters – volume in Liters
10 Tons – weight in Tons
11 Linear Square Millimeters - the perimeter in millimeters
12 Linear Square Meters - the perimeter in meters
13 Square Millimeters – length x width in millimeters
14 Cubes Millimeters – length x width x height measured in millimetres
15 Linear Millimeters - the perimeter in millimeters
SCALEITEM
(Only applicable to scale items.)
The ScaleItem field specifies if the stock item is a scale item or not. In other words, if a True is entered for an item, it means that this is a scale item. T
he value for all the non-scale items will be false. This field will be updated if a value “True”/”False” is provided.
NOTE: For all scale items, the check in the DisAllowDecimals field has to be removed and for importing purposes the field has to imported with the value “False”.
STOCKTYPE
The StockType field will be updated if the correct value is provided.
0 Normal Stock Item (Default Value
1 Cell Phone
2 AirTime
3 Television
4 Voucher
5 Sundry Item
ONHOLDTYPE
This field “OnHold Type” specifies the modules in which this stock item has been set to ONHOLD. The field will be updated if the correct values are provided.
0 Units (Default setting)
1 Invoices and/or Recurring Charges
2 Credit Notes
4 Goods Receiving
8 Returns
16 Purchase Orders
32 Sales Orders
64 Quotes
128 Job Cards
256 Point of Sale
512 Stock Transfers
1024 Stock Transfer Requests
2048 Bill of Quantities
4194304 Agent Transactions
134217728 Laybyes/Layaway
Each of the codes above represent a module in the system.
The user can select one or a combination of the codes above to be On hold for the selected module.
When a combination is used, the codes for each of the modules have to be added together.
In other words if the selected stock item should be On hold in the Point of Sales, the Sales Orders and the Quotes modules, the code to use will be 256 + 32 + 64 = 352.
If all the modules are selected the code will be 138416127.
EXCLUDESELLINGVALUE
The ExcludeSellingValue field specifies if the stock item should be excluded from Ordering and then which modules it should be excluded from.
The field will be updated if the correct values are provided:
0 The item should not be excluded from ordering.
4 Exclude item from ordering in Goods Receiving (GRV)
16 Exclude item from ordering in Purchase Orders
20 Exclude item from ordering in Goods Receiving (GRV) and from Purchase Orders
DEFAULTWAREHOUSE
The DefaultWarehouse field is the default warehouse which will be updated when the stock item is sold. This field will update if the correct number for a warehouse is provided, as it was created in Module Parameters.
DEFAULTPURCHASINGWAREHOUSE
The DefaultPurchasingWarehouse field is the default warehouse which will be updated when the stock item is purchased.
This field will update if the correct number for a warehouse is provided, as it was created in Module Parameters.
DEFOUTSTATUSCODE
The Default Out Status Code field is the default Status Code which will be used when the stock item is sold or taken out of the system. This field will update if the correct status code, as it was created in Module Parameters.
DEFINSTATUSCODE
The Default In Status Code field is the default Status Code which will be used when the stock item is bought or taken into the system. This field will update if the correct status code, as it was created in Module Parameters.
MDR_SUPPLIER_ID
(Applies to the BuildIT group.)
The MDR_Supplier_ID is the Master Data Repository Supplier Id number which will identify the same items between different branches which use different Stock item codes for the same item.
MULTIPLEBARCODE
The system allows the user to add multiple barcodes to items.
In other words, a selected item can have more than one barcode to identify the item. An example of the import file of multiple barcodes for an item:
MULTIPLEBIN
The system allows the user to store an item in multiple bin locations in a warehouse. In other words, a selected item can have more than one bin location where the stock is stored. An example of the import file of multiple bins for an item:
BINWAREHOUSE
The system allows the user to link specific bin locations to specific warehouses. If the bin locations have been linked to a specific warehouse, for example Warehouse 1, the warehouse linked to the bin location can also be imported.
Import CSV file
The Stock Price Update functionality can be found under IQ Enterprise --> Stock --> Utilities --> Stock Price Update.
The following form will display.
Click on the Load Import File button and select manual CSV file Import.
Select the 1.) CSV file and click on the 2.) Open button.
On the gridscreen below, the system will indicate in the Error field if there are any errors or not.
In the import below, it looks like the import is successful, since there is a status of "No Error".
If there are no errors click on the Process button.
The following dialog box will appear, the user can select the Inclusive or Exclusive sell prices to be imported.
Click on the Accept button to proceed. .
If the import was successful, the items will disappear from the import details grid screen below.
The following message will appear like below.
Import Completed. Items that did not import successfully will remain in the Import Details grid for review.
Click on the OK button and review the items that was imported.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article