The easiest and fastest way to set up your ingredient's packages and pricing for ordering, inventory and sales analysis, is by using our "Ingredient's Packages" Excel template. This article shows you how to use this template.
For a Dutch version of this article, click here.
There are two excel import files that manage ingredients data: "Ingredient" Excel import and "Ingredient's Packages" Excel import. The "Ingredient" Excel import is used to create ingredients with general information, allergens, nutritions, etc. Click here if you want to read up on this file.
Adding packages and pricing data to these ingredients is done with the "Ingredient's Packages" file, which is discussed in this article. If you want to learn more on importing ingredients, read our extensive guide on this topic.
Go to your ingredient list by clicking: Ingredients > Import > Ingredient's packages
On this page, you can download an empty template (recommended if you are creating new ingredient packages) or a sheet with all your active packages filled in (recommended if you are updating existing ones).
Now that you have downloaded the template, you are ready to start. Each row in the Excel-file is divided into 4 sections: "Internal", "General information", "Packaging info", and "Supplier info":
- Apicbase ID: This is a unique ID that Apicbase generates when a new ingredient is created. If you use a filled-in template, the Apicbase ID will be here. If you are creating new ingredients and packages then just leave this empty. The ID will be created automatically when you upload the file. You can find more info on the Apicbase ID in this article.
- Name (mandatory): The ingredient's name.
- If an Apicbase ID is given and you change the name in the template, the new ingredient name will be used in Apicbase.
- If no Apicbase ID is given, the importer tries to find an ingredient with the given name and update that one.
- If there is no Apicbase ID and there is no match on the name, a new ingredient will be created with the given name.
Using the two fields above, the importer has established which ingredient is being created/modified. To do the same for the packages, you need to fill out at least the columns 'quantity' and 'unit'.
- Quantity (mandatory): The quantity of the package. For example, for a bottle of 33 cl, the quantity 33 is filled in. We also support a shorthand notation for combined packages (e.g. 6 x 33 cl). You can find an example of this at the end of this article.
- Unit (mandatory): The unit of the package. It should be one of the units supported by our software (g, mg, kg, ml, cl, l, and piece)
- Gtin (optional): Global Trade Item Number, a global identifier such as EAN or UPC. Generally, this is the barcode and it should be a unique number per package.
- Package type (optional): The type of the package (e.g Can, Bottle, Box, etc.);
- Stockable (optional):
- 1: The package is stockable
- 0: The package is not stockable. (If left empty it will be set as stockable.)
- Piece (optional): Fill with 1 if this package is the one you would refer to us a "piece" when used in a recipe. for more information on the use of the "piece" setting, read this article.
Now we have identified the ingredient and added a package for it. For the packages you can order, you have to enter the supplier and pricing data.
- Supplier (mandatory to create/update supplier package): Name of the supplier. (Be sure to use the exact name of the supplier as entered in Apicbase. Read this article to learn how to check the supplier's name in Apicbase.)
- Supplier # (optional): The supplier article number. This is the unique number that the supplier uses to identify this article. Although it is optional, we strongly recommend filling out this value, especially when using our ordering module as the supplier article number is the only way the supplier can identify the article.
- Price (optional): Price of the item; This can be either the price for the package (e.g. €5 for a 5kg pack of sugar) or the price per unit (e.g. €20/kg of salmon).
- Price per pack (optional):
- 1: if the price in the former column is the price per pack.
- 0: if the price in the former column is the price per unit.
- Price unit (optional):
- Leave empty if the given price is for the whole pack (1 in the previous cell).
- Otherwise: fill with the corresponding unit. e.g. "kg" if the price is per kilogram.
- Orderable (optional):
- 1: The package is orderable through Apicbase
- 0: If this package is not orderable through Apicbase.
e.g. You buy 6 packs of spaghetti and each pack contains 200g. In this case, you have two packages, the base package of 200g and then a package of 6 x 200g. The 200g package would set "orderable" to 0 and the 6 x 200g package would set it to 1. Because the only package you can order is the 6 x 200g package.
In the sheet above we have a few common examples. Let's assume we are creating those items (ignore the Apicbase ID filled, since normally during a creation that column would be empty)
- In row 3, we are creating an ingredient without any packaging.
- In rows 4 to 7 we are referring to the same ingredient with different packages. Note that the 'Supplier Info' section is empty, which means these packages are not linked with any supplier and therefore can not be ordered. Note that in row 4 to 6 there are packages that share the same base package. We call base package the lowest order package, in the case the 33cl can. Ingredient B another base package: the 25 cl can in row 7.
- Ingredient B has the 33cl package on the second row defined as 'piece', so every time a piece of that ingredient is used, the recipe will contain 33cl;
- In row 8 the package has no supplier so the 'Supplier Info' part of the sheet is empty. This package will not be orderable. In rows 9 and 10, the same ingredient has a package linked with two different suppliers.
- Note also that in row 10 the price is given in KG, while in the 9 it is given for the whole pack.
There a few important things you should know before start working with our excel:
- If the entered supplier does not exist in your library, he will be created with the entered name. Be sure to use consistent names for your suppliers. If you use another name or misspell the name, a new supplier will be created. This field is case insensitive so the supplier "Bidfood" will match on supplier "BidFood". Be sure to fill out the rest of the supplier information after you have uploaded the template. Please read this article if you want to learn how.
- There is also a 'shorthand notation' to create packages. In the example above, if you are creating the 10 x 1 kg or the 6 x 33 cl but you don't really want to add information like gtin, package type, etc or link it to a supplier, you could simply add the lines with the 10 x 1 kg and 6 x 33 cl. That will automatically create the packages for 1 kg and 33 cl.
- Changing the name of an ingredient that is shown multiple times in the sheet, can be done by changing the last row of this ingredient. The same works for packages. In the example given above, the package 10 x 1 kg of Ingredient C is shown twice since it is linked with two suppliers. If you want to change its gtin for example, you can change in every row of course but the only one that will actually make difference will be in the last row that it shows up.