Custom Menu using named query

Hello all,

What do you think about using a query to populate a menu and sub-menu items? The table would have flags for various security groups, zones, etc. We are currently using security groups via ADS, but have found that when people move around departments or get promoted, etc., then we have to reword the menu code (at least that's what think anyway).
The benefit of using a table is we could change a value and voila! Done.

And, if we can use a named query, I'm not sure how the binding/parsing would work. My first attempt broke the menu items completely. So if there is a way to parse through the dataset and send the name/value pair to a specific menu item/sub-menu item...

I did send a test query data set to a text area, and displayed it as JSON...

My thought, so far, includes these fields in a table:
Menu Name
Parent Menu (for sub-menu items)
User Role (the named security roles from the gateway)
Allow Menu (boolean to show or not to show)


Edit: Also, how can I get the JSON statement for building the menu? In the Docs they show a sample, but how can I pull it off my menu?

1 Like

Preface: I am not the most experienced with the ADS. Not sure I have access, but if I do I know I don't belong messing with that at all in my company. Which means I don't know how that is intended to work at all.

Weight my preface, but here are my thoughts:

I like this idea, except would a dictionary not be better?
I am interested in the development of the concept.

The ADS can be a tier above the design space in some regards for larger companies.
I think in my case, it would let me as a designer check/set security of pages at my tier.

Then I can make pages that users of security groups I set can then check/set access for pages in relevant to their department.

Or said another way:

ADS at the corporate tier.
Dictionary in our scripts for the design tier for use designers.
Table for a tier of users of our pages to configure for their uses for their subordinates/department.

I'm not sure what you mean by dictionary here.

Also, I forgot to mention the main reason we want to do this - is to be able to do away with all the scripting for each menu/sub-menu item for every client/user. Especially when we need to make a change to the menu structure, we have to manually re-script.

If we had a table to draw the values from, then the changes could be made to the table and the menu props could be bound to the applicable fields to reduce our time in the designer.

I hope that all makes sense, lol.

1 Like

A query could be made to work, though I'm not sure you would be able to escape scripting completely.

Since this is really something that is global to the project I would consider a few options:

  1. Use a project global variable to hold entire structure of menu. This could be generated by a query, or could even be a persitant object using system.util.getGlobals(). There is a very long discussion here about the pros/cons and how to's of that here:
  1. Create a script that could loop through the results of the query, or even a dictionary or JSON string, and parse it to the correct format. That script should reside in a project script. For instance perhaps create a session property that calls a function getAuthorizedMenuTree() and returns the valid tree structure based on what ever criteria is needed, for that users authorization level.

The easiest way to acquire the JSON for your menu structure is to build it out and copy it from the component. Otherwise you can use the structure from the menu example and change the values in the appropriate places.

Another option rather than storing this in a DB table would be to use a tag with a Document Type. That method would avoid the potential pitfalls of persisting a top level variable.

This is the output of a query to a text area:
It looks like a JSON string to me. If i could use a FOR loop

For option 2:

object = JSON string
i = 0
for x in object
menu.props.item[i] = x.MenuName?
i = i + 1

something like that work?

As far as copying the JSON from the component I have no idea how to get there, unless you mean build it out in JSON first.

Because it is JSON. I assume that you have the return format set to either auto or json. You could set it to dataset if you were more comfortable with that but parsing the JSON is just as simple.

So, let's say that you have a default Menu Item config, something like this:

defaultItem = {
    'navIcon': {
    'label': {
        'icon': {
    'showHeader': True

Next, lets assume that you're query returns all of the required fields ( At a minimum you will need a target and label path, all though you could also store things like navIcon path, Label Icon path. The manual entry for the Menu Tree explains all of the different options.

Then you could use a script like this to build a list of menu items. (I will assume that you have used a query binding with a script transform where value would be the result of the query.

#convert JSON string to a python object
itemDefs = system.util.jsonDecode(value)

#loop through the results and build the authorized menu item list
defaultItem = {'target':'','items':[],'navIcon': {'path':''},'label': {'text':'','icon': {'path':''}},'showHeader': True}

items = []
for iDef in itemDefs:
    if iDef['allowMenu']:
        thisItem = dict(**defaultItem)
        thisItem['target'] = iDef['navigationPath']
        thisItem['navIcon']['path'] = iDef['navIconPath']
        thisItem['label']['text'] = iDef['menuName']
        thisItem['label']['icon'] = iDef['labelIconPath']

return items

NOTE: This is a very basic example and doesn't take Sub-Menus into account. The exact way that is handled is dependent on how you design the table. Perhaps a parent column that gives the name of a child's parent. You would need some type of recursive call to do this, and it would be best to only call the function once.

Just a quick thing to note. Queries update when you tell them to, not when the underlying data changes. I saw this issue on a project I worked on by another integrator. I went added some pages to the nav on the sql tables, but none of the clients updated because the query never reran. I had to go around to each client and restart ignition on them

If the binding is set to poll, then this isn’t an issue.

You don't really want 120 clients all set on query poll though, 99% of the time being wasteful. Well, it's probably not such a big burdon, but changes won't apply until it polls, so you'd want to set it fairly quick. I just don't like it personally

Make a query tag and point the clients to the tag. Then the update is polling slow and only one spot.

Ok ok, there are ways around it, maybe I'm just salty from that experience :sweat_smile: haha (I actually thought it was a smart idea until I realised they hadn't allowed for updating it in clients)

You wouldn't even have to poll it then, just make sure you resave the tag if you change tables

Return format was Auto, now JSON.

Regarding the menu config: is there a way to extrapolate the JSON from the menu (or any component for that matter), or do I need to manually type it all out?

Not sure what you mean here.

The menu tree default config:

By binding, you are referring to binding the items object to the query? The pic I posted earlier was just a text area to display what the query returned. And I have not used a script transform on a query yet.

What would the script transform be required to do? If value is the result of the query, why use a transform?

def transform(self, value, quality, timestamp):
	return value

And if I create this script in the Scripting Library, it should be simple to call it in any event action or script action?

I am already planning for this. Earlier this year I discovered someone using a self-join in an MS Access table for this purpose, and I thought 'What a cool idea!' (Was wondering how I'd solve the parent-child setup there, and for an employee table as well.)

You could use a message handler to refresh all the queries. Watched a video on that this week.

These are efficient but you have a fixed level of nesting. If your DB is modern, it certainly supports recursive Common Table Expressions that can handle an arbitrarily nested tree.

Yes, right click on the property that you want and copy it. Now you can paste it in your text editor of choice (or a script dialog).

I mean that this page lists all of the properties that will be recognized by the component. You can of course add any property that you want to the item object, but only the ones listed in the manual are guaranteed to affect the component.

Yes, I knew the pic you posted was intended to show the result of the query. I was clarifying that you will want the binding on the items prop. Though that may not actually be where you bind the query depending on how you set it up. You could have the query binding on a session prop, or use a query tag and bind to the tag. Eventually though you will need to bind to the items prop, and the value will be the result of the query.

If you can write a query that returns a properly nested structure, and you want to call that query at every login of a user, then more power to you. Personally, I would call the query and return all items, and then filter that based on the logged in user. Which is what script I provided was attempting to show.

Yes, I would recommend that you place the script in a project library, and then it is fairly simple to call it from where ever you want.

1 Like

Here is my query:

SELECT [adm].Menus.MenulabelText,
FROM [adm].Menus
ORDER BY [adm].Menus.MenulabelText

Here is the Script Transform:

def transform(self, value, quality, timestamp):
	itemDefs = system.util.jsonDecode(value)
	defaultItem = {}
	items = []
	for iDef in itemDefs:
		if iDef['MenuVisible']:
			thisItem = dict(**defaultItem)
			thisItem['target'] = iDef['MenuTarget']
			thisItem['label']['text'] = iDef['MenuName']
	return items

I get this error:

jsonDecode() 1st arg can't be coerced to string

So, my query is not structured properly?

BTW - it took me a while, but I came back to work on this...
@lrose @pturmel

If I put this statement in, commenting out the rest: system.perspective.print(value), I get:
Other wise it errors on this: itemDefs = system.util.jsonDecode(value) with the above error.

jsonDecode takes a string, you are giving it a dataset. You should be iterating through the dataset. Perhaps converting it to a PyDataset for iterating convenience.

Make certain that your query binding is configured to output JSON.

I get the same error no matter what output I set it to. In the script console, if I JSON encode it first, then print out the encoded "string", I can do that:

itemDefs = system.util.jsonEncode(jStr)

What does the double asterisk do in this statement? thisItem = dict(**defaultItem)

Also, by using this syntax: if iDef['MenuVisible']: instead of if iDef[0]: I get this error:
TypeError: unicode indices must be integers

If I use any integer besides 0, I get an index out of range error.

And for some reason it does not like having two 'keys', such as:
thisItem['navIcon']['path'] = iDef['navIconPath']
The error here is KeyError: 'navIcon'

What is the value of jStr? What does this print?

Note: You should be using system.perspective.print() to print to the output console in the designer.

In the code that I provided, defaultItem is not an empty Set/Dictionary. It actually has a value. The ** is an operator that tells python to unpack the dictionary.

This tells me that there is something wrong with either your query, or the value that it is returning.

If you add system.perspective.print(value) at the top of the transform, what does it print?

thisItem will be a dictionary with the structure that is provided by defaultItem since you have provided an empty dictionary, it has no keys. There for when you try to reference the key navIcon for thisItem you receive a key error because the key doesn't actually exist.

defaultItem is intended to be a dictionary of the properties that all Items will have, that can then be used to hold values that are processed from the DB.