Creating Supplier Price files helps to ensure that you are using the latest prices from your Suppliers (Payable accounts).
Introduction to Supplier price files
Request a Price file from your Supplier (Payable account)
The process to import a Supplier Price File
What to do if a Stock Item already exists
What to do to update only the Unit Description of existing Stock Items
Introduction to Supplier price files
Creating or importing price files from your Suppliers ensures that you are using the latest prices from your Suppliers when you create a Purchase Order. This helps you to ensure that you are being charged the correct amount when you receive invoices from your suppliers.
We suggest that you obtain a price file from your main suppliers to import to your Lentune system. Most larger Suppliers are able to provide their prices in a spreadsheet format.
When creating a Purchase Order for your Supplier (Payable Account), these prices will then be used, and when the goods are ordered, the invoice will show these prices.
If you use OCR (optical character recognition) to read and auto-populate your invoice lines, then the system can also compare the prices on the invoice to the correct pricing via OCR. Where there are price discrepancies detected, you can request a credit from the supplier to correct the overcharged amount.
You may import price files from multiple suppliers, although each file would be imported separately.
Request a Price file from your Supplier (Payable account)
First, request a file of prices from your Supplier. This would normally be in the form of a spreadsheet.
Once you have a price file spreadsheet from your Supplier, you will need to transform it to the correct format to be imported to Lentune. You can export a template for the Supplier Price File that will show the required format. Refer below for instructions.
Note: Price files can be imported to apply prices from your selected Supplier company-wide, or you might have pricing that applies only to a specific Branch and / or Project.
When importing prices for a specific Branch and / or Project, ensure that you add this detail to each record line in the spreadsheet so that it imports only for that Branch / Project.
The process to import a Supplier Price File
This process has three steps: export a template file or existing file for a specific supplier, update this file from your Supplier data, then reimport it to your Lentune system.
1. Export a template file
This will give you a spreadsheet file with the required headings for the format to import.
-
Go to Invoice Automation > Task > Supplier Price File.
-
Select the Supplier (Payable account) that you want to import a price file for.
-
Click Export Price file.
-
-
Export Template: to export a blank file with the column headings you need:
-
OR:
-
-
Export integrated price file: to export a list of Payable contracts that have been imported via the automated Integrated file for this Payable account.
-
This will produce a spreadsheet that will be placed in your downloads folder.
2. Update the spreadsheet
- Now you can update the file by cutting and pasting the relevant columns from your supplier data. Save your spreadsheet changes.
Some fields are obligatory and some are optional, as noted in the table below.
The optional fields can provide additional flexibility if you need specific prices per Branch or Project, or with specific Start and End Dates to apply to the price for a Stock item.
Table of Supplier Price File fields
Tip: If you don't put in an effective date it will be effective immediately. If you don't define an expiry date, the price will be valid indefinitely.
3. Import the file
- Go back to Invoice Automation > Task > Supplier Price File (where you exported the blank file).
- Select Import price file.
On the Upload screen:
Here you can see a list of compulsory and optional columns that can be included in your file.
- You have two options to delete and replace existing pricing contracts. You can select one or both of these options:
- A. Delete all existing contracts for payable account: Checking this field will delete all existing pricing for this Supplier and replace those prices with the new ones in your file. Leave this field blank to import new prices while still retaining any existing pricing.
- B. Delete all existing contracts for project: Checking this field will replace contracts but only if the Project matches.
TIP: If you do not delete all existing contracts, then your file to import should contain only the new records that you wish to import, to avoid duplications.
TIP: If you do not delete all existing records, and your data to import contains a record for an existing Stock item where the Description has changed, you will see a warning. Refer below for what to do. If something else, such as the price, has changed, then a new contract line will be created.
TIP: If you delete all existing contracts for project, and do not delete all existing contracts for payable account, then your file to import should contain only the updated records for the Project.
-
Browse to select your file, or drag the file to the import box.
-
Click Next. The stock items and prices will be displayed.
-
Click Next. The import will now be processed.
-
Click Close.
When you import the Supplier Price File, all Stock Items will be created in Lentune's Stock system.
What to do if a Stock Item already exists
When a Stock item / price is imported and the same Stock item already exists but the Description has changed, then a warning message will be displayed and the import will not proceed for any items in the file.
There is an option on the left of the screen to download this error file. This will download a spreadsheet that will show you which Stock item/s you need to investigate.
- Click Download error file to download the file, then locate and open the file.
The Stock item/s that already exists will show with Error in the Status column (Column A) on the left of this spreadsheet. Any Stock items that are ready to import will show as ReadyToImport in the Status column, however, they will not yet be imported.
- Go back to your original file to import.
- Add a new column (column N) called StockAdjustmentAction.
- In this column, for the record that is producing the error, add either:
- Ignore: This will apply this pricing to the existing Stock item and will not update the description, or:
- Update: This will apply the pricing to the existing Stock item and will also update the description.
However, if the item is different to the one that already exists in the system, but has the same Stock code, you must replace the Stock code for the new item to avoid duplicates. In this case:
-
- From the downloaded error file, copy the automatically generated code from the StockAdjustmentActionStockCode column (column Q).
- Copy this Stock code to your import file, to the Stock code field for that record.
- Repeat the import process. This will create a new Stock item with the updated Stock code. All other items will also be imported.
What to do to update only the Unit Description of existing Stock Items
If you've made a mistake in your spreadsheet to import, e.g. entered an incorrect measure for the Unit Description, you can correct the spreadsheet and re-import it. (This is similar to the above section for re-importing when a Stock Item already exists.)
- Go back to your original spreadsheet file to import.
- Add a new column (column N) called StockAdjustmentAction.
- In this column, for the records that have the wrong Unit of Measure, add either:
- Ignore : This will apply this pricing to the existing Stock item and will not update the description, or:
- Update : This will apply the pricing to the existing Stock item and will also update the description.
Note: The Unit Description is a feature of the Stock item, not the contract.
Was this helpful? If you require further assistance, or would like to contact us about this article, please email us at support@lentune.com.
Thank you. Your feedback is appreciated.
Last updated: 16 November 2022