Limit number of rows in a table

Maker
,
1

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
image1473×802 33.2 KB

2

Try setting PROPS virtualized : false on each table.
You may also need to check your POSITION grow and shrink property settings.

3

Thanks,

I've tried this now, but still the same... :face_with_raised_eyebrow:

4

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.
5 
[
  {
    "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"
        }
      }
    }
  }
]
6

The root is flex container, and the table is dropped into another flex container. :slight_smile:

7

Table position

You have the table height fixed at 200px.
Change it to basis : auto.

8

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

def transform(self, value, quality, timestamp):
	max_rows = 3
	return value[0:max_rows]
9

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.

10

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.

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]

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]
1 Like
11

@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!

12

Why are your table bindings returning blank rows?
Can you show us the query for the table that should return only one row?

13

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
image868×398 16.3 KB

14

Try,

SELECT
  'Yeast amount',
  'Yeast unit',
  'Yeast name'
FROM Recipes.(Recipe)
WHERE 'Yeast amount' IS NOT NULL
15

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
image881×1239 64.7 KB

16

Thank you :slight_smile: But that returned this error:
image

17

I used (recipe) instead of {recipe}. Sorry. Try this.

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,

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

18

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:

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]
19

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.

20

Thank you! That worked :slight_smile:

I needed to replace ' with ` for some reason, but now its working :slight_smile: