# 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:** 2

<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, 5:50pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/21 "2023-05-22T17:50:58Z")

</div>

> [@lrose](#):
>
> Note: You should be using `system.perspective.print()` to print to the output console in the designer.

I'm attempting to break through this barrier of errors in the Script Console. `jStr=system.db.runNamedQuery('test_menu_nq')`

I set up a label, bound to the same query: here is the text, which is what the `Perspective.Print` gives me:

```auto
[{"MenulabelText":"Dashboards","MenuVisible":true,"MenuTarget":"dashboards"}]

```

If I JSON encode it, I get this:

```auto
{"columns":[{"name":"MenulabelText","type":"java.lang.String"},{"name":"MenuVisible","type":"java.lang.Boolean"},{"name":"MenuTarget","type":"java.lang.String"}],"rows":[["Dashboards",true,"dashboards"]]}

```

---

<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, 6:33pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/22 "2023-05-22T18:33:27Z")

</div>

Well, I found this to work in the Script Console:

```auto
jStr = system.db.runNamedQuery('test_menu_nq')
itemDefs = system.dataset.toPyDataSet(jStr)
defaultItem = {}
items = []
for iDef in itemDefs:
	if iDef['MenuVisible']:
		thisItem = dict(**defaultItem)
		thisItem['target'] = iDef['MenuTarget']
		thisItem['path'] = iDef['MenuLabelText']
		items.append(thisItem)
print(items[0])

```

This prints out:

```auto
{'target': u'dashboards', 'path': u'Dashboards'}

```

BUT(!!) it can't be coerced into a Dataset like:

```auto
itemDefs = system.dataset.toPyDataSet(value)

```

in Perspective script transform.

---

<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, 6:38pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/23 "2023-05-22T18:38:58Z")

</div>

> [@mdemaris](#):
>
> I'm attempting to break through this barrier of errors in the Script Console. `jStr=system.db.runNamedQuery('test_menu_nq')`

This works for some things in perspective, but not all. The Script Console is really Vision Client Scope, and perspective runs in Gateway Scope.

I believe you will get to the bottom of your issues quicker if you troubleshoot in perspective itself.

> [@mdemaris](#):
>
> `[{"MenulabelText":"Dashboards","MenuVisible":true,"MenuTarget":"dashboards"}]`

This would work with your code.

> [@mdemaris](#):
>
> ```auto
> {"columns":[{"name":"MenulabelText","type":"java.lang.String"},{"name":"MenuVisible","type":"java.lang.Boolean"},{"name":"MenuTarget","type":"java.lang.String"}],"rows":[["Dashboards",true,"dashboards"]]}
> 
> ```

This would not work with your code as written. This looks like the JSON encoding of a Dataset Object. I suspect that you've been caught unawares by the fact that the script function to call a named query will only return a dataset, where a binding can return a JSON string.

---

<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, 9:56pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/24 "2023-05-22T21:56:06Z")

</div>

> [@lrose](#):
>
> ```auto
> 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}
> 
> ```

@lrose My apologies! There is one major thing I had somehow missed when I copied your script 🤦‍♂️ and the is the structure of the dictionary for `defaultItems`!

However, the `jsonDecode()` did require a `str()` function to fix the coercing into string issue.

Many thanks 👏 and apologies, you've been most helpful and patient. Give yourself a raise 💵, too!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 22, 2023, 10:05pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/25 "2023-05-22T22:05:44Z")

</div>

FWIW, I find comprehensions make stuff like this a _lot_ cleaner:

```python
itemDefs = system.dataset.toPyDataSet(jStr)
items = [
	{
		'target': item['MenuTarget'],
		'path': item['MenuLabelText'],
	}
	for item in itemDefs
	if item['MenuVisible']
]

```

No need for repeated references to the same item from the outer scope, no need to append items to a list manually, etc.

---

<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, 10:18pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/26 "2023-05-22T22:18:15Z")

</div>

There is no loop here, so how would you cope with more than one row? When I put this concept into the scripting console, I can `print item[0-2]` and return the values of the first row, but any integer larger than the number of columns, with zero based index, is an out of range error.

Please excuse my lack of python, JSON, Java understanding as I attempt to understand how this works. Much appreciated!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 22, 2023, 10:22pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/27 "2023-05-22T22:22:00Z")

</div>

This is an example of a comprehension, specifically a list comprehension. It's a standard bit of Python syntax; there _is_ a loop, it's just embedded inside the list construction.

> **[Python - List Comprehension](https://www.w3schools.com/python/python_lists_comprehension.asp)**
>
> W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

---

<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, 10:41pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/28 "2023-05-22T22:41:45Z")

</div>

I haven't quite solved it, yet. It seems that one row's value overwrites every other value returned for a particular dictionary value: `['label']['text']`

Which suggests that the original dict structure might be off.

In this case, I've used:  
Dashboards  
Inventory  
Trucks  
Cars

And 'Trucks' overwrites all the other labels.

---

<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 22, 2023, 10:49pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/29 "2023-05-22T22:49:29Z")

</div>

> [@mdemaris](#):
>
> one row's value overwrites every other

That usually means you are placing the same dictionary in multiple rows, after which following changes affect prior work. Double check how you are instantiating each row. Or share the latest version of your code.

---

<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, 10:58pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/30 "2023-05-22T22:58:04Z")

</div>

I was just going over the structure again, and it seems fine to me.

```auto
def transform(self, value, quality, timestamp):
	itemDefs = system.util.jsonDecode(str(value))
	defaultItem = {'target':'','items':[],'navIcon': {'path':''},'label': {'text':'','icon': {'path':''}},'showHeader': True}
	items = []
	for iDef in itemDefs:
	    if iDef['MenuVisible']:
	        thisItem = dict(**defaultItem)
	        thisItem['target'] = iDef['MenuTarget']
	        thisItem['label']['text'] = iDef['MenuLabel']
	        items.append(thisItem)

```

I thought that there might be a missing (or extra) set of curly braces that would cause the `label:text` to be overwritten, but I don't see that.

FWIW, here is the returned data:

```auto
[{"showHeader":true,"navIcon":{"path":""},"label":{"icon":{"path":""},"text":"4"},"items":[],"target":"1"},
{"showHeader":true,"navIcon":{"path":""},"label":{"icon":{"path":""},"text":"4"},"items":[],"target":"2"},
{"showHeader":true,"navIcon":{"path":""},"label":{"icon":{"path":""},"text":"4"},"items":[],"target":"3"},
{"showHeader":true,"navIcon":{"path":""},"label":{"icon":{"path":""},"text":"4"},"items":[],"target":"4"}]

```

Where the text should be numbered the same as target.

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [May 22, 2023, 11:00pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/31 "2023-05-22T23:00:36Z")

</div>

> [@mdemaris](#):
>
> `thisItem = dict(**defaultItem)`

this is doing a shallow copy. I would import copy and do `thisItem = copy.deepcopy(**defaultItem)` in it's place.

[copy — Shallow and deep copy operations — Python 3.12.1 documentation](https://docs.python.org/3/library/copy.html#:~:text=A%20shallow%20copy%20constructs%20a,objects%20found%20in%20the%20original).

---

<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, 11:02pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/32 "2023-05-22T23:02:06Z")

</div>

I understood the `**` to unpack a python dictionary. Rose linked an article above

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [May 22, 2023, 11:12pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/33 "2023-05-22T23:12:00Z")

</div>

I've only ever used the `**` operator to unpack a dictionary when passing it to a function. I would assume `dict(**defaultItem)` isn't accomplishing anything and would be the same as `dict(defaultItem)` and just copy the outermost dictionary and not any nested within.

---

<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:** [May 22, 2023, 11:16pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/34 "2023-05-22T23:16:03Z")

</div>

> [@Matthew.gaitan](#):
>
> I would assume `dict(**defaultItem)` isn't accomplishing anything and would be the same as `dict(defaultItem)` and just copy the outermost dictionary and not any nested within.

Given:

```auto
defaultItem = {'target':'','items':[],'navIcon': {'path':''},'label': {'text':'','icon': {'path':''}},'showHeader': True}

```

`dict(**defaultItem)` is _**almost**_ equivalent to

```auto
dict(target='', items=[], navIcon={'path': ''}, label={'text': '', 'icon': {'path': ''}}, showHeader=True)

```

However, the dict values (for `navIcon` and `label`) are actually pointers to the originals

---

<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 22, 2023, 11:16pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/35 "2023-05-22T23:16:07Z")

</div>

> [@Matthew.gaitan](#):
>
> and not any nested within.

Correct. The default item has nested lists and dictionaries. Those will be shared by all items unless completely replaced.

---

<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 22, 2023, 11:17pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/36 "2023-05-22T23:17:10Z")

</div>

> [@nminchin](#):
>
> `dict(**defaultItem)` is equivalent to

No, the second form is supplying newly constructed lists and nested dictionaries.

Edit: caught you just before you added "almost". 😁

---

<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:** [May 22, 2023, 11:18pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/37 "2023-05-22T23:18:39Z")

</div>

I added "almost" in an edit, with extra info below.

> [@pturmel](#):
>
> Edit: caught you just before you added "almost". 😁

Haha. I have a bad habit of "post first, read after"

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 22, 2023, 11:24pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/38 "2023-05-22T23:24:57Z")

</div>

I still maintain a _well formatted_ comprehension is cleaner and easier to reason about. Plus no faffing about with deepcopies or mutable references.

```python
def transform(self, value, quality, timestamp):
	itemDefs = system.util.jsonDecode(str(value))

	return [
		{
			'target': item['MenuTarget'],
			'items': [],
			'navIcon': {
				'path': ''
			},
			'label': {
				'text': item['MenuLabel'],
				'icon': {
					'path': ''
				}
			},
			'showHeader': True
		}
		for item in itemDefs
		if item['MenuVisible']
	]

```

---

<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, 11:30pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/39 "2023-05-22T23:30:11Z")

</div>

@paul-griffith’s example is a much better implementation @mdemaris.

In my defense, I threw that example together with little to no testing and not a whole lot of thought towards actual implementation. It was mostly for illustration purposes.

---

<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 22, 2023, 11:30pm UTC](https://forum.inductiveautomation.com/t/custom-menu-using-named-query/65771/40 "2023-05-22T23:30:28Z")

</div>

> [@paul-griffith](#):
>
> a _well formatted_ comprehension is cleaner

Yes, but...

> [@paul-griffith](#):
>
> and easier to reason about

Many would disagree that comprehensions are comprehensible in the first place. **I** like them, but I learned long ago not to project this sort of thing onto others.

I tend to keep list comprehensions to one-liners, for others to maintain.

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

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