# Write data from database table to UDT tags

**URL:** <https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633>\
**Category:** General Discussion\
**Tags:** perspective\
**Created:** [September 26, 2023, 10:23am UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633 "2023-09-26T10:23:19Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 10:23am UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/1 "2023-09-26T10:23:19Z")

</div>

Hi Team,  
I have a data in database table. I am fetching data from database and displaying in table on clicking on button. Database table column names and UDT tags names are same.

can you please guide how to write this database table data to UDT. I am working in Perspective mode.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/0/205eca0076d57ee37f26a13a4fd1b8ccfb7d70ae.png)

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [September 26, 2023, 12:07pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/2 "2023-09-26T12:07:51Z")

</div>

> [@kavya\_shree](#):
>
> this database table data to

try this approach:  
When ever you click on load button, write the query result dataset to a memory dataset [Name: Mem Dataset] tag,  
and make other tags which are same as columns as expression tag that refers to their column name like,

```auto
{[.]Mem Dataset}[0, "MACHINE_ID"]

```

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 12:32pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/3 "2023-09-26T12:32:00Z")

</div>

Thank you but sorry I didn't understood . I am not experienced. can you please guide in detail.

```auto
def runAction(self, event):

	sapordernumber = self.getSibling("TextArea").props.text
	query = "select top 1 * from KBETable where SAP_OPRDER_NUMBER = '"+sapordernumber+"'"
	
	# here's the command to run the query
	returnedData = system.db.runQuery(query)
		
		
	self.getSibling("Table").props.data = returnedData
	system.perspective.print(returnedData)
	
	# Convert to a PyDataset
	pyData = system.dataset.toPyDataSet(returnedData)
		 
		# The for loop pulls out the whole row, so typically the variable row is used.
	for row in pyData:
		    # Now that we have a single row, we can loop through the columns just like a list.
		for value in row:
		    system.perspective.print(value)
			    
	system.tag.writeBlocking("[default]Raptor_KBE_WebServices/KBE_UDT_Tags", value)
	

```

this is how I am trying

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [September 26, 2023, 12:33pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/4 "2023-09-26T12:33:21Z")

</div>

> [@kavya\_shree](#):
>
> `returnedData `

modify as  
system.tag.writeBlocking("[default]Raptor\_KBE\_WebServices/MemoryDatasetTag", [returnedData])

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/5 "2023-09-26T12:50:46Z")

</div>

I tried this still not working

---

<div class="post-metadata">

**Author:** ![CalebM](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@CalebM](https://forum.inductiveautomation.com/u/CalebM)\
**Post date:** [September 26, 2023, 1:03pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/6 "2023-09-26T13:03:14Z")

</div>

[system.tag.writeBlocking()](https://docs.inductiveautomation.com/display/DOC81/system.tag.writeBlocking?gclid=CjwKCAjwgsqoBhBNEiwAwe5w0wg4WcXPtElxSMVaZzZq6cshcSYCipTKQyjh7mnHN87QBTkpAvMoahoCGa8QAvD_BwE) requires a list of tagPaths and values. It would have to be something like:

```auto
system.tag.writeBlocking(["[default]Raptor_KBE_WebServices/MemoryDatasetTag"], [returnedData])

```

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 1:23pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/7 "2023-09-26T13:23:01Z")

</div>

```auto
MemoryDatasetTag"

```

this mean, do I need to create other tag then what data should I put. sorry

---

<div class="post-metadata">

**Author:** ![CalebM](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@CalebM](https://forum.inductiveautomation.com/u/CalebM)\
**Post date:** [September 26, 2023, 1:39pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/8 "2023-09-26T13:39:52Z")

</div>

You will have to write to each of the tags within the UDT. You can do it in one call by create a list of tagPaths and values. In the example below I am assuming that you are successfully getting returned data. We create a list of tag paths and a list of values and then write those.

```auto
def runAction(self, event):

	sapordernumber = self.getSibling("TextArea").props.text
	query = "select top 1 * from KBETable where SAP_OPRDER_NUMBER = '"+sapordernumber+"'"
	
	# here's the command to run the query
	returnedData = system.db.runQuery(query)
		
		
	self.getSibling("Table").props.data = returnedData
	system.perspective.print(returnedData)
	
	# Convert to a PyDataset
	pyData = system.dataset.toPyDataSet(returnedData)
		 
		# The for loop pulls out the whole row, so typically the variable row is used.
	for row in pyData:
		    # Now that we have a single row, we can loop through the columns just like a list.
		for value in row:
		    system.perspective.print(value)
			    

	tagPaths = [
	"[default]Raptor_KBE_WebServices/SAP_OPRDER_NUMBER"
	]
	
	values = [
	returnedData[0]["SAP_OPRDER_NUMBER"]
	]

	system.tag.writeBlocking(tagPaths, values)

```

What exactly are you trying to accomplish here? There may be a better way to do it. What is the purpose of having to hit the button to push data to the UDT?

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 1:59pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/9 "2023-09-26T13:59:42Z")

</div>

we are trying to fetch data from DB table and write in UDT tags.  
DB table contains live Machine data. same live data we want to send to HMI screen.

first step we are trying this.  
In our organization we have server called KBE(Knowledge base Engineering) which contains SAP machine data.  
From KBE file we will send to DB-UDT-HMI.

---

<div class="post-metadata">

**Author:** ![CalebM](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@CalebM](https://forum.inductiveautomation.com/u/CalebM)\
**Post date:** [September 26, 2023, 2:03pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/10 "2023-09-26T14:03:20Z")

</div>

I'm assuming you have multiple machines with different MACHINE\_ID's. When will the update of the UDT be triggered?It seems like you're manually entering an order number and then using that to update the UDT. Will the data in the table change for a given order number once the UDT has been updated?

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 2:06pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/11 "2023-09-26T14:06:43Z")

</div>

yes, I am entering SAP order number as filter, yes data will change/update in DB.  
i need the method to write one row of data to UDT

---

<div class="post-metadata">

**Author:** ![CalebM](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@CalebM](https://forum.inductiveautomation.com/u/CalebM)\
**Post date:** [September 26, 2023, 2:11pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/12 "2023-09-26T14:11:20Z")

</div>

Makes sense. I would recommend using a [query tag](https://docs.inductiveautomation.com/display/DOC81/Types+of+Tags). Instead of updating all the data to the UDT using `system.tag.writeBlocking()`, just update the SAP\_OPRDER\_NUMBER with the user entry. Then create a query tag that gets the row data for that order number. Finally turn all the existing tags into expression tags and have each on grab its value from the query tag.

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [September 26, 2023, 2:26pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/13 "2023-09-26T14:26:44Z")

</div>

thanks . I will try.  
its an urgent requirement I need to try since its new for me

---

<div class="post-metadata">

**Author:** ![CalebM](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@CalebM](https://forum.inductiveautomation.com/u/CalebM)\
**Post date:** [September 26, 2023, 2:37pm UTC](https://forum.inductiveautomation.com/t/write-data-from-database-table-to-udt-tags/79633/14 "2023-09-26T14:37:59Z")

</div>

You can use tag bindings in the query tag query. Something like  
`select top 1 * from KBETable where SAP_OPRDER_NUMBER = '{[.]SAP_OPRDER_NUMBER}'`