# How to filter a dataset to a new dataset

**URL:** <https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281>\
**Category:** Ignition\
**Tags:** ignition79, database\
**Created:** [July 28, 2021, 6:15am UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281 "2021-07-28T06:15:25Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![penji](https://avatars.discourse-cdn.com/v4/letter/p/c5a1d2/32.png) [@penji](https://forum.inductiveautomation.com/u/penji)\
**Post date:** [July 28, 2021, 6:15am UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/1 "2021-07-28T06:15:25Z")

</div>

Hello 😃  
My Ignition Vision is 7.9.16.  
I want to query a dataset (This dataset just have ID and another string type data) to a new dataset (same structure as old one). The rule of filter is ’ while the string data (old dataset) exists in a Database, it should be displayed in new dataset '.  
What kind of sulotion can I use and How to use? Thanks in advance! 😃

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 28, 2021, 12:35pm UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/2 "2021-07-28T12:35:54Z")

</div>

Have to say I’m a bit confused by your question. It isn’t exactly clear what you’re trying to do.

What exactly have you tried and not gotten the results you’re looking for? What is the difference between the old dataset and the new dataset?

---

<div class="post-metadata">

**Author:** ![mori.luca](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mori.luca/32/13424_2.png) [@mori.luca](https://forum.inductiveautomation.com/u/mori.luca)\
**Post date:** [July 28, 2021, 12:55pm UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/3 "2021-07-28T12:55:42Z")

</div>

```auto
def databaseCheck(itemToSearch):
	
	databaseFakeData = ["c","d","e"]
	return itemToSearch in databaseFakeData
	

header = ["key","value"]
body = [[1, 'a'],[2, 'b'],[3, 'c'],[4, 'd'],[5, 'e']]

oldDataset = system.dataset.toDataSet(header, body)
oldDataset = system.dataset.toPyDataSet(oldDataset)

newBody = []

for item in oldDataset :
	
	if databaseCheck(item["value"]):
		newBody.append([item['key'], item['value']])

newDataset = system.dataset.toDataSet(header, newBody)

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [July 28, 2021, 4:56pm UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/4 "2021-07-28T16:56:48Z")

</div>

@pturmel’s (free) Simulation Aids module adds a `view` expression function that allows you to query a dataset using SQL-like syntax:  
[https://www.automation-pros.com/simaids/doc/expression.view.html](https://www.automation-pros.com/simaids/doc/expression.view.html)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 28, 2021, 5:12pm UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/5 "2021-07-28T17:12:01Z")

</div>

I didn’t pipe up immediately with a suggestion to use my `view()` function because you need a separate query against the DB to lookup up the strings’ existence. A query with a dynamic IN clause of strings. Non-trivial.

---

<div class="post-metadata">

**Author:** ![penji](https://avatars.discourse-cdn.com/v4/letter/p/c5a1d2/32.png) [@penji](https://forum.inductiveautomation.com/u/penji)\
**Post date:** [August 5, 2021, 5:24am UTC](https://forum.inductiveautomation.com/t/how-to-filter-a-dataset-to-a-new-dataset/49281/6 "2021-08-05T05:24:17Z")

</div>

Thanks all replies, I add a button and script it:

#Manual inserted table in HMI  
InsertedTable = event.source.parent.getComponent(“DatasetEditor”).data  
#get the needed col  
pData = system.dataset.filterColumns(InsertedTable,[1])  
IDlist = system.dataset.toPyDataSet(pData)  
containerList = system.db.runQuery(“SELECT barcode FROM location”,“DB”)

dblist=  
for row in containerList:  
for value in row:  
dblist.append(value)

headers = [“ID”]  
data =   
for row in IDlist:  
for value in row:  
print(value)  
if (value in dblist):  
data.append([value])  
event.source.parent.getComponent(‘Power Table 1’).data = system.dataset.toDataSet(headers, data)

And then this was solved.