Data entry form based data collection

I want to use a HMI window as a form for manual data entry into a database table. The user fills in some text boxes and selects some Boolean values and clicks submit. The data is taken from each field and entered as a new record in my table along with a time stamp. I need to clear the form after it has written to the DB as well. I have the form built so I just need the script to grab the values from each input field, insert a new record in the DB and then clear the input fields on the form. The fields are date from the latched date/time calendar, product, gallons, notes. Product and notes are text fields.

Great! Was there a question in there?

If the question was just a “how do I do this” in general, read on…

This is quite easy. Most (all) of the work will be done in a script in the submit button. The script will:
[ul]
[li]gather up the pieces of data from the form components into variables[/li]
[li]run an INSERT query into the database using these variables[/li]
[li]Clear the form components[/li][/ul]

If there is any freeform text in the form (like a description), consider using the fpmi.db.runPrepStmt to do the INSERT rather than fpmi.db.runUpdateQuery.

If you provide more specifics (like what the table looks like, what fields are to be input by the operator), we can help you with the script in a more concrete way.

Hope this helps,

Hey - no fair, you edited your post while I was writing my reply.

(for anyone reading this post - his initial version of his post didn't include any column names or anything...)

Here is an example of a script that would do this. Of course, this is just an example, as you haven't told us the component names, column names, or table names. You'll have to actually understand this script, not just cut and paste it...

parent = event.source.parent
date = parent.getComponent("Date").date
product = parent.getComponent("Product").text
gallons = parent.getComponent("Gallons").intValue
notes = parent.getComponent("Notes").text
query = "INSERT INTO mytable (date, product, gallons, notes) VALUES (?,?,?,?)"
fpmi.db.runPrepStmt(query, [date, product, gallons, notes])
parent.getComponent("Product").text = ""
parent.getComponent("Gallons").intValue = 0
parent.getComponent("Notes").text = ""

Hope this helps,

This failed for some reason and gives me an error:

Gateway Error 301: SQL Error: Parameter index out of range (1> number of parameters, which is 0). For Query:INSERT INTO inputdemo (Date, Product, Gallons, Notes) VALUES (date, product, gallons, notes)

This is what I setup behind the submit button:

parent = event.source.parent 
date = parent.getComponent("Calendar").formattedLatchedDate
product = parent.getComponent("Product").text
gallons = parent.getComponent("Gallons").intValue 
notes = parent.getComponent("Notes").text 
query = "INSERT INTO inputdemo (Date, Product, Gallons, Notes) VALUES (date, product, gallons, notes)" 
fpmi.db.runPrepStmt(query, [date, product, gallons, notes]) 
parent.getComponent("Product").text = "" 
parent.getComponent("Gallons").intValue = 0 
parent.getComponent("Notes").text = ""

Prepared statements use question marks in place of the values of their parameters. Also, don't use the formatted latched date, use the actual latched date. With prepared statements, you want the real date object, not the date object turned into a string. Try this:

parent = event.source.parent 
date = parent.getComponent("Calendar").latchedDate 
product = parent.getComponent("Product").text 
gallons = parent.getComponent("Gallons").intValue 
notes = parent.getComponent("Notes").text 
query = "INSERT INTO inputdemo (Date, Product, Gallons, Notes) VALUES (?,?,?,?)" 
fpmi.db.runPrepStmt(query, [date, product, gallons, notes]) 
parent.getComponent("Product").text = "" 
parent.getComponent("Gallons").intValue = 0 
parent.getComponent("Notes").text = ""

That did the trick and worked perfectly. Thanks!

No problem, glad it’s working!