Menu Item button display format

V5 Request
10

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