# Power Table dropdown lists data from query not appearing Ignition 7.9 **URL:** \ **Category:** Ignition\ **Tags:** ignition79\ **Created:** [January 6, 2025, 4:36pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487 "2025-01-06T16:36:40Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Juan\_Gerardo\_Rivera](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@Juan\_Gerardo\_Rivera](https://forum.inductiveautomation.com/u/Juan_Gerardo_Rivera)\ **Post date:** [January 6, 2025, 4:36pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/1 "2025-01-06T16:36:40Z")
Hello, May you please help me with the next concern, what I'm doing is to convert the cells from a Power Table into dropdown lists to show the materials available by using the event configureEditor():, all the information the script I used is the following: ```python if colName == 'Material': # Consulta SQL para obtener los materiales #Use SQL Query and get the materials' list query = """ SELECT DISTINCT Material FROM Almacen_Indirectos_Catalogo_ZM WHERE Material IS NOT NULL """ try: # Ejecutar la consulta #Executes the query resultados = system.db.runQuery(query) # Imprimir los resultados del query para depuración #Printing the result for debugging print "Resultados del query:", resultados # Convertir los resultados en una lista de opciones (solo los valores de 'Material') #The options from lists are printed(just the values from 'Material'column options = [fila["Material"] for fila in resultados] # Imprimir algunas opciones generadas para el dropdown para depuración #Printing dropdown options for debugging print "Opciones generadas para el dropdown:", options[:10] # Imprime las primeras 10 opciones #Print the first ten options # Retornar las opciones del dropdown #Returning the dropdown options return {'options': options} except Exception, e: # Manejo de errores con mensaje en la consola #Error handling by showing issues in the message box of console print "Error ejecutando la consulta:", str(e) # En caso de error o sin resultados, retornar lista vacía #If options are not found, a empty list will be returned return {'options': []} ``` ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/9/f9cb0b5e06ef79d215b9deddc1812719a7cbb6d3.png) By the way, in the process debugging as well as running the query on SQL, the elements are shown correctly so I do not know if the issue is in the options list ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/7/77b10e441d69abe40f401e725f2ed501ac6d3c06.png) I used a query to show all the elements because the list contains 1876 elements, I checked the query on Microsoft SQL Server Management Studio and eveything seems to be fine ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d236275e273cba5fa86d8d2b1baf3fd27bd37fd.png) I just want the dropdown list to show all the information. Best regards ---
**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:** [January 6, 2025, 5:09pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/2 "2025-01-06T17:09:37Z")
> [@Juan\_Gerardo\_Rivera](#): > > ```python > resultados = system.db.runQuery(query) > > ``` # Do not do this. Running a query is a _very_ fragile operation that is subject to a _minimum_ of four IPC calls, each almost always over the network: 1. Your client/designer -\> your gateway 2. Your gateway -\> your DB 3. Your DB -\> your gateway (with the results) 4. Your gateway -\> the client/designer Because you're in the `configureEditor` extension function, that query operation is running on the primary GUI painting thread (known as the 'event dispatch thread' or EDT). If _any_ of those operations take longer than a few hundred milliseconds (and inevitably they will), users will report that your window is laggy, get frustrated, and start to hate your project/Ignition/you personally 😄. They also will not understand _why_. What you need to do is run your query in a _binding_, in an additional custom property on the table, where it will automatically run _asynchronously_. Then use scripting to _refer_ to that table to bring in your options list. ---
**Author:** ![Juan\_Gerardo\_Rivera](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@Juan\_Gerardo\_Rivera](https://forum.inductiveautomation.com/u/Juan_Gerardo_Rivera)\ **Post date:** [January 7, 2025, 4:12pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/3 "2025-01-07T16:12:21Z")
> [@Juan\_Gerardo\_Rivera](#): > > ```python > SELECT DISTINCT Material > FROM Almacen_Indirectos_Catalogo_ZM > WHERE Material IS NOT NULL > > ``` Understood, Paul, and thank you for your fast quick response, I just created a custom property directly in the Power Table, which datatype is Dataset and is named MaterialOptions which is bound to the query ```python SELECT DISTINCT Material FROM Almacen_Indirectos_Catalogo_ZM WHERE Material IS NOT NULL ``` How do I call this custom property within the script? Is correct to use a dataset type for the binding or should I use another one? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/6/9695dee459410321a617989140dbcd15727e03dd.png) ---
**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:** [January 7, 2025, 5:26pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/4 "2025-01-07T17:26:19Z")
Yes, a dataset return type is appropriate. You just need to 'map' the dataset into the list-of-tuples format the `options` property expects. With a list comprehension it's pretty simple: ```python if colName == 'Material': # Fix this name to be correct; use the property selector in the script area to help (the chain icon) ds = event.source.nameOfYourDatasetCustomProperty # For each row in the dataset, repeat the one column's value into a two-item tuple (required by the power table) options = [ (ds.getValueAt(row, 0), ds.getValueAt(row, 0)) for row in xrange(ds.rowCount) ] return { "options": options } ``` ---
**Author:** ![Juan\_Gerardo\_Rivera](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@Juan\_Gerardo\_Rivera](https://forum.inductiveautomation.com/u/Juan_Gerardo_Rivera)\ **Post date:** [January 7, 2025, 6:22pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/5 "2025-01-07T18:22:09Z")
The dropdown shows the list of materials I needed, thank you so much for your help. I have 2 more questions(if i need to create another post, please let me know) I am planning to create another table for materials approvals requested in the warehouse for different areas, how do I maintain the text inside every cell and then send it to the other table, the .text will still be working? and the second one, I'm converting the cells from another column into dropdown ones, is there a way to connect the cells matching per position, by this I mean, I have a list of materials but everyone matches with a ZM code, I'll use another custom property bound to a query so every option selected from the "Material" column will change automatically the cell from column "Codificacion ZM" according to match. ---
**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:** [January 7, 2025, 6:59pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/6 "2025-01-07T18:59:27Z")
I'm not quite sure what you're asking with the first question, but: > [@Juan\_Gerardo\_Rivera](#): > > the .text will still be working If you have two components on the _same_ window they can absolutely refer to each other's properties (though at scale this can be an antipattern; it often makes sense to make additional custom properties on the root container and bind bidirectionally to that central property from both components as needed). If you have two components on _different_ windows, then you need to obtain a reference to the actual window object (e.g. [`system.gui.getWindow`](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-gui/system-gui-getWindow)), _or_ use a different strategy to share the data (e.g. [client tags](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/vision/vision-client-tags)) or one of a few other possible options, but the exact best approach will vary a bit. * * * For your second question, if I'm understanding correctly, take a look at this post/project; I think it covers what you're asking for: > [@Force configure cell update in power table](https://forum.inductiveautomation.com/t/force-configure-cell-update-in-power-table/16318/7): > > The much more efficient solution is to embed a custom editor, which will lookup the datasets dynamically whenever it’s appropriate. I’ve got a very simple example of this below; the project has an editable ‘Options’ dataset, and the available options in column B in the editable table will be dynamic based on the value in column A. The relevant code is here: root = self from javax.swing import JComboBox, DefaultCellEditor, DefaultComboBoxModel class CustomCellEditor(DefaultCellEditor):… ---
**Author:** ![Juan\_Gerardo\_Rivera](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@Juan\_Gerardo\_Rivera](https://forum.inductiveautomation.com/u/Juan_Gerardo_Rivera)\ **Post date:** [January 8, 2025, 6:09pm UTC](https://forum.inductiveautomation.com/t/power-table-dropdown-lists-data-from-query-not-appearing-ignition-7-9/100487/7 "2025-01-08T18:09:18Z")
To be more specific with my first question and second question, let's say all the data and details from the table will be registered per row, basically one item selected from the first row in column "Material" will cause a value that matches the first row in the column "ZM" row, in the following example, imagine I selected one item, in this case, the wooden box so I just chose the option from the dropdown list. Let's say I've selected the big wooden box from the dropdown just generated, when the item is selected I expect the ZM code ZM0140289 appearing in the column "Codificacion ZM' automatically. I don't know if it's better to use an empty container to store the details by using a button component to register all the line or if there is way of registering all the rows in another table. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/3/e3330bcff63693c960e8b70ca46d4f1b497cf3bc.png) I found that to change the values from a Power Table it is better to use the event onCellEdited(): My main objective for this application is to stop using paper for material's request submissions, so when a value is selected from the dropdown cell, the cell's value from column ZM should be change and then I will use another table or power table to receive the information and wait for approvals from supervisors. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/6/3630b2153388089e5c26f36dba4c3ce51b23ad6a.png)