Has anyone come up with a good way to get an electronic list of menu items by menu area? The idea is to get a list that mirrors how it looks in E10 without manually keying them all in. Doing some searching of past posts, it seems one of the issues is limiting the list to just the licensed items, and then getting it to show the parent menu with the sub-items below (like in Menu Maintenance). Anyone have a solution they can share?
Thanks!
Jeff
Here’s a snip of the Excel sheet where I want to load the menu items:
I think one of the other things that needs to be considered is the fact that the same menu item (name) can exist in different parts of the menu structure. Your spreadsheet doesn’t appear to show that.
This is just a snip of the spreadsheet. The multiple locations of a specific menu items is reflected in the full spreadsheet. The problem I am having is being able to export/download a list of all the menu items in the order the appear in Menu Maintenance.
I have never been able to figure out how to just get the order of the programs and only what appears in the menu structure. I always thought it seemed like the data driving this is hidden or driven by something other than tables.
I wonder if you could use an indented menu listing -maybe based on the example from Epicor?
I can’t remember where I found instructions… was either an ICE manual or an EpicCare knowledgebase.
I thought I had something from my E9 days, but alas I was wrong. It is tricky to extract and reviewing different methods looking at hierarchyid Data Types
DECLARE @TopLevel INT = 1 -- Top level Parent Menu (node)
,@NumLevels INT = 5; -- Depth of the hiearchy to traverse
WITH HierarchyTraaversal AS
(
-- rCTE anchor: retrieve the top level node
SELECT [Level]=1, ParentMenuID, MenuID,MenuDesc
,ParentMenu=CAST(ParentMenuID AS VARCHAR(8000)) + '/' + CAST(MenuID AS VARCHAR(8000))
,Sequence
FROM ice.menu
WHERE ParentMenuID = ''
UNION ALL
-- rCTE recursion: retrieve the following nodes
SELECT [Level]+1, a.ParentMenuID, a.MenuID, a.MenuDesc
,ParentMenu=ParentMenu + '/' + CAST(a.MenuID AS VARCHAR(8000))
,a.Sequence
FROM ice.menu a
JOIN HierarchyTraaversal b ON b.MenuID = a.ParentMenuID
WHERE [Level] < @NumLevels --+ 1
)
SELECT [Level], ParentMenuID, MenuID, MenuDesc,ParentMenu,Sequence
FROM HierarchyTraaversal
--ORDER BY [Level], ParentMenuID, MenuID;
ORDER BY [Level],ParentMenuID, sequence;
I can’t take credit for it and it is not perfect, but it should point you in the right direction. Altering the order by should help narrow down the order, but you could just copy and paste to excel and fine tune it from there. It’s the old story, you spend a heap of time setting it up initially and once it’s in you forget the pain.
No doubt there are some smarter people here than myself may have a better way.
Essentially we are saying that the order needs to be based on a combination of the Parent Menu Hierarchy and the sequence id.
Hope this helps. But at least I refreshed on something.
This can be done fairly easy with a CTE BAQ on the Menu table. I have a BAQ that i have created multiple times as a training experience for CTE queries. Here are the results… the query demonstrates multiple CTE Features, such as appending fields (building Menu Path), Indention, and sorting into the correct order (for things like Explosion of data like this).
Thanks for the responses and suggestions! I will try @Hally’s dashboard, adjusting the country section as @amurdock advised, and also see if I can put together a BAQ like @timshuwy showed. Sure will be nice to have a non-manual entry way to do this…
There is a good example of a CTE baq in the tools guide. Mine was based on that and another post about menu security. I put detail on the notes of the dashboard.
