Auto add a new record to DB

I have an ADD button that the operator can select to add a new record to the dB. But they sometimes forget to create the record.
I need to have a script that will automatically add a new record for the next day (date+1) at midnight and have the values from the text fields populate the new record.
Any help or ideas would be appreciated.

this is my working Add button.

Grab the values from the Text Fields.

date_time = event.source.parent.getComponent('date_time').date
p_1 = event.source.parent.getComponent('r1').floatValue
p_2 = event.source.parent.getComponent('r2').floatValue
p_3 = event.source.parent.getComponent('r3').floatValue
p_4 = event.source.parent.getComponent('p_4').text
p_5 = event.source.parent.getComponent('p_5').text
p_6 = event.source.parent.getComponent('p_6').text
p_7 = event.source.parent.getComponent('p_7').text
p_8 = event.source.parent.getComponent('p_8').text
p_9 = event.source.parent.getComponent('p_9').text
p_10 = event.source.parent.getComponent('p_10').text
p_11 = event.source.parent.getComponent('p_11').text
p_12 = event.source.parent.getComponent('p_12').text
p_13 = event.source.parent.getComponent('p_13').text
p_14 = event.source.parent.getComponent('p_14').text
p_15 = event.source.parent.getComponent('p_15').text
p_16 = event.source.parent.getComponent('p_16').text
p_17 = event.source.parent.getComponent('p_17').text
p_18 = event.source.parent.getComponent('p_18').text
p_19 = event.source.parent.getComponent('p_19').text
p_20 = event.source.parent.getComponent('p_20').text

Use those values in the Named Query.

system.db.runNamedQuery("Pg1/Pg_Add", {"date_time":date_time, "p_1":p_1, "p_2":p_2, "p_3":p_3,"p_4":p_4, "p_5":p_5, "p_6":p_6, "p_7":p_7, "p_8":p_8, "p_9":p_9,"p_10":p_10, "p_11":p_11, "p_12":p_12,"p_13":p_13, "p_14":p_14, "p_15":p_15,"p_16":p_16, "p_17":p_17, "p_18":p_18,"p_19":p_19, "p_20":p_20})

Refresh the table to immediately bring in the new row.

system.db.refresh(event.source.parent.getComponent("pg1_power_table"), "data")


I recommend moving the logic to a project script, then you can call it from anywhere.

Based on what you have I'm going to assume that all the components are in one container.

Rough scripting, as I don't know your layout. Feel free to adjust to your needs. :slight_smile:

Project script (I generally use one called 'project'. It's subtle, I know.)

def addRecord(dateIn, container)
	# Grab the values from the Text Fields.
	p_1 = container.getComponent('r1').floatValue
	p_2 = container.getComponent('r2').floatValue
	p_3 = container.getComponent('r3').floatValue
	p_4 = container.getComponent('p_4').text
	p_5 = container.getComponent('p_5').text
	p_6 = container.getComponent('p_6').text
	p_7 = container.getComponent('p_7').text
	p_8 = container.getComponent('p_8').text
	p_9 = container.getComponent('p_9').text
	p_10 = container.getComponent('p_10').text
	p_11 = container.getComponent('p_11').text
	p_12 = container.getComponent('p_12').text
	p_13 = container.getComponent('p_13').text
	p_14 = container.getComponent('p_14').text
	p_15 = container.getComponent('p_15').text
	p_16 = container.getComponent('p_16').text
	p_17 = container.getComponent('p_17').text
	p_18 = container.getComponent('p_18').text
	p_19 = container.getComponent('p_19').text
	p_20 = container.getComponent('p_20').text

	# Use those values in the Named Query.

	system.db.runNamedQuery("Pg1/Pg_Add", {"date_time":date_time, "p_1":p_1, "p_2":p_2, "p_3":p_3,"p_4":p_4, "p_5":p_5, "p_6":p_6, "p_7":p_7, "p_8":p_8, "p_9":p_9,"p_10":p_10, "p_11":p_11, "p_12":p_12,"p_13":p_13, "p_14":p_14, "p_15":p_15,"p_16":p_16, "p_17":p_17, "p_18":p_18,"p_19":p_19, "p_20":p_20})

	# Refresh the table to immediately bring in the new row.

	system.db.refresh(container.getComponent("pg1_power_table"), "data")

The script on the button changes to

date_time = container.getComponent('date_time').date
container = event.source.parent
project.addRecord(date_time, container)

Add an exression on the container (my example is called midnight):
now() = midnight(now())

Add a propertyChange script to the container:

if event.propertyName == 'midnight':
	date_time ='date_time').date, 1)
	container = event.source
	project.addRecord(date_time, container)
1 Like

That said, all of this is pretty fragile, as it relies on the client having that window open at midnight.


True, so running a script in the gateway would be better to do.

Yes, but the gateway can't access the Windows. If these are all user entry, there's not much you can do.

Why not a gateway scheduled script?

The gateway can't access vision components. If, however, they are populated from somewhere else, it's a possibility.

You might want to consider having gateway tags for each of these values and have the components bi-directionally bound to them and run the script (grabbing from the gateway tags instead) at midnight in a gateway scope - most likely a gateway scheduled script set to run at midnight.

Then hypothetically a user could set the values that should be recorded at midnight earlier in the day, turn off the client computer at 10 PM, and the script and values would still record.