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. Adding packages and pricing data to these ingredients is done with the "Ingredient's Packages" file, which is discussed in this article.
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 3 sections: Ingredients, Packages, and Supplier/Pricing:
- 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.
- 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. If the 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.
- 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 the first row, we are creating an ingredient without any packaging.
- The second, third, fourth and fifth row 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 also that in the third and fourth row there are spaces before the ingredient's name to represent a tree structure for packages that share the same base package (We call base package the lowest order package. In the example above, Ingredient B has two base packages: 25 cl and 33 cl. It also has a 6 x 33cl package and a 24 x 6 x 33 cl package). Those spaces are generated automatically by our export and they are not seen in Apicbase.
- 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 rows seven and eight, the same ingredient has a package linked with suppliers. The base package in the sixth row has no supplier so the 'Supplier Info' part of the sheet is empty;
- Note also that in the seventh row the price is given in KG, while in the eighth it is given for the whole pack.
There a few important things you should know before start working with our excel:
- 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.
- 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.