Comparing Two Datasets Values, and using those to determine difference

Hi, I need to compare two datasets in a power table. in this table the user inputs parts and qty for an order. what i want to do it is the following.

This Dataset are for the Power Table

SelDS=Query from DB were ordernumber=value (polling Off)

EmptyDS= a five column empty dataset

Table.data = (Power Table dataset)

if the order is being edited(Meaning the table already has values in it SelDS saved.

Table in Ignition (SelDS)

image

let’s say i change the value of the Case to Ship column in row(0) from 75 to 90

Then i save this order again, and i want to compare. the SelDS (old Data from the db when the order was open for edit) with Table.data( new data with the changes) and get the following results.
I know the following code is not correct, it just to show what i’m looking for and expected results

Compare palletnumber and Casetoship all rows between both DS.

  if table.casetoship!=SelDS.casetoship:
      if table.casetoship>SelDS.casetoship:
           do something    #update the table with new value where palletnumber=palletnumber 
  if table.palletnumber  not in SelDS.palletnumber:  # new value only exist in new data table.data
          do something  #insert into a table
  if Sel.DS.palletnumber not in table.palletnumber   # part was remove from order, need to delete 
                                                           and update in db
from com.inductiveautomation.ignition.common.util import DatasetTestUtils
DatasetTestUtils.equals(<dataset>, <dataset>)

Probably the easiest solution.

2 Likes

Thanks, but that actually provides a True or False comparison, for the DSs involve, I’m trying to compare the values inside of them, not just the full dataset against each other.
What I’m trying to do is to edit an order in the db. to do this i want to compare the values of the two datasets, to determine if changes where made to the order like, a new pallet was entered, or a pallet was remove from the order, or [Case to Ship] changed, with this comparisons, I then do update on db using palletnumber to find the record, and updated in their table

something like this for example

compare all palletnumbers in both datasets and check the following
if pallet was added(Palletnumber not in old ds but exist in new ds (table.data)), or if it was deleted, or if palletnumber exists in both ds and [Case to Ship] change value, if palletnumber exist in both DS and [Case to Ship] value not change

Then you’ll just have to iterate through all the rows in the columns you care about, in an application specific manner. Without knowing exactly what you need, it’s not really something we can help with. I would recommend starting with system.dataset.toPyDataset() to make your datasets more like Python objects, then just iterating over them.

Knowing that two datasets are equal or not equal is just the first step.
If they are not equal and you want them to become equal then there needs to be a decision for each row that is different, either INSERT, UPDATE or DELETE.

Assuming that you can’t use SQL Minus or other comparisons before getting to Ignition then you’re stuck with row-by-row comparison.

I’m sure there are more efficient methods but here’s an example of INSERTs, DELETEs and UPDATEs I threw together for a UI dataset (e.g. Power Table) compared to a dataset from an SQL SELECT.
In this example the user edits (UI*) are the controlling/authoritative data and the database is updated based on the user edits.

Since I just threw this together please don’t be overly critical when you find typos and logic mistakes. You will need to tweak the INSERT, UPDATE and DELETE syntax for your data and RDBMS.
Since I’ve never done a runPrepUpdate for an UPDATE statement, I switched to just runUpdate at the end.

database = system.tag.read( 'Config/DatabaseConnection' ).value
pkColumn = 'yourPKcolumnName'

# make sure both datasets are sorted by Primary Key (or candidate key) Values
uiDataset = system.dataset.sort( uiDataset, pkColumn, True )
dbDataset = system.dataset.sort( uiDataset, pkColumn, True )

# the user edits are the controlling/authoritative data
rowsToInsert = []
rowsToDelete = []

# handle INSERTS and DELETES before looking for UPDATES
if uiDataset.rowCount != dbDataset.rowCount:
	uiRow = 0
	dbRow = 0
	currentRow = 0
	while currentRow < uiDataset.rowCount:
		uiPk = uiDataset.getValueAt( uiRow, pkColumn )
		dbPk = dbDataset.getValueAt( dbRow, pkColumn )
		while uiPk != dbPk:
			if uiPk > dbPk:
				# uiPk record was INSERTED by the user
				print 'row %n with Primay Key "%s" was INSERTED by the user' %(currentRow,uiPk)
				# store the INSERTed row. Note that INSERTs will be done from the uiDataset
				# advance to the next row in the uiDataset for the next compare
				uiRow = uiRow + 1
				# hold the dbDataset comparison back one row - until the primary keys are back in step
				rowsToInsert.append( uiRow )
			elif uiPk < dbPk:
				# uiPk record was DELETED by the user
				# advance to the next row in the dbDataset for the next compare
				print 'row %n with Primay Key "%s" was DELETED by the user' %(currentRow,uiPk)
				# store the DELETEd row. Note that Deletes will be done from the dbDataset
				dbRow = dbRow + 1
				# hold the dbDataset comparison back one row - until the primary keys are back in step
				rowsToDelete.append( dbRow )
			elif uiPk == dbPk:
				# key values are the same, advance to the next rows in both datsets
				uiRow = uiRow + 1
				dbRow = dbRow + 1

			uiPk = uiDataset.getValueAt( uiRow, pkColumn )
			dbPk = dbDataset.getValueAt( dbRow, pkColumn )

		# THIS LINE IS CRITICAL! Without it, you will have an endless loop
		currentRow = currentRow + 1
		
		# We've found the INSERTs and DELETEs, update the database with the user edits
		for row in rowsToInsert:
			# build the INSERT statment
			sql = 'INSERT INTO <table> <columnNames> VALUES <?,?,?>'
			rowData = []
			for column in range( uiDataset.columnCount ):
				rowData.append( uiDataset.getValueAt( row, column ) )
			# INSERT the data - this should be in a try/except block for error handling
			system.db.runPrepUpdate( sql, data, database )

		for row in rowsToDelete:
			# build the DELETE statment
			pkValue = dbDataset.getValueAt( row, pkColumn )
			sql = 'DELETE <table> <columnNames> WHERE %s = ?' %(pkColumn, pkValue)
			# DELETE the data - this should be in a try/except block for error handling
			system.db.runPrepUpdate( sql, [pkValue], database )

# now that the INSERTs and DELETEs have been handled, look for UPDATEs
# refresh the dbDataset using the same method it was first retrieved...
# sort the dbDataset by the primary key if it wasn't sorted via SQL								
if uiDataset.rowCount == dbDataset.rowCount:
	# use column names instead of ordinal numbers for updates
	columnNames = system.dataset.getColumnHeaders( uiDataset )
	# remove the primary key from the columns to be updated
	columnNames.remove( pkColumn )
	for row in range( uiDataset.rowCount ):
		# UPDATE if the PKs are the same
		pkValue = uiDataset.getValueAt( row, pkColumn )
		updateCounter = 0
		if  dbDataset.getValueAt( row, pkColumn ) == pkValue:
			updateCounter = 0
			sql = 'UPDATE <tablename> SET '
			for column in columnNames:
				# UPDATE if the data is different
				if uiDataset.getValueAt( row, column ) == dbDataset.getValueAt( row, column ):
					sql = sql + column + '=' + uiDataset.getValueAt( row, column ) + ','
					updateCounter = updateCounter + 1
			# drop the trailing comma
			if sql[-1] == ',':
				sql = sql[:-1]
			if updateCounter > 0:
				# The WHERE caluse is CRITICAL!!!
				sql = sql + " WHERE %s='%s'" %(pkColumn,pkValue)
				print sql
				system.db.runQuery( sql, database )

1 Like

Where does table.data get its data from? On the surface, it seems it would be easier to write your changes to the db then refresh the view.

Yes, you are right, I normally update the database as the user changes rows so I don’t actually use this script - I just tossed it out there to give @juang a possible approach to making two datasets equal. One dataset has to be the authority/driver, of the changes.

For this example, table.data is a Power Table where the user has been Adding, Deleting and Updating records. All of the updates would happen when they click a “Save” button.

I’m testing this code, but can’t get it to work.

from com.inductiveautomation.ignition.common.util import DatasetTestUtils
table=event.source.parent.getComponent('Power Table')
edit= table.editSelectDS
data=table.data

print DatasetTestUtils.equals(data,edit)

i get a False result. but both DS are exactly the same, the are actually bound to the same property for testing. but result it’s still false. any thoughts?

You can do a visual check of the data by clicking the tableData icon next to editSelectDS in the Custom Properties section and the tableData icon next to “Data” in the Data section.

Not knowing how editSelectDS is defined or initialized, I did a simple test to prove the function its self is good.

The following gives me a True, followed by a False.

from com.inductiveautomation.ignition.common.util import DatasetTestUtils
table = event.source.parent.getComponent(‘Power Table’)
edit = table.edits
data = table.data
data2 = table.data

print DatasetTestUtils.equals(data,data2) # this is true
print DatasetTestUtils.equals(data,edit) # this is false

I already did that same comparison yesterday. I got the same result you did. Also already checked the the data in the view the data in. Both and it’s exactly the same. As I said in my other post before this one o actually bound the two DS to the same property so they are 100% the same . Same values, same data types and everything. But it get false. I believe the the comparison is also comparing the name which in this case in is data and other one is edit. Because that is the only thing it’s different. But I can’t compare it another way.

DatasetTestUtils compares column names, column types, row count, and actual data values in that order.

If you strictly want to compare datapoints, and don’t want to write the iteration yourself, you could cast each dataset you have into a com.inductiveautomation.ignition.common.BasicDataset, then compare them using java.util.Arrays.deepEquals() - something like this:

from java.util import Arrays 
from com.inductiveautomation.ignition.common import BasicDataset

def equals(ds1, ds2):
	if not isinstance(ds1, BasicDataset):
		ds1 = BasicDataset(ds1)

	if not isinstance(ds2, BasicDataset):
		ds2 = BasicDataset(ds2)

	return Arrays.deepEquals(ds1.getData(), ds2.getData())

The cast to BasicDataset() is necessary because BasicDataset has a getData() method that returns a 2d array of all the values in the dataset - something that is not part of the Dataset interface, and therefore will not always be available on implementing classes.

Yes I knew this so that’s why I named the data inside and Colums inside exactly the same but I get a false result. I have not tried comparing the data property of two different tables with the same data to test my theory that it’s giving me false just because the dataset property being compared its named differently.i will test and let you know if that if that works for True. If it does then I’ll just create and invisible power table that will.have the old data.it is not ideal but if it does trick then it’s fine .

nope it does not work either, here is a photo of the my designer. i tried with two brand new Power Table. with no data on it.

@PGriffith I’m Not really sure how to do this. but to make this simple right now all i need is to know if the data was edited, nothing more than that. if a pallet was removed, or if it was changed. this is because the user will be opening the invoice, so if they edit, and then try to close the window, i need to let them know if they want to save the changes or not.

And @martinel3746 i can’t just modify the database while they are editing, because if they make a mistake to go back it’s not that easy and will be writting unnessary to the DB to many times. it just does not make sense to edit the DB until they want to save the changes they made. I figure out a way to get if they change any cases from the data, by just adding a column in the Power Table.data with the name oldcasesship. so all i do is just compare whatever the newcases ship is against the old, and i update the db with the difference. they i just do the rest on a the db. but i still need to be able to know if the new data was edited, this is not only for my powertable, but for the whole invoice form. i have and InvoiceDS, which has all the data pulled from the db like(invoiceid, customerid, shipday, appointmenttime, and much more info), but if the compare ds does not work correctly then how will i know if it was edited or not.

I know I can just add and edit button which I did, the whole idea is to make this work automatic without the user having to press and edit. if the Invoice was edit, the program needs to know by itself. the reason why i need this to work correctly it’s obvious offcourse, but i also want to do much more with that, but if i can’t compare apples to apples. this is pretty much useless for me. I want to add this code into add this code into the InternalFrameClosing, to prevent a close if the invoice was edit. i need this for many reasons , simplest one, would be user forgerts they are doing a change, and tries to close the program. this will prevent the client from closing. if there where editing. just like word, note, excel, and every single program in the world where you can edit anything. so it is very important to get this to work.

Could be done with sets. Give me a moment. Rome has been burning.

Try this one, modify the script to fit your needs:

Compare Datasets_2019-04-15_1605.proj (11.5 KB)

editTable = event.source.parent.getComponent('Edit Table')
masterTable = event.source.parent.getComponent('Master Table')

editData = editTable.data
masterData = masterTable.data

editSet =set(tuple(row) for row in system.dataset.toPyDataSet(editData))
masterSet =set(tuple(row) for row in system.dataset.toPyDataSet(masterData))

print editSet
print masterSet
print editSet - masterSet

Thank you @JordanCClark , but how does that help in this case, as that in not coparing anything. can you explain, when you can no rush. but when you have time can you please explain how am i going to use that. let’s say i want to set a custom property name InvoiceWasChanged(Boolean). set this property to true if the data was change at all.

Sure!

editTable = event.source.parent.getComponent('Edit Table')
masterTable = event.source.parent.getComponent('Master Table')

editData = editTable.data
masterData = masterTable.data

editSet =set(tuple(row) for row in system.dataset.toPyDataSet(editData))
masterSet =set(tuple(row) for row in system.dataset.toPyDataSet(masterData))

print editSet
print masterSet
print editSet - masterSet

if len(editSet - masterSet) > 0:
  print 'A change has occurred!'

Or, in your case:

table.InvoiceWasChanged = 1

Ok, time for a breather! :slight_smile:

Subtracting on set from another removes whatever is in the second set from the first set. what remains is whatever changes were made to the edit table. If there is nothing in the subtracted set, there was no change. Otherwise you have a new set with each row that has a change in it.

Play with the sample window I supplied. change whatever you want in the edit table, and see the results.

thank you but it does not work completely, because i can add or remove rows. and i does not recognize that. it only work with one type situation, but not all. for example i just deleted a row and it does not do anything in that situation… it is better than nothing so i will see what i can do to make it work as i need to. thank you very much for all the time spent on my question.