Stock | Utilities | Stock Price Update

Modified on Tue, 29 Oct at 3:56 PM

(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

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article