SambaPOS 5 Bulk Product and Inventory Importer

Hey Q can I just check - you do not have to have a Menu Item for each portion do you? Having a product button which then can access all portions I feel is the best way to do portions with price definitions.

So above I have 1 product menu button, but I can click the Order to display other portions right?

That is the case most of the time. However, there are some instances where I do map a Product to the same Menu and Category more than once and set the Menu Item Portion. It is useful for Fast Menu selection so the user can skip Portion selection, which means the modifier screen does not appear, such as …

I am trying to cover all the bases here, so that is why I am making more changes to the CSV structure/export and the Import operation.

1 Like

May be relevant but referring to your image above that has the “Portion” column highlighted:

During LIVE testing I found I use a combination of Auto Select + Portion for Tap Beer. The “left field” item was Midstrength Beer used a default of MIDDY portion but the more favoured beer Peroni needed the defaut portion set to PINT!

Umm, also in the image I would set up “Americano” as 1 Menu Item Product, have Portion set to Single BUT also have another Price in Product List Editor for Double?

[EDIT]
@QMcKay I know “you’ve got this” but here is a typical sample of my Product List Editor.

Sample Menu :smile:

Umm, no. LOL. You cannot manage Inventory Consumption via Price Definitions. You need to use Portions. That is what they are for.

Current version has support for multiple Price Definitions. It works very well.

Or Portion, but I digress… this is becoming a crazy nightmare. I had this grande vision of being able to Export, then modify offsite, then Delete/Archive, then Import.

Well, no dice. We don’t have enough fields… yet? Sure, it could work, but the export would just be easier to backup a pile of DB Tables, edit them using SSMS in Grid mode, then restore. I mean, the CSV file would be unnecessarily complex, with 100+ fields/columns. It’s the things like Category Order, Menu Item Order, Button widths per Category, colors, heights, more colors, column counts, colors. And what about Menu Item Order Tags? Automation Commands, etc?

This is the Export SQL at this point - it joins 12 Tables. If I included every Column of all the tables, it would be ridiculous …

    SELECT 
     CAST(CAST(pr.[GroupCode] AS VARCHAR(8000)) AS TEXT) AS [ProductGroupCode]
    ,CAST(CAST(pr.[ItemType] AS VARCHAR(8000)) AS TEXT) AS [ProductItemType]
    ,CAST(CAST(pr.[Barcode] AS VARCHAR(8000)) AS TEXT) AS [ProductBarcode]
    ,CAST(CAST(pr.[Name] AS VARCHAR(8000)) AS TEXT) AS [ProductName]
    ,CAST(CAST(pr.[Tag] AS VARCHAR(8000)) AS TEXT) AS [ProductTag]
    
    ,CAST(CAST(po.[Name] AS VARCHAR(8000)) AS TEXT) AS [ProductPortionName]
    ,CAST(CAST(po.[Multiplier] AS VARCHAR(8000)) AS TEXT) AS [ProductPortionMultiplier]

    ,CAST(CAST(pp.[Price] AS VARCHAR(8000)) AS TEXT) AS [ProductPrice]
    --,CAST(CAST(pp.[PriceTag] AS VARCHAR(8000)) AS TEXT) AS [ProductPriceTag]

    ,CAST(CAST(mn.[Name] AS VARCHAR(8000)) AS TEXT) AS [MenuName]
    ,CAST(CAST(mc.[Name] AS VARCHAR(8000)) AS TEXT) AS [MenuCategory]
    ,CAST(CAST(mc.[MainButtonColor] AS VARCHAR(8000)) AS TEXT) AS [CategoryButtonColour]
    
    ,CAST(CAST(mi.[Name] AS VARCHAR(8000)) AS TEXT) AS [MenuItemName]
    ,CAST(CAST(mi.[Header] AS VARCHAR(8000)) AS TEXT) AS [MenuItemHeader]
    ,CAST(CAST(mi.[SubMenuTag] AS VARCHAR(8000)) AS TEXT) AS [SubMenu]
    ,CAST(CAST(mi.[ItemPortion] AS VARCHAR(8000)) AS TEXT) AS [MenuItemPortion]
    ,CAST(CAST(mi.[ButtonColor] AS VARCHAR(8000)) AS TEXT) AS [MenuItemButtonColour]

    ,CAST(CAST(ir.[Name] AS VARCHAR(8000)) AS TEXT) AS [RecipeName]
    ,CAST(CAST(iri.[OrderTag] AS VARCHAR(8000)) AS TEXT) as [RecipeItemOrderTag]
    ,CAST(CAST(ii.[Name] AS VARCHAR(8000)) AS TEXT) AS [RecipeItemName]
    ,CAST(CAST(iri.[Quantity] AS VARCHAR(8000)) AS TEXT) as [RecipeItemQty]
    ,CAST(CAST(ii.[BaseUnit] AS VARCHAR(8000)) AS TEXT) as [RecipeItemUnit]
    ,CAST(CAST(iri.[ItemType] AS VARCHAR(8000)) AS TEXT) AS [RecipeItemType]

    ,CAST(CAST(ii.[Warehouse] AS VARCHAR(8000)) AS TEXT) AS [WarehouseName]
    ,CAST(CAST(ii.[WarehouseCode] AS VARCHAR(8000)) AS TEXT) AS [WarehouseCode]
    ,CAST(CAST(ii.[Name] AS VARCHAR(8000)) AS TEXT) AS [InventoryName]
    ,CAST(CAST(ii.[BaseUnit] AS VARCHAR(8000)) AS TEXT) as [BaseUnit]
    ,CAST(CAST(ii.[DefaultBaseUnitCost] AS VARCHAR(8000)) AS TEXT) as [DefaultBaseUnitCost]
    ,CAST(CAST(ii.[TransactionUnit] AS VARCHAR(8000)) AS TEXT) as [TransactionUnit]
    ,CAST(CAST(ii.[TransactionUnitMultiplier] AS VARCHAR(8000)) AS TEXT) as [TransactionUnitMultiplier]
    ,CAST(CAST(ii.[DefaultTransactionUnitCost] AS VARCHAR(8000)) AS TEXT) as [DefaultTransactionUnitCost]

    --,CAST(CAST(itd.[Name] AS VARCHAR(8000)) AS TEXT) AS [InvDoc]
    --,CAST(CAST(itx.[Quantity] AS VARCHAR(8000)) AS TEXT) AS [InvTxQty]
    --,CAST(CAST(itx.[Unit] AS VARCHAR(8000)) AS TEXT) AS [InvTxUnit]
    --,CAST(CAST(itx.[TotalPrice] AS VARCHAR(8000)) AS TEXT) AS [InvTxTotalPrice]
    --,CAST(CAST(wh.[Name] AS VARCHAR(8000)) AS TEXT) AS [Warehouse]

    ,pp.[MenuItemPortionId]

    FROM [MenuItemPortions] po
    LEFT JOIN [MenuItemPrices] pp on pp.[MenuItemPortionId] = po.[Id]
    LEFT JOIN [MenuItems] pr on po.[MenuItemId] = pr.[Id]
    LEFT JOIN [ScreenMenuItems] mi on mi.[MenuItemId] = pr.[Id] 
    LEFT JOIN [ScreenMenuCategories] mc on mc.[Id] = mi.[ScreenMenuCategoryId]
    LEFT JOIN [ScreenMenus] mn on mn.[Id] = mc.[ScreenMenuId]
    LEFT JOIN [Recipes] ir on ir.[Portion_Id] = po.[Id]
    LEFT JOIN [RecipeItems] iri on iri.[RecipeId] = ir.[Id]
    LEFT JOIN [InventoryItems] ii on ii.[Id] = iri.[InventoryItem_Id]
    --LEFT JOIN [Warehouses] wh on wh.[Name] = ii.[Warehouse]
    --LEFT JOIN [InventoryTransactions] itx on itx.[InventoryItem_Id] = ii.[Id]
    --LEFT JOIN [InventoryTransactionDocuments] itd on itd.[Id] = itx.[InventoryTransactionDocumentId]
    --LEFT JOIN [Warehouses] wh on wh.[Id] = itx.[TargetWarehouseId]

    WHERE 1=1
    AND pr.[ItemType]=0
    AND isnull(pp.[PriceTag],'(REG)')='(REG)'
    AND isnull(iri.[ItemType],0)=0

    ORDER BY pr.[GroupCode],pr.[Name],pp.[Price] DESC, mn.[Name],mc.[Name]

I think my grande vision is out-of-scope for the purpose of the Tool, and does not make practical sense.

2 Likes

So you are suggesting “Portions” as shown in my first image (MIDDY | SCHOONER | PINT | JUG) are different to portions used in the SambaPOS INVENTORY? So sorry if this is the case as I was hunting this down worried about that possibility.

So what is the solution in that case - I known question is a side issue.

Yes my questions were really helping to either get to that conclusion or be of a benefit (if that makes sense?) . I think a basic 1:1:1:1 menuitem import still have some legs. In our case my team would just need to set up the Product Portions in the List Editor so still some use.

No. A Portion is a Portion is a Portion, no matter where it is “set”.

Product.Portion < this is where it is defined, and the “regular” Price is set for that Portion.
Price Definition Product.Portion < allows per-Portion “alternate” Pricing
Inventory Recipe Product.Portion < controls Inventory Consumption, no matter the Price

Take the Americano, Latte, Cappuccino, Espresso example. If you want to track Consumption, and by relation, Profit Rate, they must have Portions for Single, Double, Triple, Quad, etc, because Portions are the only way to separate Product Recipes according to the quantity of “ingredients” used.

Price Definitions do not help in the case of Consumption. But they will affect Profit Rate. The Price Defs, in turn, for HH, VIP, etc are set at the Portion-level, which is a good thing. Offering a discounted Price for a VIP Price Def, whether it is for the Portion of Single, Double, and/or Triple will not affect your Inventory Consumption (because the Product.Portion Recipes define that), but it will affect your Profit Rate negatively (it is calculated according to average Inventory Cost for the Quantity Consumed via the Product.Portion Recipe against the average Sell Price).

Maybe I am confused with your question/statement.

So to have an Americano with only a Portion of “Single”, and then define a Price Def for Double, Triple, etc, makes no sense. I can only track Consumption of the “Single” Portion.

Anyway, if that is not what you meant, sorry. It looks like your are using the different features properly. That is, you are using Portions (MIDDY | SCHOONER | PINT | JUG) and Price Defs (HH, Functions, Staff).

Mapping a Product to a Menu Item multiple times based on Portion is simply a good way to go for very fast Item selection when taking customer orders, because when you uncheck the Auto-Select, the Modifier Screen does not appear - so you don’t need to close it before selecting the next item. That is all I was trying to demonstrate.

Ok I think we are on the same page… :confused:

Where this all started is your example image above you have product “American” listed twice in the Menu and there will give you 2 separate buttons onscreen?

What I have done is listed the product as 1 button, but when pressed it brings up the portions. Some buttons have auto select and some not.

What has confused me is if you have defined price portions in the “Price List Editor” for Americano; Portion Single; Portion Double - should you not get a popup? Or if you have not used checkbox “Auto Select” fine, BUT if the user selected the Order they will get the Portion Screen as I have shown above (1st image from me).

[EDIT]
Yes looking at your image you have used fast menu and made a side note about this setup.

AH HA!

So I followed the tutorial for warehouse inventory tracking BUT are you suggesting that sales using portions from portions popups (think you call it modifiers) are not tracked? ← Nope this cannot be right?
If I sell a Cascade.MIDDY PORTION and have that Portion ALSO defined in Inventory RECIPES - hoping everything will be alright (if I have Inventory Warehouse Tracking on).

What is a portion popup? Can you show a screenshot of what your calling portion popup? Portions are portions… we also have Order Tags but I am confused it doesnt seem like your describing Order Tags.

Ok you get this screen when you select an Product:

I can show Product setup but sure you guy know all this top to bottom. Attached below anyway…

All I am concerned about is if I set Product Recipe to match the product portions above - that my Inventory Control & Consumption will work. Looking at the option “Find missing Recipes” it seems it will.

Ok so your just talking about portions… thats what I thought but portion popup was confusing me. No we do not call those modifiers we call them portions. What is commonly called modifiers is Order Tags

Yep was not sure what to call it! As you guys can see Inventory Management is my last frontier so I am still at a basic level. Give it a week should be OK ( or :sob:) …

Looking at your configuration you use order tags but mostly for HH automation. have you tried using them as actual modifiers? Order tags allow an even better fine tuned approach to inventory management.

No guilty of no idea about modifiers. Yes the HH & VIP tags was one of the first attempts at digging into automation. I have retained the Tags because my implementation allows for a % to be used for a Product Group - just for those venues that do not wish to type in every price/portion for discounts.

Order tags are the modifiers. Try it.they can be mapped in recipes even.

This might be a good starting point.

1 Like

@pauln, you are setting it up properly.

I call it the “Modifier Screen” because the screen that appears contains both Portions and Order Tags.

Again, for FAST MENU, I do not want to see the Modifier Screen. I want the Menu Button to choose the Portion automatically. It is much faster when taking orders for “simple” items.

I touch Americano Double Menu Button and it automatically puts Americano.Double Portion on the Ticket. No need to choose the Portion from the Modifier Screen. So then I click Cappuccino Triple and boom - Cappuccino.Triple appears. No Modifier Screen. FAST.

I can still change the Order in case of operator or customer error. Select the Order and the Modifier Screen appears, then I change the Portion for that item and close the Modifier Screen.


I have no idea if these Costs are correct. But this is the “jist” of using Portions, Recipes, and Price Defs …

2 Likes

Thank you guys - have the 3 superheros by my side, its a great feeling! Great Q, good to know I am on the right track as deadline is looming in 4 weeks.

We also have a back log of orders from other venues as they have found out what we are up to and the features we will provide. :cold_sweat:

1 Like

Good on ya! I have recently been approached to possibly become the Tech for a new POS “company” here. Not sure I want to have the extra work though, lol. Between my 2 Venues, Beta Testing, Forum support, etc, it really cuts down on my “social” time :stuck_out_tongue_winking_eye:

2 Likes

Hello,

I am using samba version 5.1.58 [DB 124]. I have downloaded and Install wampsever 2.5. I extracted the zipped files above into the “www” folder of the wampsever and edited the connection string as follows;

I selected Opera as my browser during wampsever installation, then i tried lunching the interface through Opera with “http://localhost/dbops.php

This is my result obtained;

Other Information;

  1. I am using windows 10
  2. Although wampserver is online, the service is not running and i cant start it

I would appreciate help from the forum. Thank You

I have never used wamp but it sounds like you might want to ask wamp forum if you don’t get a response here.

Edit: it looks like your running IIS and it’s probably attached to your port already.