Date Parameter in a Named Query

Ignition
,
1

Hello all,

I have an inventory management project that I created where the user manually enters part #, part quantity, transaction type, and any notes they want to tie to the transaction. I am also grabbing the user from the system.username tag.

I have a datetime field in my data table where this information is being stored. Initially, I was just using CURRENT_TIMESTAMP to create a timestamp of the transaction based on the time it took place. Here is the initial named query:

INSERT INTO Hardware_Inventory (HardwareID, TransactionAmount, TransactionType, TransactionNotes, TransactionTime, TransactionPerson)
	VALUES (
	:HardwareIDParam,
	:QuantityParam,
	:TransactionTypeParam,
	:TransactionNotesParam,
	CURRENT_TIMESTAMP,
 	:TransactionPersonParam 	    
	)

This worked great, but after using it for a bit, we realized that we would like the ability to override the current timestamp and input our own dates. To do this, I started by adding a parameter to my named query:

INSERT INTO Hardware_Inventory (HardwareID, TransactionAmount, TransactionType, TransactionNotes, TransactionTime, TransactionPerson)
	VALUES (
	:HardwareIDParam,
	:QuantityParam,
	:TransactionTypeParam,
	:TransactionNotesParam,
	 :TransactionDateParam ,
 	:TransactionPersonParam 	    
	)

Then, I added a "Date Override" checkbox along with a calendar component to my main Vision window. Upon hitting the submit button for this window, I am running a script that looks to see if the "Date Override" checkbox is selected. If it is not, I am writing to a client tag that I created called "TransactionDate" using the system.date.now() function. If the checkbox is selected, then the client tag updates based on the latest change to the calendar. Here is the script for the submit button on this page:

window = system.nav.openWindow('Pop-Up Windows/Submit Inventory Transaction')
system.nav.centerWindow(window)

if (event.source.parent.getComponent('CheckBox').selected == 0):
	system.tag.write("[client]TransactionDate", system.date.now())

And here is the script for the calendar using the propertyChange event handler:

system.tag.write("[client]TransactionDate", event.source.date)

When the user hits the "Submit" button, another pop-up window is displayed so they can review their entry before running the DB insert query. This is where I am running into the problem.

I have the date in two places - the client tag that is of DateTime data type and a data label that is simply bound to the client tag. The data label is obviously of the text data type.

I am launching the system.db.runNamedQuery function upon the user selecting the "Commit Transaction" button. However, it seems like however I try to enter the TransactionDate parameter I get hit with a datatype error upon execution as the SQL statement doesn't seem to like what I am giving it.

Here are a couple of the scripts that I have tried:

if (event.source.parent.getComponent('TransactionTypeLabel').text == 'Adjust -' or 
	event.source.parent.getComponent('TransactionTypeLabel').text == 'Upstairs'):

	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value * -1,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": "[client]TransactionDate"
			}
else:
	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": "[client]TransactionDate"
			}


print(system.db.runNamedQuery("INSERT Inventory Transaction", params))

window = system.nav.openWindow('Pop-Up Windows/Transaction Details')
system.nav.centerWindow(window)
system.nav.closeParentWindow(event)


if (event.source.parent.getComponent('TransactionTypeLabel').text == 'Adjust -' or 
	event.source.parent.getComponent('TransactionTypeLabel').text == 'Upstairs'):

	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value * -1,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": [client]TransactionDate
			}
else:
	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": [client]TransactionDate
			}


print(system.db.runNamedQuery("INSERT Inventory Transaction", params))

window = system.nav.openWindow('Pop-Up Windows/Transaction Details')
system.nav.centerWindow(window)
system.nav.closeParentWindow(event)

if (event.source.parent.getComponent('TransactionTypeLabel').text == 'Adjust -' or 
	event.source.parent.getComponent('TransactionTypeLabel').text == 'Upstairs'):

	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value * -1,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": event.source.parent.getComponent('TransactionDateLabel').text
			}
else:
	params = {
		"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
		"QuantityParam": event.source.parent.getComponent('QuantityLabel').value,
		"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
		"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
		"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
		"TransactionDateParam": event.source.parent.getComponent('TransactionDateLabel').text
			}


print(system.db.runNamedQuery("INSERT Inventory Transaction", params))

window = system.nav.openWindow('Pop-Up Windows/Transaction Details')
system.nav.centerWindow(window)
system.nav.closeParentWindow(event)

I have also tried to type cast using the system.date.parse function as well as the toDate function with no luck.

Any help anyone can provide would be much appreciated. Happy to send more code or screenshots if needed.

Thanks!

2

I cleaned up your script a bit. Notice that when retrieving the tag value, you have to read it with a system function or reference a component property that is bound to the tag. I showed it as reading the tag.

transType = event.source.parent.getComponent('TransactionTypeLabel').text
transDate = system.tag.readBlocking(["[client]TransactionDate"])

params = {
	"HardwareIDParam": int(event.source.parent.getComponent('BagIDLabel').text),
	"QuantityParam": event.source.parent.getComponent('QuantityLabel').value,
	"TransactionTypeParam": event.source.parent.getComponent('TransactionTypeLabel').text,
	"TransactionNotesParam": event.source.parent.getComponent('TransactionNotesLabel').text,
	"TransactionPersonParam": event.source.parent.getComponent('TransactionPersonLabel').text,
	"TransactionDateParam": transDate[0].value
		} 

# Override applicable parameters
if transType in ('Adjust -', 'Upstairs'):
	params["QuantityParam"] *= -1

print(system.db.runNamedQuery("INSERT Inventory Transaction", params))

window = system.nav.openWindow('Pop-Up Windows/Transaction Details')
system.nav.centerWindow(window)
system.nav.closeParentWindow(event)
1 Like
3

This is fantastic and works perfectly! Thank you so much.

I did not know about the readBlocking system function - this will come in handy. Also, I like the way you cleaned up my if statement with the dictionary override.

Very helpful. Thank you very much!

1 Like
4

Bookmark these two pages in your browser, you'll be glad you did :slight_smile:
System Functions | Ignition User Manual
Expression Functions | Ignition User Manual

2 Likes