Compound Menu/Category

Have brought up before in beta I think but wanted to start fresh thread.
Mentioned in the compound template topic earlier but think a similar compound system would be powerful tool for menus.

My thought being for an example the hotel has several menus;
Breakfast Food(7-10)
Lunch Food (12-3 a not Tuesday)
Dinner Fod(6-9)
Bar Menu Food(12-9 - not Tuesday lunch)
Drinks (All day)
Market Day (Tuesday only 11-3)

There is obviously cross over on these menus with some available all day.

Would be nice to automate menu visibility namely lunch and dinner crossover.
To implement this at minute you would need at least 4-5 menus in which drinks and bar menu would be available and duplicated and mean there would be allot of work to update say a drinks product listing in a menu.

On same line as the template compound idea it would be nice to define the above menus (drinks, lunch and dinner would have multiple categories)

Then define master menus (the menus selected/switched automatically)

These would be along the lines of;

Normal Breakfast (Drinks & Breakfast) every morning 7-10
Normal Lunch (Drinks, Lunch & Bar Menu) Every lunch exc Tuesday
Normal Afternoon (Drinks & Bar Menu) Every day 3-6
Normal Dinner (Drinks, Dinner & Bar Menu) Every day 6-10
Tuesday Lunch (Drinks, Market Day Menu) Tuesday Lunch Time 10-3

Any thoughts?

Donā€™t think a script will do for this one @QMcKay :wink:

Hey @JTRTech

You seem to have done most of the research with product timers - only 23 posts (not just yours), half of them referring to Searches the rest minimal details. Would it be useful for Timers to be also linked to a Menu, therefore a Product A could be listed as ā€œAll Dayā€ and would appear on all menus? If Product B was listed 7am - 3pm then it may appear on 2 menusā€¦

This may already exist so apologize in advance as need to find some more chunky details on product timers.

Product timers are for time based products like rentals or hire services where quantity is defined as a multiple of whatever period has passed since order added and timer stopped.

Ok, a simple quantity based implementation. Yes I was putting a little innuendo out there :smile: - like the possibility of expanding it to include Menu Visibilityā€¦
Maybe emre might of caught a sniff, or maybe not.

Ok, crazy thought, what about menu category states?
Along with a menu option for category state filter field?
An action to update menu category state would obviously be needed.

Think there could be some interesting things come from this.

@RickH is your retail setup a single layer of buttons? Do you have ā€˜sub menusā€™ of buttons?

1 Like

This was something i was going to suggest, i have menus that open menus that open menus lol

States would be good as i could have a master menu and if we could assign states to the custom product buttons we could have it so the buttons only appear for that states and show as hidden when that state is not present

Currently i have different buttons that appear when ticket is in suspended sale state so i have another rule to switch menu to a suspended sale menu to only show those buttons at that state. This mean creating another menu and making work around with other actions to have it displayed when i need it and change when i dont, and if other buttons are pressed depending on what it is the menu changes when i dont want it to, theres loads of automation to do for it

States would solve this issue i think :slight_smile:

2 Likes

After looking at the database structure there is obviously no simple way to achieve this which I can appreciate.

My logical thoughts if this were considered would be that Categories would not be built within a menu and menus and categories would become two separate.
The current structure would not allow a category to be assigned to multiple menus so would need something in-between ScreenMenuCategories and ScreenMenus like a ScreenCategoryAssignments which would be something like.
[Id], [ScreenMenu], [ScreenMenuCategory]
Sorry if this is over presumptive of me, Iā€™m not a programmer and donā€™t get me wrong Iā€™m not trying to be one, mearly offering ideas.

However in the mean time maybe we could get a clone category option which allows easy update of a second menu.
My example being multiple food menus for times of day but drinks being the same all day.
Rather than updating each menus drinks category we say update the lunch menu, delete drinks category on dinner menu and close the updated lunch menu drinks category into the dinner menu?

@QMcKay as the SQL masterā€¦ I am trying to work a clone category to another menu using mix of SQL and script.
Have a solid plan for the script and have base SQL worked out but am struggling on inserting the duplicated screenmenuitems back in to that table.
Plan so far is;
Ask category to clone
Ask menu to clone from
Ask menu to clone to
Lookup ids for them all
Check if category name exists in to menu

  • clear items if exists
  • clone from category changing menu id if doesnā€™t
    Query items with from category id to temp table
    Change category id to existing or new id
    Insert back into screen menu items table with new category id

Issue Iā€™m having Iā€™m sure is relating to the screen menu item id column.

Searches suggest using insert intergration something to ON. (Am not at PC to check the exact function name) but am struggling to achieve that part.

I manage to put a script together to Clone a menu :grinning:

Its probably much more cluttered than needed as couldnā€™t work out a more streamlined SQL qry but it works.

This script takes the category and its menu names you want to clone from and the menu name you want to clone to.

USE WITH CAUTION - THIS MAKES CHANGES DIRECTLY TO THE DATABASE - ALWAYS BACKUP FIRST

function cloneCategoryToMenu(categoryName,fromMenuName,toMenuName) {
	if (fromMenuName == toMenuName) {
		var response					= 'Category can only be cloned to diferent menu with this function'; 
		} else {
		var fromMenuId					= menuNameId(fromMenuName);
		var fromCategoryId				= categoryNameId(categoryName,fromMenuId);
		var toMenuId					= menuNameId(toMenuName);
		var toCategoryExists			= checkCategoryNameExists(categoryName,toMenuId);
		if (toCategoryExists > 0) {
			var toCategoryId			= categoryNameId(categoryName,toMenuId);
			delectCategoryItems(toCategoryId)
			delectCategory(toCategoryId)
			}
		var fromCategoryValues			= categoryDetailsList(fromCategoryId);
		insertCategoryRow(fromCategoryValues,toMenuId)
		var toCategoryId				= categoryNameId(categoryName,toMenuId);
		var fromCategoryItemCount		= categoryItemsCount(fromCategoryId);
		var fromCategoryItemIdList		= categoryItemsIdList(fromCategoryId)
		for (r = 0; r < fromCategoryItemCount ; r++) {
			var loopItemId				= fromCategoryItemIdList[r];
			var loopItemRowValues		= categoryItemRowList(loopItemId);
			var loopInsert				= insertItemRow(loopItemRowValues,toCategoryId)
			}
		var response					= 'Category: '+categoryName+' [ID:'+fromCategoryId+'] has been cloned from Menu: '+fromMenuName+' [ID: '+fromMenuId+'] to Menu: '+toMenuName+' [ID: '+toMenuId+'] as Category: '+categoryName+' [ID: '+toCategoryId+']';
		}
	return response
}

function menuNameId(inputMenuName) {
	var qry = "SELECT [Id] FROM [ScreenMenus] WHERE [Name] = '"+inputMenuName+"'";
	var menuId = sql.Query(qry).First;
	return menuId
}

function categoryNameId(inputCategoryName,inputMenuId) {
	var qry = "SELECT [Id] FROM [ScreenMenuCategories] WHERE [Name] = '"+inputCategoryName+"' AND [ScreenMenuId] = '"+inputMenuId+"'";
	var categoryId = sql.Query(qry).First;
	return categoryId
}

function checkCategoryNameExists(inputCategoryName,inputMenuId) {
	var qry = "SELECT COUNT([Id]) AS CT FROM [ScreenMenuCategories] WHERE [Name] = '"+inputCategoryName+"' AND [ScreenMenuId] = '"+inputMenuId+"'";
	var categoryCount = sql.Query(qry).First;
	return categoryCount
}

function categoryItemsCount(inputCategoryId) {
	var qry = "SELECT COUNT([Id]) AS CT FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputCategoryId+"'";
	var categoryItemCount = sql.Query(qry).First;
	return categoryItemCount
}

function categoryItemsIdList(inputCategoryId) {
	var qry = "SELECT [Id] FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputCategoryId+"'";
	var categoryItemsIdList = sql.Query(qry).Delimit('~').All;
	return categoryItemsIdList
}

function categoryItemRowList(categoryItemId) {
	var qry = "SELECT [Name],[Header],[Appearance],[MenuItemId],[SortOrder],[AutoSelect],[ButtonColor],[Quantity],[ImagePath],[FontSize],[SubMenuTag],[ItemPortion],[OrderTags],[OrderStates],[AutomationCommand],[AutomationCommandValue] ";
	qry += "FROM [ScreenMenuItems] WHERE [Id] = '"+categoryItemId+"'";
	var categoryItemRow = sql.Query(qry).Delimit('~').First;
	var rowarray	= categoryItemRow.split('~');
	var rowcount	= rowarray.length;
	var rowValuesList	= '';
	for (i = 0; i < rowcount ; i++) {
		if (!rowarray[i] && i != 10) {
			rowValuesList += "NULL,";
			} else {
			rowValuesList += "'"+rowarray[i]+"',";
			}
		}
	var responce = rowValuesList.slice(0,-1)
	return responce
}

function insertItemRow(inputValues,inputScreenCategoryId) {
	var qry = "INSERT INTO [ScreenMenuItems]";
	qry += "([Name],[Header],[Appearance],[MenuItemId],[SortOrder],[AutoSelect],[ButtonColor],[Quantity],[ImagePath],[FontSize],[SubMenuTag],[ItemPortion],[OrderTags],[OrderStates],[AutomationCommand],[AutomationCommandValue],[ScreenMenuCategoryId])";
	qry += "VALUES ("+inputValues+",'"+inputScreenCategoryId+"')";
	sql.ExecSql(qry)
}

function insertCategoryRow(inputValues,inputScreenMenuId) {
	var qry = "INSERT INTO [ScreenMenuCategories]";
	qry += "([Name],[Header],[Appearance],[SortOrder],[MostUsedItemsCategory],[ColumnCount],[MenuItemButtonHeight],[MenuItemButtonColor],[MenuItemFontSize],[WrapText],[PageCount],[SortAlphabetically],[MainButtonHeight],[MainButtonColor],[MainFontSize],[SubButtonHeight],[SubButtonRows],[SubButtonColorDef],[NumeratorType],[NumeratorValues],[AlphaButtonValues],[ImagePath],[NumberPadPercent],[MaxItems],[ScreenMenuId])";
	qry += "VALUES ("+inputValues+",'"+inputScreenMenuId+"')";
	sql.ExecSql(qry)
}

function delectCategoryItems(inputScreenCategory) {
	var qry = "DELETE FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputScreenCategory+"'";
	sql.ExecSql(qry)
}

function delectCategory(inputScreenCategoryId) {
	var qry = "DELETE FROM [ScreenMenuCategories] WHERE [Id] = '"+inputScreenCategoryId+"'";
	sql.ExecSql(qry)
}

function categoryDetailsList(categoryId) {
	var qry = "SELECT [Name],[Header],[Appearance],[SortOrder],[MostUsedItemsCategory],[ColumnCount],[MenuItemButtonHeight],[MenuItemButtonColor],[MenuItemFontSize],[WrapText],[PageCount],[SortAlphabetically],[MainButtonHeight],[MainButtonColor],[MainFontSize],[SubButtonHeight],[SubButtonRows],[SubButtonColorDef],[NumeratorType],[NumeratorValues],[AlphaButtonValues],[ImagePath],[NumberPadPercent],[MaxItems] ";
	qry += "FROM [ScreenMenuCategories] WHERE [Id] = '"+categoryId+"'";
	var categoryItemRow = sql.Query(qry).Delimit('~').First;
	var rowarray	= categoryItemRow.split('~');
	var rowcount	= rowarray.length;
	var rowValuesList	= '';
	for (i = 0; i < rowcount ; i++) {
		if (!rowarray[i] && i != 17) {
			rowValuesList += "NULL,";
			} else {
			rowValuesList += "'"+rowarray[i]+"',";
			}
		}
	var responce = rowValuesList.slice(0,-1)
	return responce
}
1 Like