# Limit number of rows in a table **URL:** \ **Category:** Maker\ **Tags:** ignition81, perspective\ **Created:** [January 24, 2024, 9:40am UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683 "2024-01-24T09:40:44Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 24, 2024, 9:40am UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/1 "2024-01-24T09:40:44Z")
Hi, I have a MySQL database with 10 rows. I run multiple names queries to make up multiple tables. Not all of there tables need all 10 rows. Is there a way to limit the number of rows displayed in a table either to a fixed number, or to not display row if value is "null" ? If not, is there a way to remove the vertical scoll bar? Here is my tables, and i.e, the bottom one, i want to only display 1 row. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/1/116b67194880a181562e4fffab93c8186ee1fc92.png) ---
**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 24, 2024, 10:19am UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/2 "2024-01-24T10:19:01Z")
Try setting PROPS `virtualized : false` on each table. You may also need to check your POSITION `grow` and `shrink` property settings. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 24, 2024, 8:45pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/3 "2024-01-24T20:45:00Z")
Thanks, I've tried this now, but still the same... ๐Ÿคจ ---
**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 24, 2024, 8:50pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/4 "2024-01-24T20:50:21Z")
You haven't shown your configuration so it's difficult to help you. 1. What is the root container type? 2. Are the tables dropped straight into the root container or are they embedded in something else? 3. Post one of the table configurations. In the Project Browser, right-click on a table component, copy, paste it in here, select the code block and press the \ formatting button. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 24, 2024, 8:52pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/5 "2024-01-24T20:52:59Z")
```auto [ { "type": "ia.display.table", "version": 0, "props": { "virtualized": false, "selection": { "enableRowSelection": false }, "dragOrderable": false, "pager": { "bottom": false } }, "meta": { "name": "Table_5" }, "position": { "shrink": 0, "basis": "200px" }, "custom": {}, "propConfig": { "props.data": { "binding": { "config": { "parameters": { "Recipe": "concat(concat(\"`\",{[default]Brewfather_OPCUA/sRecipeName}),\"`\")" }, "queryPath": "Brewfather/Yeast" }, "type": "query" } } } } ] ``` ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 24, 2024, 8:54pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/6 "2024-01-24T20:54:15Z")
The root is flex container, and the table is dropped into another flex container. ๐Ÿ™‚ ---
**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 24, 2024, 8:58pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/7 "2024-01-24T20:58:28Z")
![Table position](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/3/73f5f1e75f12aa77a2debffee59b6381226f7a24.png) You have the table height fixed at 200px. Change it to `basis : auto`. ---
**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:** [January 24, 2024, 9:33pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/8 "2024-01-24T21:33:55Z")
When I want to limit the count of displayed rows, I either hard-code that limit in my Named Query, or I apply the following transform to my data (assuming a JSON data format): ```auto def transform(self, value, quality, timestamp): max_rows = 3 return value[0:max_rows] ``` ---
**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 24, 2024, 9:37pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/9 "2024-01-24T21:37:07Z")
I think the queries will only ever display a few rows. The problem is to eliminate the _empty rows_ so that the tables auto-size to fit the non-empty rows. I'm waiting to see if my basis solution solves the problem. ---
**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:** [January 24, 2024, 9:57pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/10 "2024-01-24T21:57:48Z")
These examples both strip out rows which have NoneType values OR boolean `false` values, or empty strings, and so might not be safe for all usages. They should be safe if all columns are strings/NoneType. ```python def transform(self, value, quality, timestamp): # ALL columns of row must have value max_rows = 3 original_pass = [] for row in value: if all(row.values()): original_pass.append(row) return original_pass[0:max_rows] ``` ```python def transform(self, value, quality, timestamp): # ANY column of row may have value max_rows = 3 original_pass = [] for row in value: if any(row.values()): original_pass.append(row) return original_pass[0:max_rows] ``` ---
**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 24, 2024, 10:08pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/11 "2024-01-24T22:08:35Z")
@cmallonee, I take back my previous comment. I can see the bottom table is displaying one row but the alternate row background colors indicate that there are many empty rows in the table. Carry on! ---
**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 25, 2024, 10:42am UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/12 "2024-01-25T10:42:18Z")
Why are your table bindings returning blank rows? Can you show us the query for the table that should return only one row? ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 25, 2024, 10:21pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/13 "2024-01-25T22:21:42Z")
Hi, The database is fixed at 10 rows, where all rows are blank, until data is pushed into the database. The data thats gets pushed into database only fills up some of the rows (not fixed number, but \<10). Therefore I want to display the rows that actually has any information. This is the SQL query: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/c/ac57a9a6cf4042191c5193b0c7d9bf0496781db3.png) ---
**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 25, 2024, 10:25pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/14 "2024-01-25T22:25:31Z")
Try, ```auto SELECT 'Yeast amount', 'Yeast unit', 'Yeast name' FROM Recipes.(Recipe) WHERE 'Yeast amount' IS NOT NULL ``` ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 25, 2024, 10:26pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/15 "2024-01-25T22:26:30Z")
Thank you! But i'm not quite sure where/how I should use this. I tried to use it as a transform script on the query, is that correct? It returns this error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/3/c3bd953db6f4d26f945639f010c43544ff4ae6ab.png) ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 25, 2024, 10:28pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/16 "2024-01-25T22:28:19Z")
Thank you ๐Ÿ™‚ But that returned this error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/0/c0bb5582ebb4c59cad6c0dca59a5f4078510fbe2.png) ---
**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 25, 2024, 10:50pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/17 "2024-01-25T22:50:43Z")
I used `(recipe)` instead of `{recipe}`. Sorry. Try this. ```auto SELECT 'Yeast amount', 'Yeast unit', 'Yeast name' FROM Recipes.{Recipe} WHERE 'Yeast amount' IS NOT NULL ``` This should work if the empty rows are NULLs. If they are empty strings, `""` then you might need, ```auto SELECT 'Yeast amount', 'Yeast unit', 'Yeast name' FROM Recipes.{Recipe} WHERE 'Yeast amount' IS NOT NULL AND 'Yeast amount' <> '' ``` (That's two single quotes on the last line.) ---
**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:** [January 25, 2024, 11:01pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/18 "2024-01-25T23:01:37Z")
> [@Vidar\_Hetlelid](#): > > It returns this error: Change the Return Format from `auto` to `JSON`. Unless your Table has bindings in place that are dependent on the DataSet structure of `Table.props.data`. If so, you should do something like this instead: ```python def transform(self, value, quality, timestamp): # ALL columns of row must have value # untested max_rows = 3 original_pass = [] ds = system.dataset.toPyDataSet(value) for row in ds: if all(row.values()): original_pass.append(row) return original_pass[0:max_rows] ``` ---
**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 25, 2024, 11:04pm UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/19 "2024-01-25T23:04:13Z")
You shouldn't need the script transform anymore. Insert a `return` on line 2 to suppress it. If it turns out you don't need it then delete the whole transform later. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [January 26, 2024, 8:22am UTC](https://forum.inductiveautomation.com/t/limit-number-of-rows-in-a-table/84683/20 "2024-01-26T08:22:22Z")
Thank you! That worked ๐Ÿ™‚ I needed to replace ' with ` for some reason, but now its working ๐Ÿ™‚