Menu Item button display format

That was my thinking when I was trying… although on button header.
Was for ales as they changed ales regularly so changing product name would have surfaced as stock is counted for ales banded by price and was more a case of correct name on button for staff to ring in correct price. ended up using ale 1, ale 2 etc.

@emre is there any possibility to add this functionality (as in initial post) ? I’ve setup a few times with item numbers on buttons and just about to do another! Creating the buttons with the item number (from a Product Tag) is time consuming - I managed to automate slightly using SQL and Excel to update the fields with the data but it’s a one time thing for initial install then afterwards, the client needs to manually set each time. It’s also not ideal for giving new client training and explaining the whole </linebreak> thing within button headers.

I think it would be a useful feature for a number of users.

Display format would be nice. I think at least header column should be empty and only use when custom button header needed. When it is empty, use menu item name automatically.

1 Like

This is two ways of doing it, being able to put {NAME} as header would be more powerful than just doing it automatically if no header set. Ideal solution is oprder/product level tags would work for button headers in menu id say.
Double that up with setting header as {NAME} by default gives the equivalent of what happens now with auto update.
Potentially update-able application would be to compare header to product name and if different leave as is, if same overwrite header with {NAME} meaning nothing changes while applying new method…

Here’s a little SQL script I created to automatically generate Menu Button Header values using “Item Number” Product Tag.

It expects the Product Tag to be called “Item Number” and will only change the header value for those products that have an item number set. It will create menu buttons like this:

UPDATE SMI
SET Header = '#' + SUBSTRING(MI.CustomTags, CHARINDEX('{"TN":"Item Number","TV":"', MI.CustomTags) + LEN('{"TN":"Item Number","TV":"'), CHARINDEX('"', MI.CustomTags, CHARINDEX('{"TN":"Item Number","TV":"', MI.CustomTags) + LEN('{"TN":"Item Number","TV":"')) - LEN('{"TN":"Item Number","TV":"') - 2) + '<linebreak/>' + SMI.Name
FROM ScreenMenuItems SMI
INNER JOIN MenuItems MI ON MI.Id = SMI.MenuItemId
WHERE MI.CustomTags LIKE '%"TN":"Item Number"%'
GO

I have added this into Database Tasks so it can be re-run at any time in the future when adding new products to re-generate the header values again.

I am using SQL Server 2014 right now, however if using SQL Server 2016 there is probably an easier way to do this because it natively supports JSON data within fields.

3 Likes

Script to set the Header to contain the Price for the default Portion:

UPDATE mi SET [Header]= REPLACE(mi.[Name],'\r','<br/>') + '<br/>' + convert(varchar(50),p.[Price])
FROM [ScreenMenuItems] mi
JOIN [MenuItems] pr on pr.[Id] = mi.[MenuItemId]
JOIN [MenuItemPortions] po on po.[MenuItemId] = pr.[Id] and po.[Id]=(SELECT min(po2.[Id]) FROM [MenuItemPortions] po2 WHERE po2.[MenuItemId]=pr.[Id])
JOIN [MenuItemPrices] p on p.[MenuItemPortionId] = po.[Id]

Result:

3 Likes

How do I run this script?

Use SSMS or create a DB Task…


Create a file that contains the script, and save it as [CBL]Update Menu Button Prices.sql in:

[Docmuents]\SambaPOS5\Database Tasks\

:bulb: NOTE: FYI, the [CBL] prefix on the file name is optional, but they are “flags” that indicate what is supposed to happen when the Task is executed:

  • C = Prompt for Confirm before execution
  • B = Backup DB before execution
  • L = Logout (and since you are altering the DB, a logout is required to refresh the Cache)


Run the Task in Manage > Settings > Database Tools > Database Tasks


Or use the Execute Database Task action and attach it to an Automation Command …

3 Likes

Thanks for that :D.

One other quick question, would it be better to use execute task action so I could use it in a config task? Or is there another method which I could use the script in a config task?

I am not very familiar with Config Tasks myself, but I think executing the DB Task via the Action is probably the way to go.

What does the Arguments section in the execute database task do? What is it used for?

I don’t think the Arguments field applies to SQL tasks. It probably applies to BAT and/or VBS tasks.

The tasks can be SQL, BAT, or VBS …

Someone else asked about the Arguments parameter, and there was no definitive answer given to indicate the Arguments parameter worked with SQL tasks, so …

1 Like

Thanks for the clarification :slight_smile:

For next .61 I added comma separated arguments support. However parameter handling for database tasks is different.

In the script text parameters can be referenced by index as {0}, {1}, etc…

For example when SELECT {0} from {1} script called with *,Tickets argument first parameter (*) will replace {0} and second (Tickets) will replace {1}.

1 Like

I understand the terminology but how does that work in an actual scenario?
Could you give an example of how that argument section could be used?

If you’re asking me I don’t know how it can be used :slight_smile:

1 Like

btw one should be mindful of the security implications of have any Database Task action activated during normal day to day operations. Regular users should be denied write access to that Database Task folder, otherwise they could change the SQL file to do whatever they wanted on the database.

1 Like

They could also just execute SQL via cmd or mssms, or script… your #1 defense in security is discipline and knowing what your workers are doing.

1 Like

To update via ssms or script I thought they would need the db password, and i was planning on locking the staff out of the settings files also. One of the main reasons for installing SambaPOS is to control the fraud, ;-}

You do not need DB password to do anything via SambaPOS. It already is logged into the db.