SambaPOS 5 Bulk Product and Inventory Importer

[QMcKay]

@pauln

add “Price Definition”?
2 exact records one column only changes on the subsequent record?

Hmm. I think it would be easier to process if the Header Line contained the Price Def Name/Tag so we could keep data to 1 row. Something like this:

In fact, it would be even better if we could move all of those columns to the end of the Row so that we know that anything beyond “SellPrice” is another Price Definition.

---

@pauln

can you use the import to update existing pricing if the record already exists?

Not as-is. It skips existing Products. I can add an option for updating existing records.

So here is a question regarding that: Is the “update switch” Global? Or should we have a spec on each Row in the data? ← add yet another column to the data, and only update the record if “Y”, else skip.

[pauln]

(You know the sad thing here is I cannot like a post more than once haha!)

@QMcKay

In fact, it would be even better if we could move all of those columns to the end of the Row so that we know that anything beyond “SellPrice” is another Price Definition.

PERFECT! - Such a clear and precise “take” on ideas.

@QMcKay

So here is a question regarding that: Is the “update switch” Global?

Yes tricky, well I use the Customer Update Jscript routine than just Adds or Updates records - all fields.
In this case we really wish to update a new price only BUT who is to say we also wish to update the description?
Most users might desire to build integrations and hence require continuous updates so that may be our answer - anything in the file…

EDIT:
Umm seeing your update column above, maybe that column could morph into something like Update, Add, Delete, Archive…just saying leads to better design.

[QMcKay]

@pauln

I use the Customer Update Jscript routine

Funny you should mention that, because I was seriously considering abandoning updates to the PHP script and move everything to JScript. There are Pros and Cons …

• JScript is “internal” and requires no Webserver/PHP setup.

• JScript could “freeze” SambaPOS until the import is finished, especially for large CSV files, unless it is invoked with Execute Script set to Run in Background.

• JScript requires you have SambaPOS running to be able to use the “Import Function”, whereas PHP does not… PHP can be run either way, even remotely.

Thoughts? I really do not want to “maintain” two different methods of PHP and JScript.

[pauln]

Well this is one of my favourite topics so just tell me to shut up if required

@QMcKay

JScript is “internal” and requires no Webserver/PHP setup.

I always have the focus of consumerism for @emre and SambaPOS (bigger, stronger) better for us so the “Average Joe” needs to be able to deploy SambaPOS - this is a plus.

@QMcKay

JScript could “freeze” SambaPOS until the import is finished, especially for large CSV files, unless it is invoked with Execute Script set to Run in Background.

I have bench tested this and VERY surprised how fast this is! Also with routines like “Application Start”, “Trigger”, and “File Monitor” we can load when these imports occurs. So feel this is neutral to positive.

The only issue I found was when I dropped 10 CVS files containing Customer Updates and Transaction Documents - all files were processed simultaneously by 1 workstation except the last file? Again, I have Application Start Process that will pick up any let over updates. Also these types of files are created one by one so I was trying to break it anyway…

@QMcKay

JScript requires you have SambaPOS running to be able to use the “Import Function”, whereas PHP does not… PHP can be run either way, even remotely.

Yes, this fact I spent a day figuring out Sukasems Loop - calling another Loop () but now done works very well and if any workstation is started, data is picked up during Application Load (Blue Screen). File Monitor then does the rest throughout the day.

That is how I have managed without PHP.

The main issue is the requirement for more API’s to save SQL as again the average Joe may be offended by the requirement for SQL joins etc.

EDIT:
Oh Emre mentioned a new feature - ability to call a RULE(?) from a Script - could be a game changer as well.

[QMcKay]

@pauln

Emre mentioned a new feature - ability to call a RULE(?) from a Script

I seem to remember something like that as well. Can you point me to that post?

[pauln]

Constraints with Data.Get() and Data.Set()

Constraints gets evaluated once before executing actions so a previous action won’t affect next action’s constraint. That won’t change. There is no need to create a constraint for execute script as you can pass constraint parameters to script and make that constraint check inside script. On next update you’ll be able to execute automation commands inside script. As we have a method to read / set local settings inside script there won’t be a need for constraint for execute automation command ac…

Can’t wait!

[jay007]

I tested this but it does not add the cost price. Everything else works fine. The ability to update existing product prices and cost prices would be a great feature.

[QMcKay]

@jay007

it does not add the cost price

It isn’t designed to update the CostPrice. That column in the CSV data is used for an Inventory Transaction Purchase Document so you can set an initial stock level. Really, it should be renamed, but instead, I will add columns for:

DefaultBaseUnitCost
DefaultTransactionUnitCost

The next version of this Tool is shaping up really nice with a lot of enhancements, so keep the suggestions coming … so far, I am working on these changes:

 v124b (2016-05-20) QMcKay
  - Inventory Default Cost columns added:
     - DefaultBaseUnitCost
     - DefaultTransactionUnitCost
  - supports multiple Price Definitions
     - create a Column Header in format of "PD:PriceDefinitionName/PriceTag"
     - example: PD:Happy Hour/HH
  - supports data Updates
     - previous versions would skip import of existing Products
     - this version allows Updating data of existing Products  
     - set the "Update" Column to "U" for Products that you want to Update
  - supports re-arranging columns to whatever suits you best
     - NOTE: Column Header Names are case-sensitive and must not be changed

[QMcKay]

Regarding the Data Update feature, I think we need to decide which portions of the data is subject to being updated.

We cannot update the Product Name, since we use that to search for the Product to be updated. Maybe we can mark the Product as something to be Deleted (D), or Archived (B), and have a new non-existing Product row in the data if we want to “Update the Product Name”. Still, I think this should be used with caution since it will affect Cost/Profit calculations since the Product is technically “new”.

If a Product is to be “Archived”, does that mean we simply remove it from the Menu, but leave the Product intact? I hope so, because …

I think “Deleting” a Product will be a PITA, and probably should not be done anyway for historical data integrity, so I will leave that option for a future Tool version.

Caution should also be used when updating Inventory Units (Base, Transaction, Additional Units, and their respective Parent Units and Multipliers.) since they will also affect Cost/Profit Calculations.

I think we can safely update things like:

Menu Item Name
Product Portion Prices
Product Price Definition Prices
Inventory Item Default Costs for any Unit
Recipe Inventory Item Quantity

We can also consider the possibility of setting the Product ItemType. Classically, there was only a single ItemType, but with recent Inventory enhancements, we now have the classic Product that you sell which is ItemType 0, and a new Inventory Product which is ItemType 1.

[jay007]

I know from my stock list if the product name is different it would be added as a new item. The stock list would have an item code or barcode. If item code or barcode is different it is always a new item also. I use the item code from the stock list in the barcode field in samba if the item has no barcode. In retail all items will have an item code or barcode. You could have script check if barcode is the same update and if different insert.

[silentbob]

Can you add barcodes to the list of things we can update?

It would be nice to be able to export all products and menus to the csv file for editing off premises.
The updated file could then be imported to overwrite existing product prices,barcodes etc.
It would be easier alternative to taking full database backup and restore the following day for staff with no MSSQL knowledge.

[pauln]

@QMcKay

We cannot update the Product Name, since we use that to search for the Product to be updated. Maybe we can mark the Product as something to be Deleted (D), or Archived (B), and have a new non-existing Product row in the data if we want to “Update the Product Name”. Still, I think this should be used with caution since it will affect Cost/Profit calculations since the Product is technically “new”.

Yes absolutely agree and shows, speaking for myself, that I should take your guidance on UPDATES. I certainly do not have the experience in the “Inventory Side” of Samba to make a informed comment.

Question: Can the tool be used if SambaPOS MenuItems & Products have only be setup and you then wish to extend functionality into Inventory? I not suggesting that this should be a capability but just refer to a single case . I guess many newcomers start with this scenario…

Love how it is shaping up.

[QMcKay]

@silentbob

It would be nice to be able to export all products and menus to the csv file for editing off premises.

Exporting will be implemented in the next version!

---

@pauln

Can the tool be used if SambaPOS MenuItems & Products have only be setup and you then wish to extend functionality into Inventory?

Yes, by way of UPDATE switch. But it updates everything from Product to Menu to Inventory …

Coming very soon, lots of new functionality, just bug-testing …

v124b (2016-05-24) QMcKay
  - NEW parameter to set CSV File PATH
  - NEW parameter to allow Upload of CSV File(s)
  - NOTE: several Header Columns have been renamed, and others have been added, however ...
  - NEW function to READ CSV file and output some data without importing
     - contains CSV Header Column Help
     - shows valid/invalid/missing Columns
  - NEW function to EXPORT DB Product Data to CSV file
     - Export prompts to save CSV data locally
  - choose Import CSV file by Browsing file system on Server
     - if no file is chosen, it defaults to $myImportFile parameter
  - choose Database by picking from list of available DBs on Server
     - works if $myUser has enough privelages to read 'master' DB
     - defaults to $myDB parameter
  - supports re-arranging columns to whatever suits you best
     - NOTE: Column Header Names are case-sensitive and must not be changed
     - NOTE: some Column Header Names have been changed
  - supports multiple Price Definitions
     - create a Column Header in format of "PD:PriceDefinitionName/PriceTag"
     - example: PD:Happy Hour/HH
  - supports Product/Menu/Inventory Updates/Archiving via Product Name or Barcode
     - previous versions would skip import of existing Products
     - if Product does not exist, it will be Inserted regardless of the following flags
     - to Update data of existing Products
        - to UPDATE, set the "Update" Column to "U" or "UBC":
           - U   : search and update by Product Name
           - UBC : search and update by Product BarCode
     - to Archive existing Products by re-assigning them to an "ARCHIVE Menu"
        - to ARCHIVE, set the "Update" Column to "A" or "ABC":
           - A   : search and archive by Product Name
           - ABC : search and archive by Product BarCode

[jay007]

@QMcKay

to UPDATE, set the “Update” Column to “U” or “UBC”

If update is set will it add the product if the product is not already there. The reason asking, I could get a new stock list with 10,000 items. The only thing on some items that would change is RRP Price and Cost Price. This stock list would also have new items also. Do you run the script twice? One for update and one new items.

[QMcKay]

@jay007

If update is set will it add the product if the product is not already there.

Yes, if the Product does not already exist, it will be inserted, regardless of the Update column setting.

The only time this does not seem intuitive is if the Product does not exists and you have Update set to Archive (A or ABC). It ignores that setting as well, and inserts the Product into the Menu you have specified instead of placing the Product in the “Archive Menu”.

[QMcKay]

New version 124b has been uploaded. See original post in the topic to download.

NOTE: several Header column Names have been changed/updated.

Ensure your Headers are correct.

An easy way to check this is to use the “READ FILE” operation. It will scan your CSV file and report on missing or invalid Headers.

Let me know if there are any issues…

[QMcKay]

I am already making more updates to make it more compatible with non-Retail applications.

Initially, the Tool was developed for Retail Products, Menu Items, and Inventory. And it still work fairly well for that. But it is sequenced and checked in such a way that most things need to have a 1:1:1:1 mapping, as in:

Product.Portion : Menu Item : Inventory Item : Recipe

This works well for most cases, even if you specify multiple Portions, etc.

The place where it is falling apart right now is in cases where a Product is mapped to more than one Menu, Menu Category, and/or Menu Item Portion. I have already made changes to accommodate a few of those cases, but I still need to make some more changes to the way duplicates are detected in regard to Menu Items.

Right now, duplicates are detected solely on the Product.Portion Level, so it will skip inserting an Item at that point - that is, it won’t look as deep as the Menu Item. Instead, it should go on to check if the Menu Item for a Product is specifically mapped within more than one Menu or Menu Category. This could also help in Retail-based applications.

In any case, v124c will be coming out to accommodate these scenarios. I believe then it will then be an excellent Tool for both types of application, and I might even start using it myself!

In the mean-time, please let me know if there are any other features that you would like to see, or if you see any issues, because after the next version, I will shelf this project until it becomes incompatible with SambaPOS DB changes.

[pauln]

@QMcKay

But it is sequenced and checked in such a way that most things need to have a 1:1:1:1 mapping

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?

[QMcKay]

@pauln

you do not have to have a Menu Item for each portion do you?

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.

[pauln]

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