Dataset row value changes as data is added

Ignition
1

Hello all,

I have this application where data is updated on the text label on the screen as operator scans new parts for a stack of 8. Currently, each label has a 1 sec polling named query and I have 25-30 such screens with about 10 queries per screen which seems to be pushing limits.

I came up with a named query with several select statements with an UNION binded to a custom prop.

Select COALESCE(SUM(serial), 0) from parts where layer = :layer and layer_pos = :layer_pos and pallet = :pallet and stack_pos = 1

UNION

Select COALESCE(sum(serial), 0) from parts where layer = :layer and layer_pos = :layer_pos and pallet = :pallet and stack_pos = 2

UNION
etc..up to stack_pos=8

Say the first part is 1 and gets into Dataset[1,0]. When I scan the second part, the first part number gets moved to Dataset[2,0] and so on getting pushed down in the dataset table.

In the following, last 3 digits '492' was the first scan and it started at [1,0] and is now at [3,0]

image
image530×153 12.1 KB

Any suggestions on how to make it stay at [1,0] because that's what the label refers to?

TIA.

2

Why are you using a DB for this? Just scan into UI custom properties as your operator builds their "stack", then write it all to the DB at the end.

3

This is already an existing system, and I am also new to Ignition. Also, they have other functions on the screen like deleting, updating position etc where they needed direct DB access.

For now, I am trying to optimize without rewriting much as more perspective sessions like this are being added causing more congestions.

4

In that case, I would recommend (assuming you really need all the stack positions):

  • Creating a table that holds all possible stack positions.
  • Use a left join / subquery to return all rows of the pallet, whether a serial exists for it or not.
  • Use a lookup() expression on the label to point to the correct row in the dataset.

SQLFiddle example here:

5

Thanks Jordan.

I did exactly the same yesterday. Returning stack pos with serial from the database into dataset and used lookup function to populate serial based on stack pos.

I logged in today and you had the same thing :slight_smile: