currentValue.value and previousValue.value

Hello all,

I am currently struggling with a portion of my script concerning currentValue.value and previousValue.value on a property change script. I will include my script below before going into detail on my issue:

def valueChanged(self, previousValue, currentValue, origin, missedEvents):
	#Variables
	FirstName = self.props.selection.data[0].FirstName
	LastName = self.props.selection.data[0].LastName
	PreviousYearlyOvertime = self.props.selection.data[0].PreviousYearlyOvertime
	WeeklyOvertime = self.props.selection.data[0].WeeklyOvertime
	SaturdayOvertime = self.props.selection.data[0].SaturdayOvertime
	SaturdayRejected = self.props.selection.data[0].SaturdayRejected
	SundayOvertime = self.props.selection.data[0].SundayOvertime
	SundayRejected = self.props.selection.data[0].SundayRejected
	HolidayOvertime = self.props.selection.data[0].HolidayOvertime
	SaturdayHours = self.props.selection.data[0].SatHrs
	SundayHours = self.props.selection.data[0].SunHrs
	TotalWeekOvertime = float(WeeklyOvertime) + float(SaturdayOvertime) + float(SaturdayRejected) + float(SundayOvertime) + float(SundayRejected) + float(HolidayOvertime)
	TotalYearlyOvertime = float(TotalWeekOvertime) + float(PreviousYearlyOvertime)
	OvertimeWeek = self.getSibling("Table Date").props.text
	Today = system.date.now()
	Time = system.date.format(Today, "MM/dd/YYYY hh:mm:ss a")
	Date = system.date.format(Today, "MM/dd/YYYY")
	userID = self.session.props.auth.user.userName
	Type = "Edited"
	LogEntry = userID + " edited " + LastName + ", " + FirstName + " Weekly overtime to " + str(WeeklyOvertime) + " for the date of " + str(Date) + "."
	params = {"FirstName" :FirstName, "LastName" :LastName, "OvertimeWeek" :OvertimeWeek, "WeeklyOvertime" :WeeklyOvertime}
	params2 = {"FirstName" :FirstName, "LastName" :LastName, "OvertimeWeek" :OvertimeWeek, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime}
	params4 = {"FirstName" :FirstName, "LastName" :LastName, "TotalYearlyOvertime" :TotalYearlyOvertime}
	logParams = {"Time" :Time, "LogEntry" :LogEntry, "Type" :Type}

	#Runs query to update database table
	system.db.runNamedQuery("Overtime/Charge Hours/Weekly Overtime Update", params)

	#Runs query to update week and YTD totals
	system.db.runNamedQuery("Overtime/Charge Hours/Total Week And Year", params2)

	#Runs query to return new table
	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Charge Hours/Mechanics Pull", {"OvertimeWeek" :OvertimeWeek}))
	NewValue = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (columns['LastName'], columns['FirstName']))
	self.props.data = NewValue

	#Variables for query
	Change = float(currentValue.value) - float(previousValue.value)
	system.tag.writeBlocking("[default]MOS/Mechanic Weekly", Change)
	Changed = system.tag.readBlocking("[default]MOS/Mechanic Weekly Change")[0].value
	NewSaturdayHours = float(SaturdayHours) + Changed
	params3 = {"FirstName" :FirstName, "LastName" :LastName, "NewSaturdayHours" :NewSaturdayHours}
	#Runs query to update SatHrs on MOS_Employee table
	system.db.runNamedQuery("Overtime/Charge Hours/Saturday Hours Update", params3)

	#Runs query to update MOS_Signup
	system.db.runNamedQuery("Overtime/Charge Hours/Signup Update", params4)

	#Runs query to add to log
	system.db.runNamedQuery("Configuration/Logs/Log Entry", logParams)

I have this script on valueChanged on my table within selection.data.0.WeeklyOvertime. When the value changes, it runs several queries. First, it runs Weekly Overtime Update, this query sets the new weekly overtime value to the table. Next, it runs Total Week and Year, this query sets the new totals for the week and YTD. The fourth query updates another table with the YTD, and the fifth query inserts a changes into a log. The third query is the query I am really struggling with to get functioning quite the way I want. I have the variable Change equal to the total from currentValue.value - previousValue.value. Because of the table refresh, it sets the value back to 0. I have it write to a tag to try and hold this value, though it seems very inconsistent. Every time on the first click it also runs the query. I've tried things like "if currentValue.value != WeeklyOvertime, then click a cell and it still runs when I don't want it to. I'm trying to find the change so that I can subtract or add it to a column on another table to show the updated value, whether it be a few more or a few less hours. Does anyone have any advice on a better way to complete this task?

It seems to me you are trying to run your process in your UI. Don't do that. Use timer events or expression tags on a group rate or something else in gateway scope to perform continuous calculations.

I want the user to be able to see the changes they are making realtime. Is this possible from the gateway scope? I've done a lot from the UI so this would be new to me. What's wrong with doing something like this from the UI?

If the value changes you are dealing with are coming from the user, and only from the user, then scripting with previousValue.value is reasonable. If value changes can come from both user and bindings, then you will have a cluster [expletive], as the two sources will have their previous values mixed together. If value changes are coming only from outside the UI, those operations should be in gateway scope so multiple UIs cannot conflict with each other, and the operations happen even when no UI is open.

So these tables are automatically written over the weekend with no binding through a gateway script, the only time they would change is when the manager would need to add/subtract hours. The change would update the tables accordingly, the only real challenging part is getting the difference to update the MOS_Employee table saturday and sunday hours (add or subtract change from previous to current).

I wouldn't do such calculations during editing. I would load the stored data into a custom property on page open, edit in place, then perform the difference calculations in the script called from a save button, just before navigating away from the view.

That makes sense to me and might be easier with the 4 tables I have. I wanted it to be more "interactive" but I'm thinking the save button would be easier to code. My only problem is I'm having a hard time visualizing how I would compare the two, would it be through a for loop? Would I literally just copy the "data" portion of my table on startup to a custom property, then later compare the data vs the custom property in the save button?

table1data = self.getSibling("Mechanics Table").props.data
for row in range(table1data.getRowCount()):
  values = X
  calculate difference here
  run queries?

Going to test this now on a copied page and see how that works.

Yes, exactly. Or perhaps with @PGriffith's tools module.

In writing this script I ran into the question -- how do I run the for loop for the custom property against the now changed table to compare the values?

Either. But both datasets need to be in your code so you can compare element by element between them. {If your editing can add/remove/reorder the rows, this will be very difficult.}

It's not liking the way I'm referencing the tables, should I make a "temp" table and have the changing of values change the temp table, then run a query to pull that into the for loop you think?

def runAction(self, event):
	#Variables
	Today = system.date.now()
	Time = system.date.format(Today, "MM/dd/YYYY hh:mm:ss a")
	Date = system.date.format(Today, "MM/dd/YYYY")
	OvertimeWeek = self.getSibling("Table Date").props.text
	#userID = self.session.props.auth.user.userName
	#Maybe factor in log after I get it working
	#Type = "Edited"
	#LogEntry = userID + " edited " + LastName + ", " + FirstName + " Weekly overtime to " + str(WeeklyOvertime) + " for the date of " + str(Date) + "."
	
	MechanicsTable = self.getSibling("Mechanics Table").props.data
	for row in range(MechanicsTable.getRowCount()):
		#Variables
		FirstName = MechanicsTable.getValueAt(row, "FirstName")
		LastName = MechanicsTable.getValueAt(row, "LastName")
		WeeklyOvertime = MechanicsTable.getValueAt(row, "WeeklyOvertime")
		SaturdayOvertime = MechanicsTable.getValueAt(row, "SaturdayOvertime")
		SaturdayRejected = MechanicsTable.getValueAt(row, "SaturdayRejected")
		SundayOvertime = MechanicsTable.getValueAt(row, "SundayOvertime")
		SundayRejected = MechanicsTable.getValueAt(row, "SundayRejected")
		HolidayOvertime = MechanicsTable.getValueAt(row, "HolidayOvertime")
		PreviousYearlyOvertime = MechanicsTable.getValueAt(row, "PreviousYearlyOvertime")
		SatHrs = MechanicsTable.getValueAt(row, "SatHrs")
		SunHrs = MechanicsTable.getValueAt(row, "SunHrs")
		SaturdayHoursTotal = float(SatHrs) + float(WeeklyOvertime) + float(SaturdayOvertime) + float(SaturdayRejected)
		SundayHoursTotal = float(SunHrs) + float(HolidayOvertime) + float(SundayOvertime) + float(SundayRejected)
		TotalWeekOvertime = float(WeeklyOvertime) + float(SaturdayOvertime) + float(SaturdayRejected) + float(SundayOvertime) + float(SundayRejected) + float(HolidayOvertime)
		TotalYearlyOvertime = float(TotalWeekOvertime) + float(PreviousYearlyOvertime)

		OldMechanicsTable = self.parent.custom.MechanicsTable
		for row in range(OldMechanicsTable.getRowCount()):
			#Variables
			OldFirstName = OldMechanicsTable.getValueAt(row, "FirstName")
			OldLastName = OldMechanicsTable.getValueAt(row, "LastName")
			OldWeeklyOvertime = OldMechanicsTable.getValueAt(row, "WeeklyOvertime")
			OldSaturdayOvertime = OldMechanicsTable.getValueAt(row, "SaturdayOvertime")
			OldSaturdayRejected = OldMechanicsTable.getValueAt(row, "SaturdayRejected")
			OldSundayOvertime = OldMechanicsTable.getValueAt(row, "SundayOvertime")
			OldSundayRejected = OldMechanicsTable.getValueAt(row, "SundayRejected")
			OldHolidayOvertime = OldMechanicsTable.getValueAt(row, "HolidayOvertime")
			OldSatHrs = OldMechanicsTable.getValueAt(row, "SatHrs")
			OldSunHrs = OldMechanicsTable.getValueAt(row, "SunHrs")
			OldSaturdayHoursTotal = float(OldSatHrs) + float(OldWeeklyOvertime) + float(OldSaturdayOvertime) + float(OldSaturdayRejected)
			OldSundayHoursTotal = float(OldSunHrs) + float(OldHolidayOvertime) + float(OldSundayOvertime) + float(OldSundayRejected)

			if (OldSaturdayHoursTotal != SaturdayHoursTotal) or (OldSundayHoursTotal != SundayHoursTotal):
				#Variables
				NewSaturdayHours = SatHrs + (SaturdayHoursTotal - OldSaturdayHoursTotal)
				NewSundayHours = SunHrs + (SundayHoursTotal - OldSundayHoursTotal)

				#Query to update WeeklyOvertime value on MOS_HoursChargedHistorical
				system.db.runNamedQuery("Overtime/Charge Hours/Weekly Overtime Update", 
					{
						"WeeklyOvertime": WeeklyOvertime, 
						"FirstName": FirstName,
						"LastName": LastName, 
						"OvertimeWeek" :OvertimeWeek
					}
				)

				#Query to update weekly OT and YTD OT on MOS_HoursChargedHistorical
				system.db.runNamedQuery("Overtime/Chage Hours/Total Week And Year", 
					{
						"TotalWeekOvertime": TotalWeekOvertime,
						"TotalYearlyOvertime": TotalYearlyOvertime,
						"FirstName": FirstName, 
						"LastName": LastName, 
						"OvertimeWeek": OvertimeWeek
					}
				)

				#Query to update CurrentHours total on MOS_Signup 
				system.db.runNamedQuery("Overtime/Charge Hours/Signup Update",
					{
						"TotalYearlyOvertime": TotalYearlyOvertime,
						"FirstName": FirstName, 
						"LastName": LastName
					}
				)

				#Query to update SatHrs and SunHrs on MOS_Employee
				system.db.runNamedQuery("Overtime/Charge Hours/Hours Update",
					{
						"NewSaturdayHours": NewSaturdayHours,
						"NewSundayHours": NewSundayHours,
						"FirstName": FirstName,
						"LastName": LastName
					}
				)

				#Query to add to log (add in later)

	#Refresh the Mechanics table
	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Charge Hours/Mechanics Pull", {"OvertimeWeek" :OvertimeWeek}))
	NewValues = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (columns['LastName'], columns['FirstName']))
	self.props.data = NewValues

	#Refresh the Electricians table
	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Charge Hours/Electricians Pull", {"OvertimeWeek" :OvertimeWeek}))
	NewValue = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (columns['LastName'], columns['FirstName']))
	self.props.data = NewValue

	#Refresh the Laborers table
	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Charge Hours/Laborers Pull", {"OvertimeWeek" :OvertimeWeek}))
	NewValue = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (columns['LastName'], columns['FirstName']))
	self.props.data = NewValue

	#Refresh the Lube Techs table
	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Charge Hours/Lube Techs Pull", {"OvertimeWeek" :OvertimeWeek}))
	NewValue = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (columns['LastName'], columns['FirstName']))
	self.props.data = NewValue

Are you using dataset format on your table data?

But yes, you should have a custom prop with the original data, bound to the source query with no polling (so it loads once). Unidirectionally bind the table's data prop to the custom prop. This will make it load once after the source loads.

After that, use system.dataset.setValue() in your onCellEdited event to replace the table's data as the user edits it.

Then, in the save button, you will have two datasets to work with.

If you have funky display issues that require a transform to JSON format, use a second custom dataset property to hold your edited values (so that's what onCellEdited would update), and transform from there to your table data prop.

I ended up making a temp table that will clear every time the page is opened and then load in the values from the historical for the current week. The cells are editable and will change the values within the temp table and refresh the table after. I have a save button that when clicked will save those new values to the historical table. MechanicsTable is the temp table and OldMechanicsTable is the historical table. It's not giving me any errors but the script isn't saving anything from the temp table to the historical table. Do you know what I'm doing wrong? Here is my code for my button:

def runAction(self, event):
	#Variables
	Today = system.date.now()
	Time = system.date.format(Today, "MM/dd/YYYY hh:mm:ss a")
	Date = system.date.format(Today, "MM/dd/YYYY")
	OvertimeWeek = self.getSibling("Table Date").props.text
	userID = self.session.props.auth.user.userName
	#Maybe factor in log after I get it working
	#Type = "Edited"
	#LogEntry = userID + " edited " + LastName + ", " + FirstName + " Weekly overtime to " + str(WeeklyOvertime) + " for the date of " + str(Date) + "."
	
	TempTable = system.db.runNamedQuery("Overtime/Charge Hours/Temp Table Pull", {"OvertimeWeek": OvertimeWeek})
	for row in range(TempTable.getRowCount()):
		#Variables
		FirstName = TempTable.getValueAt(row, "FirstName")
		LastName = TempTable.getValueAt(row, "LastName")
		WeeklyOvertime = TempTable.getValueAt(row, "WeeklyOvertime")
		SaturdayOvertime = TempTable.getValueAt(row, "SaturdayOvertime")
		SaturdayRejected = TempTable.getValueAt(row, "SaturdayRejected")
		SundayOvertime = TempTable.getValueAt(row, "SundayOvertime")
		SundayRejected = TempTable.getValueAt(row, "SundayRejected")
		HolidayOvertime = TempTable.getValueAt(row, "HolidayOvertime")
		PreviousYearlyOvertime = TempTable.getValueAt(row, "PreviousYearlyOvertime")
		SatHrs = TempTable.getValueAt(row, "SatHrs")
		SunHrs = TempTable.getValueAt(row, "SunHrs")
		SaturdayHoursTotal = float(SatHrs) + float(WeeklyOvertime) + float(SaturdayOvertime) + float(SaturdayRejected)
		SundayHoursTotal = float(SunHrs) + float(HolidayOvertime) + float(SundayOvertime) + float(SundayRejected)
		TotalWeekOvertime = float(WeeklyOvertime) + float(SaturdayOvertime) + float(SaturdayRejected) + float(SundayOvertime) + float(SundayRejected) + float(HolidayOvertime)
		TotalYearlyOvertime = float(TotalWeekOvertime) + float(PreviousYearlyOvertime)

		#Query to update all overtime values on MOS_HoursChargedHistorical
		system.db.runNamedQuery("Overtime/Charge Hours/Overtime Values Update", 
			{
				"WeeklyOvertime": WeeklyOvertime,
				"SaturdayOvertime": SaturdayOvertime,
				"SaturdayRejected": SaturdayRejected,
				"HolidayOvertime": HolidayOvertime,
				"SundayOvertime": SundayOvertime,
				"SundayRejected": SundayRejected,
				"FirstName": FirstName,
				"LastName": LastName, 
				"OvertimeWeek": OvertimeWeek,
				"TotalWeekOvertime": TotalWeekOvertime,
				"TotalYearlyOvertime": TotalYearlyOvertime,
				"Username": userID,
				"ChargeTimestamp": Time
			}
		)

		#Query to update CurrentHours total on MOS_Signup 
		system.db.runNamedQuery("Overtime/Charge Hours/Signup Update",
			{
				"TotalYearlyOvertime": TotalYearlyOvertime,
				"FirstName": FirstName, 
				"LastName": LastName
			}
		)

		HistoricalTable = system.db.runNamedQuery("Overtime/Charge Hours/Historical Table Pull", {"OvertimeWeek": OvertimeWeek})
		for row in range(HistoricalTable.getRowCount()):
			#Variables
			HistoricalFirstName = HistoricalTable.getValueAt(row, "FirstName")
			HistoricalLastName = HistoricalTable.getValueAt(row, "LastName")
			HistoricalWeeklyOvertime = HistoricalTable.getValueAt(row, "WeeklyOvertime")
			HistoricalSaturdayOvertime = HistoricalTable.getValueAt(row, "SaturdayOvertime")
			HistoricalSaturdayRejected = HistoricalTable.getValueAt(row, "SaturdayRejected")
			HistoricalSundayOvertime = HistoricalTable.getValueAt(row, "SundayOvertime")
			HistoricalSundayRejected = HistoricalTable.getValueAt(row, "SundayRejected")
			HistoricalHolidayOvertime = HistoricalTable.getValueAt(row, "HolidayOvertime")
			HistoricalSaturdayHoursTotal = float(SatHrs) + float(HistoricalWeeklyOvertime) + float(HistoricalSaturdayOvertime) + float(HistoricalSaturdayRejected)
			HistoricalSundayHoursTotal = float(SunHrs) + float(HistoricalHolidayOvertime) + float(HistoricalSundayOvertime) + float(HistoricalSundayRejected)

			#Variables
			SaturdayChange = SaturdayHoursTotal - HistoricalSaturdayHoursTotal
			SundayChange = SundayHoursTotal - HistoricalSundayHoursTotal
			NewSaturdayHours = SatHrs + SaturdayChange
			NewSundayHours = SunHrs + SundayChange

			#Query to update SatHrs and SunHrs on MOS_Employee
			system.db.runNamedQuery("Overtime/Charge Hours/Hours Update",
				{
					"NewSaturdayHours": NewSaturdayHours,
					"NewSundayHours": NewSundayHours,
					"FirstName": FirstName,
					"LastName": LastName
				}
			)

			#Query to add to log (add in later)

Was forgetting my "OvertimeWeek" parameter in the table queries. Working out the last few bugs but this will work!! Thanks for all of your help @pturmel

1 Like

Looks like my MOS_Employee SatHrs and SunHrs values aren't coming out right, it's looping extra since it's in the nested for loop. It's taking forever and giving the wrong values. I'll update if I figure out what I need to change.