Info and Concepts
Commonly manufacturers will provide a list of buying/selling prices for their distributors. These are often used as a reference in sales and buying decisions. Manufacturers may also stipulate that their list selling prices are compulsory and no variations from these prices are allowed.
As of version 3.12 of Aerotrack there are new fields per inventory item where you can enter this list information including its currency. This can obviously be a very tedious operation as your supplier may have a spreadsheet containing thousands of items with pricing information.
There is also a new tool to let you import this data into a pricing imports table. From this import table we can “set” the data into matching items in our main inventory table. By using this new tool you can literally import thousands of items of pricing data into your system in minutes.
This task is really 3 separate tasks:
- Preparing the spreadsheet data prior to import
- Importing the spreadsheet data into the inventory imports table.
- Setting the matched items from the import table into the inventory table.
Items are matched on item code only. If there is no matching item code between the import table and inventory table, no data will be set.
If using a foreign currency changing the system rates for each currency will automatically adjust the AUD selling prices of the item.
The spreadsheet must be Excel 97-2003 format. The new 20007 format will not import.
The inventory list view form in Aerotrack.
Preparing the spreadsheet data prior to import
- Your spreadsheet should contain at least the following columns whether you have data or not:
- i. item_code
- ii. item_description
- iii. list_sell
- iv. list_buy
- v. currency
- vi. list_sell_compulsory
Format this as necessary.
- Allowable currency names are AUD, USD, CAN, EUR, GPB, JPY, NZY use uppercase!
- If you want the sell prices to be compulsory set the list_sell_compulsory value to “1” otherwise set to empty or “0”.
- As much as possible remove empty rows from the spreadsheet.
Importing the spreadsheet data into the inventory imports table.
- From the inventory list view screen, click “import pricing data” from within the blue toolbar.
- You will be in the new import pricing table. If there are existing records in the table clear this by clicking the “Delete all current pricing data”
- Select from the menus “File > Import > From Excel File”
- Browse to the file you have created as in the instructions above.
- You will see the table of data appear from your spreadsheet as below:
- Make sure you remove the first row of data which is the column names by clicking the checkbox “First row contains fieldnames”. The first row of data (fieldnames) will be removed. Click “Next”.
- You will see a screen as below. If you have typed the names as in 1. in the first section all columns will already match.
- Click “Next” and the import will begin.
Setting the matched items from the import table into the inventory table
- Once the data has been imported you can see items that have a match as they will say “Yes” in the match column. Matched items will also show a category, unmatched items will have an empty category.
- You can “Trim” empty rows etc. by clicking the button “delete empty rows” if you wish.
- Click the button “Set Pricing data into matched items”. The screen will show the busy indicator while processing. Approximately 1000 rows can be processed a minute.
You can convert text/csv files into excel format by using various formatting tools. Please consult tech support for more help on this.
Try to implement this process from a computer on the local network. Importing data remotely can be very slow.
It is recommended that you be the only client active when importing data.