# Select x rows from Dataset for template repeater

**URL:** <https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [April 28, 2023, 2:10pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024 "2023-04-28T14:10:00Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 2:10pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/1 "2023-04-28T14:10:00Z")

</div>

Ignition 8.x / Vision

I am doing a search on a DB table to generate a dataset for a template repeater. When getting to large of a result search it can bog down the system due to loading all the templates. I don't know what the per display limit for the repeater is but if I just have the Query loading full dataset no slowdown but if repeater is connected and results are a large number of rows ignition will hang and eventually shutdown client.

What I am thinking of doing is

1. parameter on root container that contains the named query for the search --\> full search dataset
2. Template repeater that grabs Row x -\> 20+x from that dataset to make it's dataset for the repeater showing the first 20 rows of data
3. Add a button that will cause the repeater to shift to Row 20+x as the start point.

This way it never tries to load more the 20 rows into the template at a time but operator can still page through to be able to see full search results.

Part I am currently stuck on is how do I get the full dataset into a filtered dataset that changes based on user input from on screen. I don't see a way to do this in expression so thinking I would need to runScript to generate the sub dataset. Before I go down that route looking for any other ideas / solutions.

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [April 28, 2023, 2:13pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/2 "2023-04-28T14:13:36Z")

</div>

I probably wouldn't try this on a binding. Simply use a propertyChange event on the user input and script the filtering.

---

<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:** [April 28, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/3 "2023-04-28T14:33:54Z")

</div>

The template repeater is a memory hog. Give the client as much memory as you can, and use either a scripted binding or a LIMIT clause in your SQL to ensure there aren't too many rows. You might find my `view()` expression function helpful in local filtering cases (part of the free [Simulation Aids module](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids)).

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 4:17pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/4 "2023-04-28T16:17:53Z")

</div>

> [@pturmel](#):
>
> The template repeater is a memory hog. Give the client as much memory as you can, and use either a scripted binding or a LIMIT clause in your SQL to ensure there aren't too many rows. You might find my `view()` expression function helpful in local filtering cases (part of the free [Simulation Aids module](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids)).

I added the Module  
but I think I am missing something in calling the view() function.

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 28, 2023, 4:25pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/5 "2023-04-28T16:25:07Z")

</div>

View is an expression, not a scripting thing. So you can make a custom dataset property utilizing the `view()` expression and then grab that in your scripting.

Additionally though if you'd like there is the convenience utilities module [Ignition Extensions - Convenience utilities for advanced users](https://forum.inductiveautomation.com/t/ignition-extensions-convenience-utilities-for-advanced-users/64615)

You could also install that and then you can use the expression language in scripting as well with `system.util.evalExpresssion` if you really want to use view in the scripting directly.

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 4:34pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/6 "2023-04-28T16:34:32Z")

</div>

> [@bkarabinchak.psi](#):
>
> ke there is the conv

Ahh, thanks will play with it in expression instead of script.

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 6:28pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/7 "2023-04-28T18:28:16Z")

</div>

Great that works.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/8/d82f05788e43a37836cb42243e5cd0a55f6a265d.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/4/c486ab2a5fc81ee95b4b7da84727c1d91f06eccd.png)  
SearchData = Namedquery  
SelectedData

```auto
view({Root Container.Template Repeater 1.SelectString}, {Root Container.Template Repeater 1.SearchData})

```

SelectString

```auto
Concat("Select PLCNumber, InputNumber Where RowNumber in range(",{Root Container.Template Repeater 1.StartRow}, ",",{Root Container.Template Repeater 1.RowCount}+{Root Container.Template Repeater 1.StartRow},")" ) 

```

TotalRows

```auto
Len({Root Container.Template Repeater 1.SearchData})

```

RowCount and StartRow are set buy navigation next/prev which are Visible on/off based on high/low limit based on math with Totalrows startrow and rowcount

---

<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:** [April 28, 2023, 6:31pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/8 "2023-04-28T18:31:00Z")

</div>

> [@rfolkes](#):
>
> `view({Root Container.Template Repeater 1.SelectString}`

Ewww! Don't recompute the select string. That will have horrible performance. Use the `view()` function's support for `args` to process dynamic values without reparsing the PseudoSQL.

---

<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:** [April 28, 2023, 6:35pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/9 "2023-04-28T18:35:38Z")

</div>

Also note the `view()` injects the dataset row index as variable `_r` for your use in a WHERE clause. So, something like this:

```auto
view("SELECT * WHERE args[0] <= _r < args[1]",
   {path.to.complete.dataset},
   {path.to.first.index.int.prop},
   {path.to.after.last.index.int.prop})

```

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 6:55pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/10 "2023-04-28T18:55:56Z")

</div>

> [@pturmel](#):
>
> ng. That will have horrible performance. Use the `view()` function's support for `args` to process dynamic v

thanks, change made.

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 7:07pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/11 "2023-04-28T19:07:14Z")

</div>

just for my clarification how does computing the select string change the performance? Is it an order of operations in that on change it recalcs the view statement. then changes the select string which causes a change so it does the view statement again?

---

<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:** [April 28, 2023, 7:10pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/12 "2023-04-28T19:10:57Z")

</div>

Any time the first argument to `view()` changes, or the dataset's column names change, the internal jython script that solves the request must be regenerated and then compiled before it is executed with the rest of the arguments. If the string and dataset columns still match, the previously compiled jython is simply executed with the fresh arguments.

Turn on the function's DEBUG logger to get the actual internal script it generated, and get timing details for every execution.

---

<div class="post-metadata">

**Author:** ![rfolkes](https://avatars.discourse-cdn.com/v4/letter/r/e95f7d/32.png) [@rfolkes](https://forum.inductiveautomation.com/u/rfolkes)\
**Post date:** [April 28, 2023, 8:14pm UTC](https://forum.inductiveautomation.com/t/select-x-rows-from-dataset-for-template-repeater/73024/13 "2023-04-28T20:14:38Z")

</div>

> [@pturmel](#):
>
> mn names change, the internal jython script that so

that makes sense. Thanks.