I am on 8.1.19 version.
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.
I think I could just be done if this function existed:
data.getRowIndex(rowName)
I have exactly one query on my view custom property.
I don't know how to just get the speed of my B2 line from the query to my labels.
All the dataset functions are setup for getting column information.
If your view only has one query, I don't think you can reduce it anymore without the view becoming meaningless.
Is your actual issue is how fast your components populate from said dataset?
Is the query slow? Have you run an explain statement and indexed appropriate columns
My issue is there aren't any row functions for datasets.
So I don't know how to get data out of my dataset.
Is your setup your dataset set has 12 rows and you're trying to get one row for each card, row 0 for card 1, row 1 for card 2, etc?
Great question.
I think that is what I want to do.
I don't know how to just get that row where I have data for line 'B2' though for example.
In the past, I pulled data out with the column functions.
I just have no idea how to get row data out though.
The only examples I saw use view(), and I don't see that in the expression functions.
I don't know where that comes from. Only throws errors for me.
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.
Could you show me how to use lookup to get a row?
I have not had success with it.
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).
I will post it.
I have been put on a different thing, but I will post asap.
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.
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.
lookup({data}, "B2", 0.0, "LineName", "Speed")
Thanks very much.
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.
I am not certain.