Integration with third party app (The Drink Exchange)

One of our clients who is running a bar using SambaPOS V5 is keen to integrate The Drink Exchange software ( http://thedrinkexchange.com/). It allows dynamic pricing of drinks based on demand and by utilising TVs around the bar showing current prices of drinks, it creates a stock exchange like buzz and thus increases business and profits.

For integration they need to be able to able to monitor sales coming in on the POS, and also be able to update prices of drinks on the POS. As a quick example: assume 5 Bud Lights are sold, the third party software simply need to know that 5 were sold and then they adjust the price accordingly. They are not actually modifying any inventory or quantities. The only thing that they adjust is the pricing. When the prices change on the Drink Exchange it’s reflected on the TV’s and on the POS in real time.

I have read through on the forum already about the JScript API and also looked at other integrations but I am unsure about an integration being able to access data within SambaPOS (instead of SambaPOS controlling the interactions). I am sure we can access this information directly via the database but was hoping to see if there were any better options available?

So my main questions are:

  1. To monitor sales - is there any way this can be done from an external application via an API? Or, if it has to be done by monitoring database tables, what is important to check?
  2. For the third party app to update prices - is there a way this can be done via API or similar? I read before it is not ideal to do this directly on the database, but if this is the only option we have, is there anything I need to be aware of?

Thanks! :smile:

I would suggest ask a for more details about how the Drink Exchange would like to have the sales data.
But my guess is that a custom browser printer might be the best method for exporting the data.

It would depend allot on how that system is expecting to receive the sales info?
Presume they have an API? I imagine the simplest solution if they were to accept sales data sale by sale would be to post the quantities from each ticket depending on how frequently it is expecting the data.
You can use SQL calls within the scripts so calling the data is certainly possible in many ways but depends on how they are expecting to receive?
Might need a product tag as a reference/PLU if they base on PLU/non product name reference.
Changing the prices is a different matter
 have not seen a script helper for updating the product price HOWEVER I would think you could achieve if the system can feedback price updates through the API and actual product price is not script-able maybe we could get a api helper to update product tag field so could have product tag for ‘exchange-price’ which a order added rule could update order price to ‘exchange-price’ on order added


No API support for Products in SambaPOS, yet.

You need to find out how they want the data (JSON, XML, CSV, etc), and perhaps use the Data Export feature in SambaPOS. I would definitely start looking into that for your purpose.

So I think right now the main issue is about changing the price


I’ve been testing ways to change the price, and also how it works within the lifespan of a ticket. Say a ticket is opened and customer buys a drink, ticket is submitted. Then later, customer buys another of same drink again, but now the price is different. I checked and SambaPOS works ok with this (i.e. if I change price within Manage >Products using SambaPOS). However, I also did some experimenting by changing the price in the database, and have found that sometimes it works and sometimes doesn’t. One time I changed price in database, added item to ticket and the new price was used, but then tried again to change price directly on database and it was ignored. Only way I could get SambaPOS to acknowledge the price change was either to go to the Manage > Product (only need to view it, not even save), or actually quit and reopen SambaPOS (logout and login didn’t reflect change). I can’t tell if there is a timed refresh or why it worked one time and not again.

See example below:

So if I am just letting The Drink Exchange handle price updates via database directly (which might end up being the most straightforward way), how can I ensure SambaPOS is using the exact “real-time” price stored on the database when adding a new item to a ticket? Is there an additional “refresh” I can add within the workflow of adding an item to a ticket? I think while ticket is open and items being added is not so important, but at the point of creating / reopening ticket is the important stage where SambaPOS needs to refresh prices from the database, that currently it appears is using some sort of cached price at least some of the time.

Also, my client will just use The Drink Exchange for pricing at certain times on some days, so I think it is best to keep a menu price the same, then let The Drink Exchange update a separate price. I was thinking I can just use a Product Tag in a similar way as showing in this tutorial VIP & Happy Hour (or timed special) Discounts without Triggers. But one thing that I am uncertain about is for storing different prices on products, what is different from using Product Tags or Price Definitions? What is the different features / advantages / disadvantages of each as it appears I could use either?

Saying that though, they have told me their system can also retain a standard price on items and revert back that price when their feature is turned off - so in some point, it might be ok just to go with that but I do also like the fact of retaining the menu price (“original price”) on the POS as well.

@QMcKay yeah you are right Data Export is likely flexible enough to export new tickets to The Drink Exchange, so I will try to find out how they would like the data and I am sure we can do that way.

In your Rule:

Use the Action for Execute Script to invoke SQL to make the update.
Use the Action for Refresh Cache immediately after the update.

1 Like

I think I don’t need to do this though because The Drink Eschange software would be separately updating the database itself when it decides to change the prices.

So I think I just need to add the action to Refresh Cache each time a ticket is created or reopened, right? That would ensure the prices used are exactly those stored in the database.

No don’t do that
 That will cause everything reloaded from database.

Better don’t change anyting on sambaPOS side. Create Update Order action and configure it to change order price. Call it every time a new order line added, pass product name with a CALL tag, inside script read price from wherever you want and send it back.

4 Likes

I am testing the Update Order action as @emre suggested and have a script created that will eventually query a file containing JSON of the updated prices that they will update locally (right now I am just passing a value back from the script to test functionality).

However they have suggested I provide them with itemID to identify products, rather than a product name. Is there any way I can pass the itemID from the database, or am I best to just create a Product Tag for ID and create them myself then pass to the script using {ITEM TAG:ID} ?

You could certainly call item Id from the menuitems table with SQL script if nothing else.

Yeah but I am using CALL within the rule and have to pass either the product name or product tag to the function (as I can’t access the database id from there). In that case I would then need to do a lookup using SQL to get the database id to then lookup the price from the JSON file. So there is an extra step needed.

Also for reporting the quantities sold back to The Drink Exchange, I would need to get the database id again by SQL in the report.

So I was really hoping there was a way I can get the database id directly to then pass to the function from the rule. But it looks like its just easier to create a Product Tag (as then I can just use all built in features in the script and report).

Yes using Product Tags is a great idea for such mapping
 But Creating new products will be an issue as user will need to type correct id for related tag. How you’ll solve that?

Actually the user creating new products and having to set the new product tag was a major concern. Now I have decided to just use the database id (of MenuItems table).

For updating price, I am just passing the MenuItemName to a script, which uses an SQL query to get the database id, which I will then lookup in the JSON file to get the price. I still have to do all the JSON part.

For exporting quantities sold, I am using a Data Export report with REPORT ORDER DETAILS, where I found I can use M.Id to get the MenuItems database id.

1 Like

We have {ITEM ID} tag to read id of menu item assigned to order.

2 Likes

So I now have this working (almost
):

Item price is updated using Order Added to Ticket rule, with an action that calls a script to query a JSON file containing the current drink exchange prices by item ID.

Item sales are exported to CSV using Data Export feature, and I have a Ticket Closing rule to trigger an action that runs the data export for Today, so it contains all items ordered today each time the CSV is created.

However, I am having a problem with the CSV export - when a new order is created and closed, the latest CSV file does not contain it, however the following one (on next order) does. The CSV is always one order behind. I think it’s because the order is not saved to the DB when the Ticket Closing event runs. What can I do to resolve this?

Here is my current setup for exporting items sold:

Rule

Action

Data Export

Thanks :smile:

1 Like

Am eager to see your progress on this, never been to a place which did the stock exchange type pricing but LOVE the idea and if a place in my area ever does will defiantly have to take a trip there.

Have you tried your Export Action in another event, like Before Ticket Closing, or Ticket Closed?

Actually it is live already, and working. http://www.rudehk.com/#!rbsx/cc35 (this is the install we did).

It’s apparently been popular in the USA for many years. This is the first in Hong Kong, so pretty happy to have been part of that

There are a few teething problems like the one I have here, and a few non-SambaPOS related. The Drink Exchange software looks like it’s been built 10 years ago and not much changed since :stuck_out_tongue:. But it works :smile:

1 Like

Isn’t Ticket Closed event only triggered once a ticket is fully settled? I need the action to run every time they close a ticket, regardless of payment.

The thing I also noticed is it works fine on my PC when testing. It exports new orders in the CSV using the Ticket Closing event. So I can only assume the reason it doesn’t work properly in the bar is down to system speed / network issues (as they have 2 POS terminals and a few tablets). Possibly it doesn’t save fast enough to the DB.