# Filling a table from 2 other tables

**URL:** <https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872>\
**Category:** General Discussion\
**Created:** [July 20, 2020, 5:37pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872 "2020-07-20T17:37:19Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 21, 2020, 5:49pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/21 "2020-07-21T17:49:23Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 21, 2020, 5:53pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/22 "2020-07-21T17:53:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 21, 2020, 5:55pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/23 "2020-07-21T17:55:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 21, 2020, 5:57pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/24 "2020-07-21T17:57:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 21, 2020, 6:17pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/25 "2020-07-21T18:17:12Z")

</div>

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

```python
	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

```

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 21, 2020, 6:20pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/26 "2020-07-21T18:20:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 21, 2020, 6:21pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/27 "2020-07-21T18:21:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 21, 2020, 6:25pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/28 "2020-07-21T18:25:34Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 21, 2020, 7:28pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/29 "2020-07-21T19:28:29Z")

</div>

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

```python
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)
```

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 22, 2020, 11:25am UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/30 "2020-07-22T11:25:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 22, 2020, 11:28am UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/31 "2020-07-22T11:28:38Z")

</div>

Yep. 🙂

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 11:58am UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/32 "2020-07-24T11:58:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 24, 2020, 12:01pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/33 "2020-07-24T12:01:10Z")

</div>

Look in the configureCell function.

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 12:08pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/34 "2020-07-24T12:08:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 12:09pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/35 "2020-07-24T12:09:12Z")

</div>

I was trying on the employee ID…

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 12:09pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/36 "2020-07-24T12:09:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 24, 2020, 12:14pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/37 "2020-07-24T12:14:32Z")

</div>

Is it indented underneath the function name?

 ![2020-07-24_8-13-31](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/c/9ce0988003bf5b2cb26a222a4bc7d7a17ef26aca.png)

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 12:16pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/38 "2020-07-24T12:16:00Z")

</div>

yes

---

<div class="post-metadata">

**Author:** ![toddayers](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@toddayers](https://forum.inductiveautomation.com/u/toddayers)\
**Post date:** [July 24, 2020, 12:16pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/39 "2020-07-24T12:16:48Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/8/48a0516332eae4af86a12e2b24d3f7bf17e88a85.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 24, 2020, 12:27pm UTC](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872/40 "2020-07-24T12:27:07Z")

</div>

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

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

```

[Previous page](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872.md?page=1)

[Next page](https://forum.inductiveautomation.com/t/filling-a-table-from-2-other-tables/36872.md?page=3)
