Hello Everyone,
I am trying to write a query for a query tag.
This is the code I wrote:
SELECT CONCAT (RIGHT('000'+CAST("ord no" AS int),7), RIGHT('000'+CAST("mdl cnt" AS VARCHAR(3)),3), RIGHT('00'+CAST("PAT POS" AS VARCHAR(3)),2), RIGHT('0000'+CAST(qty AS VARCHAR(4)),4)) as Barcode
FROM Sheet1$
WHERE
"Lot No" = {[default]LotManual.value}
AND
"Bin" = {[default]BinManual.value}
The lot and Bin data are coming from numeric text fields, however, I want the query to start when the operator enters both values.
Currently, the query is being run as soon as one of them, lot, or bin is changing.
Should I change something in its properties? Or should I write a script for that?
Thank you!
I believe that is expected behavior - that the query is being run every time one of the tags it depends on changes.
Is there a reason you don’t want it to re-evaluate each time?
1 Like
That is correct! However, for some projects, like the one you may see in the SQL table below this is not going to work properly.
For example, in the picture below, we may have many rows with Lot No= 112841 but each of them may have different Bin numbers.
Imagine that the operator enters 112841 as lot no first and he/she does not enter the bin, but bin no was saved from the previous query, so in this situation, the query is going to return a row that has lot=112841 and bin=47. However we do not have bin=47 for that specific lot and, therefore all other tags that are being displayed by that SQL table or being returned by that query are going to be shown red with error on the screen.
Oh ok so the real issue is that after completion, the next go around you have the entries from the last time? Because {[default]BinManual.value} was 47 from the last go around.
What about clearing those two tags after whatever process this is? Like setting bin to -1. Having {[default]BinManual.value} = -1 will return no rows until the operator enters a new one.
A few things too - you query is dependent on an operator entering entries. But I see you’re using gateway tags {[default]BinManual.value}. If you only ever have one client, it’s ok, but this is not scalable at all - even just adding a second client will have one client writing over the other in the event that two operators are both doing this at the same time. I highly recommend making these client tags (if even that).
It looks like this is for a window yes? I would make a named query with your SELECT statement and have two parameters- lot and bin, that will go where your client tags are now.
Make an boolean property on the root container of your window, maybe call it something like queryReadyToRun or something in that vain, and make it an expression like {Root Container.Lot Number.Integer} > 0 && {Root Container.Bin Number.Integer} > 0 or whatever components you are using - the key here is that that this expression is True when you are ready to run the named query.
Then in your root container property change,
if event.propertyName == 'queryReadyToRun' and event.newValue == True:
params = {'lot': event.source.path.to.lot.integer, 'bin': event.source.path.to.bin.integer}
event.source.path.to.table.data = system.db.runNamedQuery('newNamedQuery',params)
if event.propertyName == 'queryReadyToRun' and event.newValue == False:
# Do what you want here when the query isn't read, or if say the operator cleared the lot/bin number
One of the overlooked coding skills, which isn’t really coding, is getting comfortable with scope. Is this something that is supposed to run on the entire server one time (gateway scope). Or is this something that presumably could have many users doing the same thing independently (client scope).
2 Likes
Thank you very much for your response @bkarabinchak.psi . I really appreciate it.
I found an easy way to do it so I thought I should share it here as well:
I actually used two tags called LotNumeric and BinNumeric for two numeric entry text fields. Then I used a push button and action performed to save those two values in the tags I want to use later on- LotManual and BinManual. So basically I saved my tags in other tags as a buffer so that when the operator enters the first tag it does not automatically run the query and instead wait for the other number (Bin) to be entered. At the end the operator should click on the push-button to run the query.
This is the script for the push button, I used action performed:
system.tag.writeBlocking("[default]LotManual.value", event.source.parent.getComponent('LotNumeric').longValue)
system.tag.writeBlocking("[default]BinManual.value", event.source.parent.getComponent('BinNumeric').longValue)
