Is it possible to select a row from a PMI Table and use a value from the selected row in the where clause to run an update query on a database table.
Example:
I have a PMI table with 10 rows that list unique part numbers and quantities, I want select a row from the PMI table and update the quantity of that part number in the database by clicking a button.
Is the quantity the only value that you want to modify?
The edit can be done via an input component on the screen or a popup window. I’ll use an input box on the screen.
First ensure that Row Selection Allowed is checked on the table and Column Selection Allowed is not checked. This will allow the user to select entire rows on the table.
Create a dynamic property, partNum, which will reflect the part number corresponding to the row selected.
Bind partNum to the field in the dataset that you’re looking for as an expression. Reference on DataSet binding here.
{Root Container.Table.data}[{Root Container.Table.selectedRow}, 'your_db_column_name_here']Now create a text box where you will set the new value. You will probably want to bind it’s value to a similar expression as above so that when the user selects a row in the table, the current quantity shows up. You could also put a label with the text bound that shows what part number is selected.
Now create a button. With the Jython helper you will create an SQL UPDATE query that reads the value in the text box and runs a query out of it. You may want to cheat and combine the wizards results for: Setting a value (to read the textbox), and running the update query.
It will probably look something like this:
[code]part = event.source.parent.getComponent(‘Text Field’).getPropertyValue(‘partNum’)
quantity = event.source.parent.getComponent(‘Text Field’).text
fpmi.db.runUpdateQuery(“UPDATE table SET quantity = ‘%s’ WHERE my_part_num_col = ‘%s’” % (quantity, part))[/code]
Hello,
Nathan’s approach is right on here, I just wanted to add some clarifying details:
When binding into a table with the table’s selectedRow property, you need to guard against the case when nothing is selected (in this case, selectedRow will be -1). So, the first binding above should be:
if({Root Container.Table.selectedRow}=-1, '-1',
{Root Container.Table.data}[{Root Container.Table.selectedRow}, 'your_db_column_name_here'])
This will make the partNum property be ‘-1’ when nothing is selected. (note - nathans approach assumes partNum is a string, hence the quotes around -1)
Then, the script on the button would be:
[code]part = event.source.parent.getComponent(‘Text Field’).getPropertyValue(‘partNum’)
if part != ‘-1’:
quantity = event.source.parent.getComponent(‘Text Field’).text
fpmi.db.runUpdateQuery(“UPDATE table SET quantity = ‘%s’ WHERE my_part_num_col = ‘%s’” % (quantity, part))[/code]
One more note - it would probably be a good idea to use a Numeric Text Field here instead of a normal Text Field, so that you are guaranteed to get a number for the quantity field.
Hope this helps,
Carl,
Thanks for the clarification - I hadn’t considered the default conditions.
I am interested in doing the same thing, but wonder if there is a way to actually write to the table by navigating it, instead of with other components. It seems to be faster to use a table in certain instances–large amounts of data entry.
For instance, we have a table that has times/jobs for an employee. This is something that is transferred from paper at the end of the week. It would be nice to be able to update a PMI table and let the user double check the information and then hit an ‘update’ button that moves it to the SQL table.
The first preference was to directly enter data into the PMI table and then use a button as described above to handle the update query. Since it seems there is no way to put information directly into a PMI table, I thougth about using text fields and two buttons. To this end, I created a DataSet in the form. When the button is pressed, we use toPyDataSet, update the DataSet, and change it back to a PMI dataset. Is there a better way to go about this?
Thanks,
Matt