# Custom Menu using named query

**URL:** <https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [October 27, 2022, 6:07pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771 "2022-10-27T18:07:25Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [October 27, 2022, 6:07pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/1 "2022-10-27T18:07:25Z")

</div>

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)

Thoughts?

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?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [October 27, 2022, 6:31pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/2 "2022-10-27T18:31:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [October 27, 2022, 6:42pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/3 "2022-10-27T18:42:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 27, 2022, 7:10pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/4 "2022-10-27T19:10:41Z")

</div>

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:

> [@\[IGN-5244\]Persist Python Class Object](https://forum.inductiveautomation.com/t/ign-5244-persist-python-class-object/30498/3):
>
> That sounds risky. Would you say that writing a de-serialized method would be the preferred way to handle this in Ignition/Perspective? Would this be a good use case for Pickle? [https://docs.python.org/2/library/pickle.html](https://docs.python.org/2/library/pickle.html)

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.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [October 27, 2022, 9:27pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/5 "2022-10-27T21:27:03Z")

</div>

This is the output of a query to a text area:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/c/5ca9bfea3a0e1225088ef04d61eca1a54261a12a.png)  
It looks like a JSON string to me. If i could use a FOR loop

For option 2:

```auto
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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 28, 2022, 1:25am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/6 "2022-10-28T01:25:36Z")

</div>

> [@mdemaris](#):
>
> It looks like a JSON string to me

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:

```python
defaultItem = {
    'target':'',
    'items':[],
    'navIcon': {
        'path':''
    },
    'label': {
        'text':'',
        'icon': {
            'path':''
        }
    },
    '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.

```python
#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']
        items.append(thisItem)

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [October 28, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/7 "2022-10-28T22:03:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 28, 2022, 10:12pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/8 "2022-10-28T22:12:18Z")

</div>

> [@nminchin](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [October 28, 2022, 10:37pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/9 "2022-10-28T22:37:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [October 28, 2022, 10:39pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/10 "2022-10-28T22:39:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [October 28, 2022, 10:43pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/11 "2022-10-28T22:43:10Z")

</div>

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

> [@bschroeder](#):
>
> Then the update is polling slow and only one spot.

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

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [October 29, 2022, 12:42am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/12 "2022-10-29T00:42:29Z")

</div>

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?

> [@lrose](#):
>
> The manual entry for the Menu Tree explains all of the different options.

Not sure what you mean here.

The menu tree default config:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/a/da527757f1f5f367a2c3930cf920b80d17114c8e.png)

> [@lrose](#):
>
> I will assume that you have used a query binding with a script transform where `value` would be the result of the query.

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?

```auto
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?

> [@lrose](#):
>
> Perhaps a parent column that gives the name of a child's parent

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.)

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [October 29, 2022, 12:43am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/13 "2022-10-29T00:43:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 29, 2022, 12:53am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/14 "2022-10-29T00:53:18Z")

</div>

> [@mdemaris](#):
>
> self-join

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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 29, 2022, 2:56pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/15 "2022-10-29T14:56:04Z")

</div>

> [@mdemaris](#):
>
> 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?

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).

> [@mdemaris](#):
>
> Not sure what you mean here.

I mean that [this](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Menu+Tree) 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.

> [@mdemaris](#):
>
> 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.

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.

> [@mdemaris](#):
>
> What would the script transform be required to do? If `value` is the result of the query, why use a transform?

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.

> [@mdemaris](#):
>
> And if I create this script in the Scripting Library, it should be simple to call it in any event action or script action?

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.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 18, 2023, 8:14pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/16 "2023-05-18T20:14:28Z")

</div>

Here is my query:

```auto
SELECT [adm].Menus.MenulabelText,
  [adm].Menus.MenuVisible
FROM [adm].Menus
ORDER BY [adm].Menus.MenulabelText

```

Here is the Script Transform:

```auto
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']
			items.append(thisItem)			
	return items

```

I get this error:

```auto
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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/8/b868e36b43e3c34c5f44d4ab4419e89a4e4a1ade.png)  
Other wise it errors on this: `itemDefs = system.util.jsonDecode(value)` with the above error.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 20, 2023, 1:05am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/17 "2023-05-20T01:05:50Z")

</div>

`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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 20, 2023, 1:33am UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/18 "2023-05-20T01:33:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [May 22, 2023, 4:57pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/19 "2023-05-22T16:57:07Z")

</div>

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:

```plaintext
itemDefs = system.util.jsonEncode(jStr)
print(itemDefs)

```

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'`

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 22, 2023, 5:25pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/20 "2023-05-22T17:25:27Z")

</div>

> [@mdemaris](#):
>
> 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:
> 
> ```auto
> itemDefs = system.util.jsonEncode(jStr)
> print(itemDefs)
> 
> ```

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.

> [@mdemaris](#):
>
> What does the double asterisk do in this statement? `thisItem = dict(**defaultItem)`

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.

> **[Unpacking Operators in Python](https://towardsdatascience.com/unpacking-operators-in-python-306ae44cd480)**
>
> Using the \* and \*\* unpacking operators in python

> [@mdemaris](#):
>
> 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.

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?

> [@mdemaris](#):
>
> `thisItem['navIcon']['path'] = iDef['navIconPath']`  
> The error here is `KeyError: 'navIcon'`

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.

[Next page](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771.md?page=2)