Filling a table from 2 other tables

Ok… so now I got that working… can I just click on the row of information from my second table(employee table) and drag that info to the position row i want?

Yes. Both tables need to have the “Row Dragging Enabled” turned on.

The onRowsDropped extension function in the position table handles what happens to the rows you drag in.

The configureCell script helps determine what the color of the cells should look like.

1 Like

Take note that these are Power Tables, and not standard tables.

but with the onRowsDropped extension function, I should only need to enable it right? I don’t need to change the code to reflect the table names right? At least that is what I thought I read.

You’ll need code in there to tell it what to do with the data you’re dragging in. This should get you close.

	employeeId	= rowData.getValueAt(0,'EmployeeID')
	firstName 	= rowData.getValueAt(0,'FirstName')
	lastName 	= rowData.getValueAt(0,'LastName')
	shift 		= rowData.getValueAt(0,'Shift')
	department  = rowData.getValueAt(0,'Department') 

	# Check drop location
	if dropIndexLocation > 0:
		# Get table data
		data = self.data
		# Get column names. This will help us set values by column name later
		headers = list(data.getColumnNames())
		# Set values
		data.setValueAt(dropIndexLocation - 1, headers.index('EmployeeID'), employeeId)
		data.setValueAt(dropIndexLocation - 1, headers.index('FirstName'), firstName)
		data.setValueAt(dropIndexLocation - 1, headers.index('LastName'), lastName)
		data.setValueAt(dropIndexLocation - 1, headers.index('Shift'), shift)
		data.setValueAt(dropIndexLocation - 1, headers.index('Department'), department)
		# Write back to the table
		self.data = data
1 Like

OK… so does this need to be on the table I am pulling info from or the table that is getting the info? I realize that both tables have to have the Row Dragging Enabled.

This needs to be on the table you are dragging to.

1 Like

It works… so last part…Is it possible to drag over the employees I need for the listed positions and then have a submit button that drops the information into a sql table so we can gauge labor cost?

Here’s a start. I commented out the actual db operation, so you can see if the query looks okay before writing.

data = event.source.parent.getComponent('PositionTable').data
columnList = list(data.getColumnNames())
pyData = system.dataset.toPyDataSet(data)
tStamp = system.date.now()

# Create column string for the query
columnString = '(t_stamp,' + ','.join(columnList) + ')'
# Create question marks for the prepQuery insert, adding one to each set of values for the timestamp
questionMarkString = ','.join(['(' + ','.join('?' * (len(columnList)+1)) + ')'] * len(pyData))

query = "ISNERT INTO myTable " + columnString + ' VALUES ' + questionMarkString

print query

# Make a flat list out of the pyData
values = []
for row in pyData:
	# Add tStamp and row data for each row
	values += [tStamp]+list(row)
print values

#system.db.runPrepUpdate(query, values)

This is the scripting I would use in the submit button right?

Yep. :slight_smile:

I am trying to understand where you did the scripting to make duplicate entries show a different color…

Look in the configureCell function.

I found it, copied it, but it doesn’t change any colors for me, no matter what I use.

I was trying on the employee ID…

config = {'background': 'white'}
if colName == 'EmployeeID':
	data = self.data
	compareList = data.getColumnAsList(colIndex)
	if compareList.count(value) > 1:
		config = {'background': 'yellow'}
return config

Is it indented underneath the function name?

yes

As a quick sanity check try this one. This one ignores what the column name is.

	config = {'background': 'white'}
	data = self.data
	compareList = data.getColumnAsList(colIndex)
	if compareList.count(value) > 1:
		config = {'background': 'yellow'}
	return config