# Display multi-column dataset in Perspective dropdown box options

**URL:** <https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476>\
**Category:** Ignition\
**Tags:** database, ignition81, perspective\
**Created:** [June 8, 2022, 2:22am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476 "2022-06-08T02:22:02Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![amy.sudibyo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amy.sudibyo/32/38659_2.png) [@amy.sudibyo](https://forum.inductiveautomation.com/u/amy.sudibyo)\
**Post date:** [June 8, 2022, 2:22am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/1 "2022-06-08T02:22:02Z")

</div>

I am migrating a database interface system from Microsoft Access across to Ignition Perspective with Microsoft SQL.

There are many instances of a dropdown box selector for a value where it is desirable to have the label/description column displayed alongside the value column in the options list. The options list is populated from a two column SQL table.

It seems that the dropdown will only display the label column. If I modify the query to only return the value column then the dropdown will display the values, which is preferable (if I can only display one column). But ideally I’d like the options list to show both. I have tried changing the return format on the binding from ‘dataset’ to ‘json’ however the result is the same.

Is this possible at all with the standard dropdown component or will I have to create my own custom popup view to achieve this function?

 ![Screenshot 2022-06-08 121721](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/0/d0e039288127340067f3421619082f4ab3d129d9.png)  
 ![Screenshot 2022-06-08 121736](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/8/e83b538fd3735bda5aa63396b429f496050fdbbc.png)  
 ![Screenshot 2022-06-08 121814](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e18ad71ef6bf75a6bd8465cbb18e8c589b1c5d62.png)

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [June 8, 2022, 2:34am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/2 "2022-06-08T02:34:56Z")

</div>

There’s no good way to display both with a separator, but you could easily transform the binding to display the value IN the label. This is a lot easier if you change the `Return Format` from dataset to `JSON`.

```auto
return [{"label": " - ".join([op_val, op_label]), "value": op_val} for op_val, op_label in value]

```

or

```auto
options = []
for entry in value:
    options.append({"label": " - ".join(entry[0], entry[1]), "value": entry[0]})
return options

```

These should both give you options which look like this:

```auto
A - Active
B - Broken
CBU - Calibrate Before Use

```

While their underlying value is `A`, `B`, and `CBU`.

---

<div class="post-metadata">

**Author:** ![amy.sudibyo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amy.sudibyo/32/38659_2.png) [@amy.sudibyo](https://forum.inductiveautomation.com/u/amy.sudibyo)\
**Post date:** [June 8, 2022, 4:02am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/3 "2022-06-08T04:02:44Z")

</div>

> [@cmallonee](#):
>
> ```auto
> options = []
> for entry in value:
> options.append({"label": " - ".join(entry[0], entry[1]), "value": entry[0]})
> return options
> 
> ```

Unfortunately neither of these work. The first option grabs the names of the keys, not the values. The second option presents a recursion error. I suspect the issue is that selecting json effectively returns a list of dictionaries, and I can't figure how to get the script to iterate through both the indices of the list and then the keys within each dictionary

---

<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:** [June 8, 2022, 11:46am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/4 "2022-06-08T11:46:40Z")

</div>

Change your SQL to perform the concatenation for the description column.

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [June 8, 2022, 2:12pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/5 "2022-06-08T14:12:03Z")

</div>

```auto
return [{"label": " - ".join([str(entry.value), entry.label]), "value": entry.value} for entry in value]

```

Those original suggestions were pseudo-code because I didn’t have the same setup in place as you had. This example works for me after having changed the return format to json.

---

<div class="post-metadata">

**Author:** ![Eric\_Gruber](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/eric_gruber/32/51070_2.png) [@Eric\_Gruber](https://forum.inductiveautomation.com/u/Eric_Gruber)\
**Post date:** [June 8, 2023, 12:39pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/6 "2023-06-08T12:39:19Z")

</div>

Perfect!

---

<div class="post-metadata">

**Author:** ![JohnMck](https://avatars.discourse-cdn.com/v4/letter/j/eada6e/32.png) [@JohnMck](https://forum.inductiveautomation.com/u/JohnMck)\
**Post date:** [January 16, 2024, 1:11am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/7 "2024-01-16T01:11:23Z")

</div>

The examples presented always have the two fields left aligned with only a single whitespace between the field values.  
"col1 - col2"

The drop down options list apparently automatically collapses multiple white-space characters into a single character.  
For example: "col1 - col2" still displays as "col1 - col2".

Is there a way to NOT collapse the white-space? I've tried all of the style options to no avail:  
-normal  
-pre  
-pre line  
-pre wrap  
-nowrap

In my case, the options will be much easier to read if the first column is always 20 characters wide (I'm using mono-spaced font) so that the second column is vertically aligned.

---

<div class="post-metadata">

**Author:** ![JohnMck](https://avatars.discourse-cdn.com/v4/letter/j/eada6e/32.png) [@JohnMck](https://forum.inductiveautomation.com/u/JohnMck)\
**Post date:** [January 16, 2024, 1:16am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/8 "2024-01-16T01:16:01Z")

</div>

Okay - funny! The box for entering the text for this post also collapsed the white space in my second example:  
"col1\<16 spaces\> - col 2"

My format string for the options label is:  
"{0:\<20s} - {1:s}".format("col1", "col2")

Output:  
"col1 - col2"  
Whitespace gets collapsed.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 16, 2024, 1:58am UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/9 "2024-01-16T01:58:39Z")

</div>

Regarding whitespace and posting code on the forum, see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).

---

<div class="post-metadata">

**Author:** ![JohnMck](https://avatars.discourse-cdn.com/v4/letter/j/eada6e/32.png) [@JohnMck](https://forum.inductiveautomation.com/u/JohnMck)\
**Post date:** [January 16, 2024, 1:20pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/10 "2024-01-16T13:20:07Z")

</div>

Thanks.

My real question still stands, though. Can the perspective dropbox options list NOT collapse whitespace? Is there any style or setting on the dropbox component that will prevent collapsing whitespace?

The idea is to keep the two columns of information vertically alligned:  
column 1 starting at position 1 and column 2 starting at position 21  
so that the information is easier to read.

---

<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:** [January 16, 2024, 1:50pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/11 "2024-01-16T13:50:58Z")

</div>

Browsers collapse whitespace in html. HTML has a special "non-breaking" space character that doesn't collapse. You'd want to replace all of your spaces with those.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 16, 2024, 1:56pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/12 "2024-01-16T13:56:55Z")

</div>

Weird:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/9/093e00ef4f3ce06183d4fb7a2dd2ecbb30b4bdb6.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/3/13686fa769c523a4d447bd3d91c3363d6fe82db5.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/a/2ad32381f66aa7d7f660a84da0f00126aba91469.png)

---

<div class="post-metadata">

**Author:** ![JohnMck](https://avatars.discourse-cdn.com/v4/letter/j/eada6e/32.png) [@JohnMck](https://forum.inductiveautomation.com/u/JohnMck)\
**Post date:** [January 16, 2024, 3:13pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/13 "2024-01-16T15:13:20Z")

</div>

Yep, weird... Because now it's working properly.  
I'm discovering that changes in the designer don't always take immediate effect in a running session even if configured to push changes automatically.

Now, I 'Save' the project, 'Update' the gateway, 'Reload' in the browser whenever I want to save changes. Even then, I occasionally have to close the browser window and relaunch the application in order to see changes. Particularly if the changes were in a project script library.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 16, 2024, 3:28pm UTC](https://forum.inductiveautomation.com/t/display-multi-column-dataset-in-perspective-dropdown-box-options/60476/14 "2024-01-16T15:28:08Z")

</div>

Are you sure that you were setting the `dropdownOptionStyle` and not the basic `style`?