If I had a hundred queries on a page and I wanted to replace them with just 1 query, what would be the best way to distribute the information to labels?
My idea right now is to make custom properties on my view from the query.
I want to make 12 cards of data per production line.
My query has 12 rows of 20 columns.
I assume you have a query per card right now? What's your current setup like? Typically when I grab data from a dataset to a component, I make a custom property on the component I call initialValue which is an expression of the sort try({Root Container.initalData}[0, 'someColumn'], 'someDefaultValue'). I would imagine and hope this translates fine to perspective.
You may be able to combine the queries or you may not but it's hard to tell without more information. If they're all selecting the same columns with slightly different where clauses like SELECT someColumn FROM table WHERE card=1 and then SELECT someColumn FROM table WHERE card=2 you can combine them with a UNION to convert all these into one
SELECT someColumn FROM table WHERE card=1
UNION
SELECT someColumn FROM table WHERE card=2
etc
And then the try statement would need to reference the correct row, not just 0 in this part [0, 'someColumn'].
If the queries are all using the same where clause and are just selecting different columns, or each component is doing a SELECT on a single column with the same where clause, I would definitely combine those into a single query and extract with an expression.
Again though I think we'll need more info on your current setup to give better feedback.
That is from @pturmel 's Simulation Aids which is a module I do highly recommend, it's free, and I think it would make your situation much more manageable with an expression.
You could do something like this
ds = system.dataset.toDataSet(system.db.runQuery("SELECT * FROM someTable"))
def getRow(ds, rowInd):
# if you want it as a basic dataset, otherwise just system.dataset.toPyDataSet(ds)[rowInd] for pyDS
return system.dataset.toDataSet(list(ds.columnNames), [system.dataset.toPyDataSet(ds)[rowInd]])
rowData = getRow(ds, 0)
to get the row out of a ds as a dataset, but I doubt it would be as performant as downloading the Simulation Aid's module and using view.
My personal opinion is if you can install the module, just do it. It will make all your dataset manipulations much easier to handle via expressions.
Not for nothing either, but if this system/perspective app is only going to be used by a single person or a handful of people, 12 queries isn't a lot nor should it take a lot of time on a well indexed table where you're selecting based on the primary key, and if that is the case, I would probably just be doing the query inside of each card view, and providing the primary key as a parameter. I would only bother trying to reduce the query here if I knew for a fact a ton of people were going to use it, or people started complaining of slowness.
I do this by having my query in a tag, then have one expression tag per row using lookup(). Do note that my queries only return a few dozen rows or less.
Create a sample dataset for us, by hand if necessary, sanitizing any private information. Copy the dataset to the clipboard, and paste it here like you would for code. Then show us what results you want picked out of it for your application.
Keep in mind that Ignition's expression language natively supports picking out values from a dataset by row index and by column (index or name).
Do you mean each production line has 12 cards with one value each, so you have 20 production lines with 12 values per column? Or that each card is a production line, so each production line is 1 row in your data set?
The second is usually more common, so I'll make that assumption for starters. Correct me if it's wrong.
If you have a column (e.g. 'Name') with the name of each line, and a column of values (e.g. 'Speed'), then you can use the lookup function to get the speed of line B2 as follows:
lookup({data}, "B2", 0, "Name", "Speed")
that is, check "Name" column for "B2" and return the value of "Speed" in that row, and return 0 if nothing found.
Line per column case
If you have the second case where each production line is its own column, then I hope you have an extra column with the type of info labeled (e.g. "InfoType", in which case it would be
lookup({data}, "Speed", 0, "InfoType", "B2")
that is, check "InfoType" column for "Speed" and return the value of "B2" in that row.
I think two view custom property query bindings updating every 30 seconds
and 12x20 expression bound labels will be more efficient than anything else I could do.