I am trying to configure a workflow to insert or update a SQL table. I'm running into problems with the student is already in the SQL table. It is not inserting new rows. Any suggestions?
I could be not thinking correct on this, but an update statement would not insert a new row, it would only update existing.
Any ideas of how I can use an insert statement only if the row is not already in the database? The form that I am using looks up the information from the SQL table and then the user adds a new row. I initially had this as an insert data without the condition, but it would create a new row in the SQL database for rows that were already pulling from the lookup.
A little more background on what your trying here....
Your SQL table, is this table your "master" table where each student would only have one single record? Or is this a historical or data table that would reference a master record in another table, where each student would have multiple entries?
Here's what i am thinking you would need to configure in SQL. Just guessing so I apologize if i am totally off base here...
Where if i am student id 1, joe, I would open the form enter the 2 activities in which i participated, using the add row button in the form to enter the second. Upon submission, the workflow would only do an insert for each row. To validate the students id, you could add a search field at the top of the form which would populate the name field from your stored procedure. If the students name is returned from table 1, it fills their name and then an id field on the form (which you can hide so they do not see, but is available for the workflow). If the students name is not there, then they enter their name and all relative information. Your routing is based on if the id field is populated or blank. If blank, then you have the workflow assign the next id in line, insert to the table 1, then insert for each row into table 2.
Thanks for the reply! That is very close to the procedure. The only difference is the lookup is pulling the previous entries, with the option to add a new entry to the table. For example, when I go into the student's record, I would like to see the previous encounter notes and then add the note from the current visit or update any changes that may have been made to previous notes.
Assuming your for each row loop is targeting the query data, that returns no rows, it would never run because there are no rows to loop though.
If it should just run once, you may want to remove the loop for both sides.