# Issue in using Insert Named Query on a Populated Table

**URL:** <https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117>\
**Category:** Ignition\
**Tags:** database, ignition81, scripting, perspective\
**Created:** [August 24, 2024, 5:50pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117 "2024-08-24T17:50:30Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 24, 2024, 5:50pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/1 "2024-08-24T17:50:31Z")

</div>

I have a popup called `AddBrassTag` that contains a table, `Tbl_Insert_New_BrassTag`. When I enter data into this table, commit the edit (Edit Cell Commit) and press `Confirm`, the data is passed to another table called `Table` on a different popup, `ConfirmSave`.

On the `ConfirmSave` popup, there's a "Save" button (`BtnSave`). When I press this button, I want to save the data from the `Table` on the `ConfirmSave` popup into the database using an `Insert Named Query`.

Here's the script I'm using to handle this:

```python
def runAction(self, event):
    # Retrieve the table data
    table_data = self.getSibling("Table").props.data
    brassTag_Ins = []

    # Iterate over the table data and populate the BrassTag_Ins array
    for row in table_data:
        if row["BrassTag"]["value"]:
            brassTag_Ins.append({
                "BrassTag": row["BrassTag"]["value"],
                "BrassTag_Desc": row["BrassTag_Desc"]["value"]
            })

    # Now, assign the populated brassTag_Ins to the custom property
    self.getSibling("Table").custom.BrassTag_Ins = brassTag_Ins

    # Initialize tbl with the newly populated brassTag_Ins
    tbl = self.getSibling("Table").custom.BrassTag_Ins
    lastBrassTag = tbl[len(tbl) - 2]["BrassTag"]

    # Run the Named Query to save each row in tbl
    for row in tbl:
        if row["BrassTag"]:
            system.db.runNamedQuery("Query_BrassTag_Insert", 
                                    {"BrassTag": row['BrassTag'], 
                                     "BrassTag_Desc": row['BrassTag_Desc'], 
                                     "user": self.session.props.auth.user.userName})

    # Clear the table and BrassTag_Ins array after saving
    self.getSibling("Table").custom.BrassTag_Ins = []
    self.getSibling("Table").props.data = [{
                                              "BrassTag": {
                                                "editable": True,
                                                "style": {"backgroundColor": "white"},
                                                "value": ""
                                              },
                                              "BrassTag_Desc": {
                                                "editable": True,
                                                "style": {"backgroundColor": "white"},
                                                "value": ""
                                              }
                                            }]

```

**Issue:**

Despite using the above script, no data is being saved into the database.

I've attempted to store the table data in a custom array `BrassTag_Ins` and then retrieve it for saving, but this approach also isn't working.

**Request:**

Could anyone guide me on where I might be making a mistake or what I might be missing? I appreciate any advice or insights you can offer.

Thank you for your time!

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 24, 2024, 7:07pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/2 "2024-08-24T19:07:30Z")

</div>

The approach is not "ignitionesque".

Instead of scripting the whole thing, you should rely on bindings a little more.  
For example, bind the `BrassTag_Ins` property to the table data, and use a transform to process that data.  
Then your button can reference that custom property and do the insert.  
I'm also not sure why you're clearing the table afterward, if it's just a confirm popup. Just close it.

BUT, I think there's a whole other level of things that should change.  
Don't send your data to the confirm popup, make the confirm popup send a message back to the calling view to tell it that whatever was asked has been confirmed.  
Catch that message from the `AddBrassTag` popup, and do your insert there.

That way, you have a confirm popup that you can reuse everywhere you need confirmation, instead of creating new ones all over the place, and the logic is handled in the view that has the data.

PS: you don't need to send private messages, we'll see the question and answer if we can.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 24, 2024, 7:11pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/3 "2024-08-24T19:11:29Z")

</div>

How can I use the Transform -- of "Table"? -- in this context. Is populating Table is not enough to get the data?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 24, 2024, 7:19pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/4 "2024-08-24T19:19:13Z")

</div>

Frankly, don't bother. Use the approach I suggested, with messages.

Here's my own confirm popup view:

> **view's json**
>
> ```json
> {
> "custom": {},
> "params": {
> "handler": "handler",
> "message": "message",
> "payload": null,
> "popup_id": "popid",
> "send_cancel": true,
> "source_id": null
> },
> "propConfig": {
> "params.handler": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.message": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.payload": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.popup_id": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.send_cancel": {
> "paramDirection": "input",
> "persistent": true
> },
> "params.source_id": {
> "paramDirection": "input",
> "persistent": true
> }
> },
> "props": {
> "defaultSize": {
> "height": 240,
> "width": 380
> }
> },
> "root": {
> "children": [
> {
> "meta": {
> "name": "Label"
> },
> "position": {
> "grow": 1
> },
> "propConfig": {
> "props.text": {
> "binding": {
> "config": {
> "path": "view.params.message"
> },
> "type": "property"
> }
> }
> },
> "props": {
> "style": {
> "overflow": "auto",
> "overflowWrap": "break-word",
> "textAlign": "center"
> }
> },
> "type": "ia.display.label"
> },
> {
> "children": [
> {
> "events": {
> "component": {
> "onActionPerformed": {
> "config": {
> "script": "\t\n\tpayload \u003d {\n\t\t\u0027confirm\u0027: True,\n\t\t\u0027source_id\u0027: self.view.params.source_id\n\t}\n\tif self.view.params.payload:\n\t\tpayload.update(self.view.params.payload)\n\tsystem.perspective.sendMessage(\n\t\tmessageType\t\u003d self.view.params.handler,\n\t\tpayload\t\t\u003d payload,\n\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
> },
> "scope": "G",
> "type": "script"
> }
> }
> },
> "meta": {
> "name": "Button_confirm"
> },
> "position": {
> "basis": "50%"
> },
> "props": {
> "image": {
> "icon": {
> "path": "material/check"
> }
> },
> "text": "Confirmer"
> },
> "type": "ia.input.button"
> },
> {
> "events": {
> "component": {
> "onActionPerformed": {
> "config": {
> "script": "\tif self.view.params.send_cancel:\n\t\tpayload \u003d {\n\t\t\t\u0027confirm\u0027: False,\n\t\t\t\u0027source_id\u0027: self.view.params.source_id\n\t\t}\n\t\tif self.view.params.payload:\n\t\t\tpayload.update(self.view.params.payload)\n\t\tsystem.perspective.sendMessage(\n\t\t\tmessageType\t\u003d self.view.params.handler,\n\t\t\tpayload\t\t\u003d payload,\n\t\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
> },
> "scope": "G",
> "type": "script"
> }
> }
> },
> "meta": {
> "name": "Button_cancel"
> },
> "position": {
> "basis": "50%"
> },
> "props": {
> "image": {
> "icon": {
> "path": "material/close"
> }
> },
> "primary": false,
> "text": "Annuler"
> },
> "type": "ia.input.button"
> }
> ],
> "meta": {
> "name": "buttons"
> },
> "position": {
> "basis": "32px",
> "shrink": 0
> },
> "props": {
> "style": {
> "gap": "10px"
> }
> },
> "type": "ia.container.flex"
> }
> ],
> "meta": {
> "name": "root"
> },
> "props": {
> "direction": "column",
> "style": {
> "gap": "5px",
> "padding": "5px"
> }
> },
> "type": "ia.container.flex"
> }
> }
> 
> ```

Copy the json, create a new flex based view, shift + right click on it and select `paste json`.  
Check the buttons scripts, see how it works, and make your own.

Then, on your `AddBrassTag` popup's root, add a script. In there, you can add a message handler. The name you give it is the value you'll pass to the `handler` parameter of the popup, so that it can send this message when the "confirm" button is clicked.  
In the handler, check `payload['confirm']`, and handle the inserts there.

As a bonus, here's the function I use for bulk inserts:

```python
def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string): The table to insert into
		data (list of dicts): A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None, None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

```

edit: The names of the parameters may be confusing. `handler` is the message handler, `message` is the message that will be displayed in the popup.

---

<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 24, 2024, 7:28pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/5 "2024-08-24T19:28:48Z")

</div>

You're going to have better luck executing this as a Prep Query, than as a Named Query. Anytime that you don't know exactly what you need to query/update, a using a Named Query can become cumbersome quickly.

On top of that, you are running a Query for every row in the table, that has the potential to hammer your db significantly if a number of records need to be updated, which will make the UX less than desirable.

You'll be better off with something similar to this (Note: You didn't share your actual query so this is just a guess at what is going on):

```python
def runAction(self,event):
    libraryName.saveBrassTags(self.getSibling("Table").props.data,self.session.props.aut.user.userName)

```

In a project library:

```python
def saveBrassTags(brassTagData,user):
    args = [row[col]['value'] for row in brassTagData if row['BrassTag']['value'] for col in ('BrassTag','BrassTag_Desc')]
    query = """
            INSERT INTO brassTags
            VALUES {}
            WHERE user = ?
            """
    query.format(",".join(["(?,?)"] * len(brassTagData))
    args.append(user)
    system.db.runPrepUpdate(query,args)

```

This will allow a single insert operation for multiple rows. As for the general approach, I would do as @pascal.fragnoud has recommended.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 24, 2024, 7:30pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/6 "2024-08-24T19:30:12Z")

</div>

I am very thankful to you for sharing the detailed solution.

But Besides of this purpose I have few more tables where I will be showing a user old and updated values for confirmation. I was thinking maybe I can use the same solution there.

I am very new to Ignition and trying to learn it step by step.  
Just learned how to open a popup, then transferred the data from one table to another. Now trying to insert it into the table. On all of the resources on web, I couldn't found an example of parameter based named Insert Query.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 24, 2024, 7:31pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/7 "2024-08-24T19:31:52Z")

</div>

this is the simple query that I will be using `INSERT INTO BrassTag(BrassTag, BrassTag_Desc, user) VALUES (:BrassTag, :BrassTag_Desc, :user);`

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 24, 2024, 7:37pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/8 "2024-08-24T19:37:25Z")

</div>

It is indeed a bit different if you want to show the user a diff in the confirmation popup.  
But I'd probably still make the popup a simple confirmation, though more "specialized" than the one I shared. Pass it 2 datasets, with the old and new data, and display them in 2 tables or whatever is best suited for your data.  
But that would be the only difference, you'd still send a message to confirm or cancel, catch that message from the calling view, and handle whatever should happen from there.

If you handle the logic from the popup, then it's inherently not reusable, or you'll need to parameterize the logic to suit whatever you throw at it.  
That's how you end up with a bunch of `if`s and checks and whatnot.  
Don't do this. Keep it simple.

> [@Farhan\_Basheer](#):
>
> this is the simple query that I will be using `INSERT INTO BrassTag(BrassTag, BrassTag_Desc, user) VALUES (:BrassTag, :BrassTag_Desc, :user);`

Use the bulk insert function I posted above. The parameters are described in the doc string.  
Use it like this:

```python
q, v = build_insert_string("table_name", data)
system.db.runPrepUpdate(q, v)

```

---

<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 24, 2024, 9:30pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/9 "2024-08-24T21:30:04Z")

</div>

In that case, my suggested code would look like this:

```python
def saveBrassTags(brassTagData,user):
    args = [row[col]['value'] for row in brassTagData if row['BrassTag']['value'] for col in ('BrassTag','BrassTag_Desc')]
    query = """
            INSERT INTO brassTag (BrassTag,BrassTag_Desc,user)
            VALUES {}
            """
    query.format(",".join(["(?,?," + user + ")"] * len(brassTagData))
    system.db.runPrepUpdate(query,args)

```

@pascal.fragnoud 's code does the same thing, just more generically.

Note that, if all columns are being inserted, then technically you do not need the columns list in the query.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 6:56am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/10 "2024-08-26T06:56:40Z")

</div>

Finally I have reached here, the data is being passed to the `Confirmation Popup` and message is being received on the `Add_BrassTag` view again. Now Stuck in building the query for insert.  
I am assuming that I need to use the `Named` Query to write the data.

I am using this script to write the data when confirm message is being received.

```python
def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': self.session.props.auth.user.userName
            } for row in data if row['BrassTag']
        ]
        
        q, v = Query_BrassTag_Insert("BrassTag", data)
        system.db.runPrepQuery(q, v)
        
        # Execute the query
        if q and v:
            system.db.runPrepUpdate(q, v)
        
        # Clear the table data after insert
        self.getSibling("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getSibling("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]

```

But Getting this Logical error in the Logs

```python
Error running Popup/Add_BrassTag@PL5dgDy-E/root.onMessageReceived(self, payload): Traceback (most recent call last): File "<function:onMessageReceived>", line 15, in onMessageReceived NameError: global name 'Query_BrassTag_Insert' is not defined

```

**Named Query Name** : `Query_BrassTag_Insert`  
**Query Itself** : `INSERT INTO BrassTag(BrassTag, BrassTag_Desc, user) VALUES (:BrassTag, :BrassTag_Desc, :user);`

Any Guess what I am doing wrong here?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 26, 2024, 7:05am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/11 "2024-08-26T07:05:05Z")

</div>

> [@Farhan\_Basheer](#):
>
> ```python
> q, v = Query_BrassTag_Insert("BrassTag", data)
> system.db.runPrepQuery(q, v)
>         
> # Execute the query
> if q and v:
> system.db.runPrepUpdate(q, v)
> 
> ```

As the error says: You're using something that doesn't exist.  
`Query_BrassTag_Insert` isn't defined anywhere. This is NOT a named query, it's the function I posted above. here: [Issue in using Insert Named Query on a Populated Table - #4 by pascal.fragnoud](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/4)

Also, remove the `runPrepQuery` call.  
The rest of the script looks ok to me. Though you don't need to check if both q and v are None. `if q:` should be enough.

The next step would be to add some error handling, to warn the user if something went wrong.  
something like:

```python
try:
	system.db.runPrepUpdate(q, v)
	clear table
except Throwable as e:
	warn user that something went wrong
	raise

```

`Throwable` is the java error object, you can import it with `from java.lang import Throwable`.  
You need it for database errors.  
I also usually re raise errors after warning the user. This will make it bubble up to the gateway logs, and it gives me a chance to handle the error logically somewhere else if needed (warning the user is good, but it may not be enough in some cases)

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 7:17am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/12 "2024-08-26T07:17:48Z")

</div>

I think I'm very close. Just some problem with the query. `Error executing system.db.runPrepUpdate(insert into BrassTag (BrassTag_Desc,BrassTag) values (?,?), [desc, 6500], db, , false, false)`

```python
def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': self.session.props.auth.user.userName
            } for row in data if row['BrassTag']
        ]
        
        q, v = build_insert_string("BrassTag", data)
                
        # Execute the query
        if q:
            system.db.runPrepUpdate(q, v)
        
        # Clear the table data after insert
        self.getSibling("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getSibling("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]
        
        
def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string): The table to insert into
		data (list of dicts): A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None, None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

```

**Error Message** : `Error running Popup/Add_BrassTag@PL5dgDy-E/root.onMessageReceived(self, payload): Traceback (most recent call last): File "<function:onMessageReceived>", line 19, in onMessageReceived at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(insert into BrassTag (BrassTag_Desc,BrassTag) values (?,?), [desc, 6500], db, , false, false)`

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 26, 2024, 7:31am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/13 "2024-08-26T07:31:27Z")

</div>

You're building `insert_data`, but passing `data` to the `runPrepUpdate` function.

If you still have issues, you can log the values and see if they are what you think they are.  
You can also try to run that query in the database query browser (you can access it from the "tools" menu).

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 8:31am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/14 "2024-08-26T08:31:21Z")

</div>

@pascal.fragnoud  
@lrose

Thank you so much guys, finally it has worked.  
There were two issues one, I wasn't logged in, two, I had to mentioned the name of database "Explicitly" in the `onMessageReceived` method.

here is the complete code of the view side.

```python
def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': payload['user']
            } for row in data if row['BrassTag']
        ]
        
        # Build the query and values
        q, v = build_insert_string("BrassTag", insert_data)
        
        # Debugging: Print the query and values
        system.perspective.print("Query: {}".format(q))
        system.perspective.print("Values: {}".format(v))
                
        # Specify the database name, it is important.
        database_name = 'test_mdcmaster'
        
        # Execute the query
        if q:
            system.db.runPrepUpdate(q, v, database_name)
        
        # Clear the table data after insert
        self.getChild("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getChild("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]

def build_insert_string(table, data, columns=None):
    if not data:
        return None, None
    if columns is None:
    	# Specify the order explicitly, this should match with the insert query order
        columns = ['BrassTag', 'BrassTag_Desc', 'user']  
    marks = "({})".format(','.join("?" for _ in columns))
    marks = ",".join(marks for _ in data)
    col_names = ','.join(columns)
    q = "insert into {} ({}) values {}".format(table, col_names, marks)
    values = [row[c] for row in data for c in columns]
    return q, values

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 26, 2024, 8:34am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/15 "2024-08-26T08:34:33Z")

</div>

Nicely done.

One last thing: Put the `build_insert_string function` in a library script, then use the full path to call it.  
Remove the hardcoded list of columns and use the `columns` parameter to pass that list instead.  
Actually, the keys in the `inserted_data` dicts seem to match those column names, so you don't even need to specify that list.  
And leave the docstring in there. Your library functions should be documented.

For example, mine is in "lib/queries", and I call it like this:

```python
lib.queries.build_insert_string('table_name', data)

```

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

basically, take the function exactly as I posted it, put it the library, and call that.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 8:40am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/16 "2024-08-26T08:40:28Z")

</div>

One last question tough!  
Can I use a table to show the data instead of a label on the popup?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 26, 2024, 8:42am UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/17 "2024-08-26T08:42:33Z")

</div>

Yes, if you're passing data formatted for a table, there's no reason you can't use a table.  
But in this case I suggest you make another popup for this, and keep the basic popup\_confirm for later use.  
Copy the view, paste it, then replace what needs to be replaced.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 3:19pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/18 "2024-08-26T15:19:41Z")

</div>

Can I get the guidance of the steps needs to be followed? 😊

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [August 26, 2024, 3:23pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/19 "2024-08-26T15:23:35Z")

</div>

It should be pretty easy:

- add a `data` (or whatever you want to call it) parameter to your popup
- add a table component to the table
- bind the table's `prop.data` property to the `data` parameter

That's it.  
As long as the data you pass to the popup is formatted properly for a table, it will work.  
So if you just want to show a copy of the table the user built in the calling view... you can just pass that directly. It's already formatted for a table.

---

<div class="post-metadata">

**Author:** ![Farhan\_Basheer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/farhan_basheer/32/66759_2.png) [@Farhan\_Basheer](https://forum.inductiveautomation.com/u/Farhan_Basheer)\
**Post date:** [August 26, 2024, 3:33pm UTC](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117/20 "2024-08-26T15:33:56Z")

</div>

"Data Formatted for Table" makes me confused

`{"data":[{"BrassTag_Desc":"desc","BrassTag":"12300"}],"user":null}`  
This is what I am currently getting from my View.  
Is this format not good to show on a table?

[Next page](https://forum.inductiveautomation.com/t/issue-in-using-insert-named-query-on-a-populated-table/95117.md?page=2)