How to advance to the next row in a table

Ignition
1

Hello,

I am evaluating Ignition and I am working on an application where I loop though the rows in a database table. I want to display the rows to the user one at a time and not advance to the next row until the users presses the “Next” button…

Below is the script for the “Next” button. When the script executes it loops through all of the rows and places the last row is placed in designated label field. Is there a way to pause the loop and retrieve input from the user? What is the “best practice” for displaying rows to users one at a time?

stmt = "SELECT hdr.batch_no batch_no, itm.segment1 item_no, itm.description DESCRIPTION, mtl.plan_qty
FROM gme_material_details mtl, mtl_system_items_b itm, gme_batch_header_vw hdr
WHERE mtl.inventory_item_id = itm.inventory_item_id
AND mtl.organization_id = itm.organization_id
AND mtl.batch_id = hdr.batch_id
and mtl.organization_id = hdr.organization_id and hdr.batch_id IN ( 3673698 ) "

results = system.db.runPrepQuery(stmt)
matLabel = event.source.parent.getComponent(‘MaterialLabel’)

for row in results:
matLabel.text = row[“DESCRIPTION”]

2

Hi Mike,

There’s many ways this could be done. It happens that your problem is solved nicely with a Python generator so I’m solving it that way.

First, create a custom dataset property somewhere, perhaps on your “Next” button. This will hold the results of your database query. Then create a counter. Each time a user presses the “Next” button retrieve the counter and use it as a row index into the dataset to get the current row. Increment the counter. When the counter reaches the number of rows in the dataset then set it back to 0 so the user can start at the first row again.

Here’s the code that would be put on the actionPerformed event on the “Next” button:

def cycleDataSet(component):
	"""
	This is a generator function
	A Python generator function is a function that returns a generator object.
	A generator object generates a new value each time its next() method is called.
	"""
	rowIndex = 0
	while True:
		if rowIndex == component.data.getRowCount():
			rowIndex = 0
		yield rowIndex
		rowIndex += 1

generator = event.source.getClientProperty("rowIndex")
if generator == None:
	stmt = "SELECT hdr.batch_no batch_no, itm.segment1 item_no, itm.description DESCRIPTION, mtl.plan_qty \
	FROM gme_material_details mtl, mtl_system_items_b itm, gme_batch_header_vw hdr \
	WHERE mtl.inventory_item_id = itm.inventory_item_id \
	AND mtl.organization_id = itm.organization_id \
	AND mtl.batch_id = hdr.batch_id \
	and mtl.organization_id = hdr.organization_id and hdr.batch_id IN ( 3673698 ) "
	results = system.db.runQuery(stmt)
	event.source.data = system.dataset.toDataSet(results)
	generator = cycleDataSet(event.source)
	event.source.putClientProperty("rowIndex",generator)

description = event.source.data.getValueAt(generator.next(),"DESCRIPTION")
matLabel = event.source.parent.getComponent('MaterialLabel')
matLabel.text = description

It would probably be better to make the database query a SQL Query binding on the “Next” button data property, or wherever you make the custom dataset property to hold your database query results.

Another way of solving this problem is making another custom property and using it as a counter. Each time the “Next” button is pressed increment the counter. Use the counter as an index into the dataset to get the current row.

3

I would have a dataset behind the scenes (root container?) with the full result set and also have an integer for the index. Then just increment the index and use it in an expression to get the value you want. Something like: try({Root Container.data}[{Root Container.index},"valueCol"],-1)

You can also pause your script for user input with the system.gui.inputBox() and system.gui.confirm() functions.