# Updating a table based off a data selection

**URL:** <https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672>\
**Category:** Ignition\
**Created:** [August 13, 2020, 1:12pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672 "2020-08-13T13:12:51Z")\
**Posts on this page:** 20\
**Page:** 1

<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:** [August 13, 2020, 1:12pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/1 "2020-08-13T13:12:51Z")

</div>

I am trying to update a table I have based off the date selected in a popup calendar component. The table is supposed to be displaying a t\_stamp and coordinators name when it is compared to the date property of the pop up calendar. But it is not working… This is what I currently have:

event.source.parent.getComponent(‘CoordinatorTable’).data = system.db.runQuery(“Select t\_stamp, Coordinator from SQDCME.dbo.SQDCME where t\_stamp = [Root Container.ProductionDate.date]”)

But it does not seem to be working correctly.

---

<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:** [August 13, 2020, 1:22pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/3 "2020-08-13T13:22:40Z")

</div>

As a note if you use three back quotes ``` before and after your code it will be formated and more easily readable by forum members.

You have just included the string [Root Container.ProductionDate.date] in the query as opposed to actually getting the value for the date.

I am also assuming that ProductionDate is the calendar component, in which case your code should look something like the following:

```auto
event.source.parent.getComponent('CoordinatorTable').data = system.db.runQuery("Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = '" + event.source.parent.getComponent('ProductionDate').date + "'"

```

---

<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:** [August 13, 2020, 1:28pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/4 "2020-08-13T13:28:15Z")

</div>

Thank you. I didnt know about the 3 quotes…

---

<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:** [August 13, 2020, 1:57pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/5 "2020-08-13T13:57:33Z")

</div>

I tried using that code that you mentioned, but it didn work. It actually wouldn’t even accept it and let me close the window.

‘’‘event.source.parent.getComponent(‘CoordinatorTable’).data = system.db.runQuery("Select t\_stamp,Coordinator from SQDCME.dbo.SQDCME where t\_stamp = " + event.source.parent.getComponent(‘ProductionDate’).date + "’" ‘’’

---

<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:** [August 13, 2020, 2:03pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/6 "2020-08-13T14:03:44Z")

</div>

The back quote is on the same key as the tilde, or at least it is in the US.

Are you getting an error message with your code?

Is this Perspective or vision?

---

<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:** [August 13, 2020, 2:07pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/7 "2020-08-13T14:07:27Z")

</div>

It is vision. It not getting an error message per se, it won’t allow the window to close. When I hover over it, it says - no viable alternative at input ‘\n’

---

<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:** [August 13, 2020, 2:11pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/8 "2020-08-13T14:11:07Z")

</div>

Can you post a screen snippet of this?

---

<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:** [August 13, 2020, 2:19pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/9 "2020-08-13T14:19:18Z")

</div>

That would be a syntax error. { Also, when using the triple-backquotes to trigger formatting, put them on their own lines above and below. }

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [August 13, 2020, 2:24pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/10 "2020-08-13T14:24:44Z")

</div>

Also look into using [system.db.runPrepQuery](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepQuery) instead of system.db.runQuery.

---

<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:** [August 13, 2020, 2:50pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/11 "2020-08-13T14:50:35Z")

</div>

I figured out the syntax error but it still would not run. The error message that showed up was this:

```auto
Traceback (most recent call last):

  File "<event:propertyChange>", line 1, in <module>

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = event.source.parent.getComponent('ProductionDate').date, , [null], )

	caused by Exception: Error executing system.db.runPrepQuery(Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = event.source.parent.getComponent('ProductionDate').date, , [null], )
	caused by GatewayException: Remote function reference 'event.source.parent.getComponent' is not allowed, and the column name 'event' could not be found or is ambiguous.
	caused by SQLServerException: Remote function reference 'event.source.parent.getComponent' is not allowed, and the column name 'event' could not be found or is ambiguous.

Ignition v7.9.13 (b2019120915)
Java: Azul Systems, Inc. 9.0.7.4

```

---

<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:** [August 13, 2020, 2:59pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/12 "2020-08-13T14:59:25Z")

</div>

The syntax for runPrepQuery is different from runQuery.

```auto
query = 'Select t_stamp,Coordinator from SQDCME.dbo.SQDCME where t_stamp = ?'
args = [event.source.parent.getComponent('ProductionDate').date]
results = system.db.runPrepQuery(query, args)
event.source.parent.getComponent('CoordinatorTable').data = system.dataset.toDataSet(results)

```

---

<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:** [August 13, 2020, 3:13pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/13 "2020-08-13T15:13:47Z")

</div>

I’m thinking that no matter what I do, I cannot get it to work. I am trying to use the propertychange of the date component but it does not seem to be working…[Dailyproduction.proj](https://forum.inductiveautomation.com/uploads/short-url/nexAFkZflaJ43BbKR0S9BBHgIaF.proj) (657.5 KB)

---

<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:** [August 13, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/14 "2020-08-13T15:38:29Z")

</div>

This is a working script, assuming that the query is correct and will return values, there is no reason this code should not work.

Something to look out for, if the database which contains the table of interest is not the default database of the project then you must supply the database connection in the call to runQuery or runPrepQuery.

```auto
#filter the property change so that code is only executed when the date property changes
if event.propertyName == date:
    #specify the query to run representing parameter values with the '?' character
	query = 'Select t_stamp,Coordinator FROM SQDCME.dbo.SQDCME where t_stamp = ?'
    #use the newValue of the date as the value to substitute into the query
    args = [event.newValue]
    #run the query
	results = system.db.runPrepQuery(query,args)
    #set the table's dataset equal to the results of the query. NOTE: system.db.runPrepQuery
    #returns a pyDataSet and the table.data property is a Dataset type so use
    #system.dataset.toDataSet to convert the results from a pyDataSet to a DataSet type
	event.source.parent.getComponent('CoordinatorTable').data = system.dataset.toDataSet(results)

```

Also note that if this is the only function your wanting to perform in the propertyChange event, probably the easier (some would argue better) way to accomplish this is to use an SQL Query binding on the data property of the table and reference the popup calendar component’s date property there.

---

<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:** [August 13, 2020, 4:41pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/15 "2020-08-13T16:41:17Z")

</div>

So I took your advice and used a SQL Query binding on the data property of the table, but still didnt work. That query is accurate as well. Is it possible that since I am not using the newer version 8.0 that is not working?[Dailyproduction.proj](https://forum.inductiveautomation.com/uploads/short-url/zDyZeEaEfDFBR5DYhX5StVTpAa.proj) (658.0 KB)

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [August 13, 2020, 4:54pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/16 "2020-08-13T16:54:42Z")

</div>

Have you enabled Legacy Database Access in the project properties?

---

<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:** [August 13, 2020, 4:55pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/17 "2020-08-13T16:55:43Z")

</div>

Okay, so I took a look at the project.

You misunderstood what I was saying, the script I provided would have gone in the propertyChange event of the popup calendar component. The binding should look like this:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/a/5a57096cd2772ce59eb70fb3d3a2f8a55d764319.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:** [August 13, 2020, 5:02pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/18 "2020-08-13T17:02:12Z")

</div>

I had originally put it in the propertyChange event of the popup calendar and it still did not work. Also what you have in that image… does not work.

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/c/fc637814c2fbda5296a51093b56c7239a7b3ffe1.png)

---

<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:** [August 13, 2020, 5:25pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/19 "2020-08-13T17:25:59Z")

</div>

I don’t have a connection to your database so it is difficult for me to troubleshoot this particular error.

The script in and of its self should work, it is possible that it isn’t yielding the results that you expect. For instance, we are asking for results where t\_stamp is “Equal” to the selection, depending on how the data is stored in t\_stamp, this could be unreliable. You may need to insure that the “date” property holds a value which will yield results when compared to the t\_stamp column.

In theory there is no syntactic issue that I can see with the SQL, assuming that there really is a table Named SQDCME.

Try placing single quotes around the reference to the date. I threw together a sample to demonstrate, this works:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/c/bc5651442533200ef595c89a04793a52b5245861.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3d5bdb22d5637d707ee9259939c39302b7fa15ea.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:** [August 13, 2020, 6:04pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/20 "2020-08-13T18:04:26Z")

</div>

Yeah the submit button takes the information in the form and dumps it into a table called SQDCME. What I am trying to do is show the coordinators that have already entered there information. If I do it off the current date… it works… but choosing that down arrow and selecting a different date is where we have the issue. Here is my actual sql table that is being polled.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1c48e0cc47da0dc1af68a1032a6b71f7b7c706db.png)

The t\_stamp column shows the date and a time of 00:00:00.000 for each record. Also the interesting thing is my original sql query worked with no issues.

```auto
Select t_stamp, Coordinator
from SQDCME.dbo.SQDCME
where CAST(t_stamp AS date) AND CAST(GETDATE() AS Date)

```

---

<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:** [August 13, 2020, 6:05pm UTC](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672/21 "2020-08-13T18:05:24Z")

</div>

I did try the quotes around the Root.ProductionDate.date, but it just cleared out the table no matter what date I selected.

[Next page](https://forum.inductiveautomation.com/t/updating-a-table-based-off-a-data-selection/37672.md?page=2)