Using the export file | Using the import week file | Recommendations | Common errors
Spare parts data can be imported into the Alldevice CMMS environment for two purposes, to add new spare parts or to update (mass) data of existing spare parts, or both at the same time.
In order to import the data, we first need to get a sample file to add the data to, or export the data of existing spare parts, make changes to it and then import it back.
It is not possible to import an Excel file in the standard format into the Alldevice application.
Using an existing export file
To retrieve the spare parts data export file, click on the “Spare parts” button in the top menu bar. If necessary, set the filters and then click on the “Export” button in the top right corner of the page.
NB! If you do not see the “Export” and/or “Import” button, your user does not have the rights to perform the corresponding operation.
In the exported Excel file, you will see all the columns of data related to the stock card that you have made visible in the table.
Make changes in the desired fields on the desired rows and save the file.
Remember that the file contains all the data (columns) in the reserve card, but not all of them are importable/changeable, but informative (these fields are ignored during import).
The number and order of columns in the exported file depends on how you have configured the view of the reserve table.
- ID: Spare part card database identifier (this number cannot be changed or entered by the user).
- Code: spare part code (changeable)
- Item name: Name of the spare part (modifiable)
- Category: spare part category (modifiable)
- Manufacturer: spare part manufacturer/brand (changeable)
- Dropped below min stock: Date on which the quantity of the specified stock item fell below the value of the minimum quantity (informative).
- Last used: date of last use of this spare part (informative).
- Minimum quantity: the minimum quantity of a stock item you want to keep in stock (changeable).
- Stock: quantity in stock (informative) 1
- Reserved: quantity of spare parts already reserved for work orders (informative)
- Available: quantity of spare parts. Stock – Reserved = Available (informative).
- Km%: Percentage of VAT on sales price (modifiable)
- Sales price (0% km): Sales price of the spare part(changeable) 2
- Sales price (+km): Sales price of the spare part including VAT (informative). 3
- Header: Quantity of spare parts in the header (informative)
- Ladu 1: Spare parts in a warehouse created by a bulk customer (informative) 4
- Ladu 2:…
- Cost: Acquisition cost of the spare part (variable or informative). 5
- EAN: Spare part barcode (changeable)
- Additional field 1: Additional field created by the customer on the reserve card (editable). 6
- Additional field 2: …
NB! If your export file does not contain any of the fields listed above, you did not have these columns activated (visible) in the table. You can read about activating columns HERE.
IMPORTANT TO REMEMBER!!!
- Do not modify the headings of the columns in the table.
- Do not change the number in the “ID_” column (or the missing number).
- If the ID field is filled in, the import with the referenced ID will modify the data in the database. If you change the ID number, the import may overwrite the data in the stock part that you did not want to change.
- If the ID field is empty, a new stock is added to the database.
- It is not possible to delete backups using Excel import. If you delete some rows from the exported file and import the file back into Alldevice, the rows removed from the table will not be removed from the database.
- Spare part categories and manufacturer’s names must be pre-entered in the application.
- A maximum of 3000 rows can be imported at a time
To import the modified file, click on the “Import” button in the right corner of the page. On the page that opens, click on the folder icon and locate the Excel document you wish to import.
Once the file is selected, it is uploaded to the server and placed in the “Shopping Cart” for pre-import review/checking.
If there are any errors in the data, a red triangle icon will be displayed in front of the corresponding row (all rows with errors will always be displayed at the beginning), and the invalid data field will be marked with a red dotted line (in the example “Category” above). Errors will be displayed if the corresponding manufacturer or category has not been created in the Alldevice database, or if a spelling error has occurred, or if the data is in the wrong format (for example, you are trying to import text into the number field).
To correct errors, click on a field and make the changes or select the option (for a manufacturer name or category). You can also make corrections in the source file and upload it again, but don’t forget to click on the “Remove all” button first.
If you wish, you can also remove the incorrect row from the table. To do this, right-click on the row and select “Delete”.
If there is no manufacturer or spare part category requested, there is no need to suspend the import. You can go directly to the settings and add the items you want and then return to the spare parts import page (if you have closed the page in the meantime, the data will still be remembered and when you open the page, you can continue where you left off). To see the changes, click the update button at the bottom of the page.
If the table contains at least one incorrect row, the import will not be allowed to complete. You will also receive an error message and the import will be terminated immediately (no rows will be imported).
When all the rows are nicely “green”, press the “Start importing” button.
Done 😊
Using the import sample file
To import new spare parts, you can use the spare parts export file and add the new lines to be added at the end of the file. You can also delete the lines above and fill the same file as described above, but you can also download the sample import file.
To do this, click on “Spare Parts” in the top menu bar, select “Import” in the right corner of the page that opens, and from there select “Import sample file”.
When you open the sample import file, you will find three worksheets (Sheets).
- Spares – This is where you enter the data you want to import.
- Categories – Here you will find a spiked list of all the device category names that have been entered in the Alldevice application. If necessary, you can copy the category names here for use on the Spares page.
- Manufacturers – Here you will find the names of all the manufacturers entered in the Alldevice application.
The columns on the import file “Spares” page:
- ID: Spare part card database identifier (this number cannot be changed or entered by the user).
- Code: Spare part code
- EAN: Spare part barcode code
- Title: Reserve part designation
- Category: Spare parts category
- Manufacturer: spare part manufacturer/brand
- Minimum quantity: the minimum quantity of a stock item you want to keep in stock.
- Cost: Acquisition cost of the spare part 7
- Sales price (0% km): Sales price of the spare part 8
- Km%: Percentage of VAT on the sales price
- Auxiliary field 1: Auxiliary field created by the customer on the reserve card. 9
- Additional field 2: …
Once the file has all the desired lines filled in, save it and import it into the Alldevice application as already described above.
Recommendations
If you use an export file for importing, save the file you want to modify with a new name, so that in case of errors the original file can be re-imported and restored.
If you are using the import feature for the first time, try a few lines first to understand how the system works. If something goes wrong, it’s easier to correct a few rows/backups in Alldevices than hundreds or thousands of backups.
Common errors
- If you imported new spares into Alldevice and then discovered that something was missing or wrong, do not make changes/repairs to the same file and do not import the file again. If you do, you will get duplicate data. This is because the original file does not contain a spare part ID and the application treats it as a request to add new spare parts.
If it is necessary to correct the imported data, export the spare parts, modify the data in this file and import it back into the application. In this case, the ID_ column of the table will also contain the correct database ID numbers, which will tell the application that you want to update the device data and not add new devices. - Do not use formulas in the import file.
————————————————————————————————————————-
- Changes to stock levels are only made through stock movements. You can read more about this HERE. ↩︎
- The VAT and the sales price are visible when the display of the sales price on the spare parts card is activated from the spare parts module functionality. This function is targeted at service providers. ↩︎
- The VAT and the sales price are visible when the display of the sales price on the spare parts card is activated from the spare parts module functionality. This functionality is targeted at service providers. ↩︎
- These columns are displayed if you have added additional stores to the Alldevice environment. The number of columns depends on the number of warehouses you have inserted, and the names of the columns correspond to the names of the warehouses you have inserted (Warehouse 1, Warehouse 2 are shown as examples). ↩︎
- The cost is importable/convertible if the stock of a given product is zero. In this case, the cost has an informative meaning (estimated cost). If the quantity of spare parts in stock is different from zero, the cost of the product will be based on the purchasers’ prices (weighted average).
↩︎ - Custom fields for spare parts entered by the client in the Alldevice environment. Read more HERE ↩︎
- The cost is importable/convertible if the stock of a given product is zero. In this case, the cost has an informative meaning (estimated cost). If the quantity of spare parts in stock is different from zero, the cost of the product will be based on the purchasers’ prices (weighted average). ↩︎
- The sales price and VAT are visible if the display of the sales price on the spare parts card is activated in the spare parts module functionality (this feature is targeted at service providers). ↩︎
- Additional fields for spare parts entered by the customer in the Alldevice environment. Read more HERE ↩︎