I want a label to display the sum of a singular column of a named query. I am doing it in the expression for binding and have written this be it is not working.
"Total Hours Remaining: " + sum(system.db.runNameQuery("Select_DispatchList_Assembly", "5734"), 5)
5 is the index of the column and the 5734 is a parameter (and only parameter in the named query) that I am passing through
- It is
system.db.runNamedQuery
, with a d in named
- system.db.runNamedQuery returns a Dataset, not a PyDataset, which is what you need to use a sum (and a column number)
So something like this would work
ds = system.dataset.toPyDataSet(system.db.runNamedQuery('someQuery'))
result = sum(ds[0])
You can't mix the expression language with the scripting language in Ignition either.
1 Like
sum
is a python/jython keyword you can use on PyDataSet.
However now that you say that yes I see how they are doing it is wrong.
it shouldbe system.dataset.toPyDataSet(system.db.runNameQuery("Select_DispatchList_Assembly", "5734"))[5]
But the way it's used here (with the column index as second argument) suggests a mix up, as this is the signature for the expression function
1 Like
I'm talking about the running of a named query in an expression.
1 Like
Oh boy I need to get more coffee before I give anyone else any advice. That's now two things I missed I shouldn't have ha.
2 Likes
I'd use getColumnAsList
instead of converting to a pyDataSet
.
No, actually, I'd use an expression.
Thank you so much I can't believe I forgot the d in Named with the amount of times I've written that.
I will try running the code listed in a event configuration and set result to a custom property so that I can reference it in the expression like dkhayes117 pointed out.
Thank you all so much!
To be clear, you can mix the expression language with scripting indirectly, you just have to use runScript()
to do it.
I would just make an additional custom Named Query binding property with your NQ and reference the dataset with sum(myNewCustomPropDS, 5)
that way and avoid runScript
for this particular use case.
1 Like
Oh I didn't know that you could do that. This might be a dumb question but would I encapsulate the script in the runScript parenthesis? Or just after the script as call?
Frankly, you don't need events.
Show us the whole thing, because I feel like there's a very simple and clean way to do this.
I agree, but a secondary property might not even be needed, depending on what the original binding is.
No, you define it as a project script function, then call it with the runScript syntax. However, this is probably not the answer for this problem.
2 Likes
I can totally see a simple way of doing it. There aren't alot of components to it but I have below the full named Query:
SELECT
sch.job
,sch.oper_num
,job.job_date orig_job_date
,sch.pcs_per_lbr_hr pcs_per_hr
, route.qty_received / sch.pcs_per_lbr_hr * 1.0 total_planned_hrs
,item.item
,item.description item_description
FROM
jrt_sch_mst sch
LEFT JOIN job_mst job on sch.job = job.job and sch.suffix = job.suffix
LEFT JOIN jobroute_mst route on sch.job = route.job and sch.suffix = route.suffix and sch.oper_num = route.oper_num
LEFT JOIN JER_BI_Items item on job.item = item.item
LEFT JOIN JER_BI_WCs wc on route.wc = wc.wc
LEFT JOIN (SELECT distinct
job
, suffix
, oper_num
, next_oper
FROM
jobtran_mst
WHERE
next_oper is not null
) jtrans on sch.job = jtrans.job and sch.suffix = jtrans.suffix and sch.oper_num = jtrans.next_oper
LEFT JOIN jobroute_mst prevRoute on jtrans.job = prevRoute.job and jtrans.suffix = prevRoute.suffix and jtrans.oper_num = prevRoute.oper_num
WHERE
route.wc = :wc
and job.stat = 'R'
and prevRoute.complete = 1
and route.complete <> 1
and route.qty_received > 0
ORDER BY
job_date -- -- Sorted by Earliest Job Start Date
and I am just trying to take the 6 column or 5th index and sum that.
From there in a view I want to display this sum, for right now this project is in basic stage so we are not pulling all the wc and just starting with 5734
I don't know if this helps any to understand more?
Change your binding type to a query binding, then add an expression transform like this:
sum(value, 5)
Is this perspective? He hasn't said.
IMO and I know it's just a style thing but I think 2 custom properties is the cleanest nicest way to do it.
1 custom property for your dataset
1 custom property summing your dataset
If you ever get results that seem weird you can open up designer and manually inspect the dataset now (or maybe you display it for people with special roles for troubleshooting no designer access required).
I always like to be able to inspect the dataset that is generating the result for debugging.
it is perspective so sorry
Thank you so much. I knew it would be more simple than what I was making it out to be.
This worked perfectly!