Workflow Soluiton

I have created a custom form, based off of the video series found here. http://www.symantec.com/connect/videos/create-workflow-using-service-catalog-request-template-part-1

I have everything working as I would like, except for 1 final detail I cannot seem to get past. I’m trying to create an Edit action that will allow for original input in the form to be updated and saved in the original row of the database. The Edit action that is built into the template only updates the data in the workflow, not in the database. Part of the instructions included creating an Integration Library using User Defined Type with DB Mapping (ORM). This creates an “Insert or Update (Form Name)” component. This works, in that it writes to the DB. But it creates a new row in the database, it doesn’t allow to update the row for the selected ticket, at least not that I can find. It generates a new _id key so it creates a new row.

I edited the Integration Library, and added an EditForm generator using the Table Generator and selecting the table related to the form, with Read Records, Read Records (by key) and Write Records components. I can use the Read All Records component to add the rows in the DB to a grid in the form, select the row I want to edit, then use the Write component to update the row in the DB. Of course this will be problematic once several rows are added. Using the Read Records (by key) component, I cant find anything to key off of to find data. Everything I try ends with it exiting through the Data Not Found channel.

I’m looking for a way to have only the row of data for the ticket that is being edited to be displayed, and then edited and that row updated in the DB. Can anyone offer assistance in what I need to do to accomplish that? Thanks

For my money, I don't use "User Defined Type with DB Mapping (ORM)" or the Table Generator. I either write a stored procedure or use a sql query component that will check my input variables key field (usually just an "id" column or something to see if the row exists. If not, it does an insert and if it does, it performs an update. Preferably with a transaction begin and rollback/commit inside a try block, just in case.

So, to more specifically answer your question, do you have a good key value for your table? If not, just tack on an auto incrementing "id" column. Obviously, this is much easier if the table has now data in it yet. Just to give you an idea of what I'm talking about, I dropped in the template I use below. If you are using the sql query generator, you may have to trick it into not thinking some things are variables by leaving them out, clicking on the find variables button, then putting the full query back in.

DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;

SELECT [id],... FROM [table]
WHERE [id] = @id

IF @@ROWCOUNT > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
        UPDATE [table]
        SET [col1] = @col1
            ,...
        WHERE [id] = @id
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
        RAISERROR(@ErrorMessage,@ErrorSeverity,1)
    END CATCH
END
ELSE
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
        INSERT INTO [table]
        ([col1],...)
        VALUES
        (
            @col1
            ,...
        )
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
        RAISERROR(@ErrorMessage,@ErrorSeverity,1)
    END CATCH
END

EDIT: I suppose I should point out that this is my template for use in the stored procedure. I'm not sure how the workflow sql component would hadn't the SELECT statements for the error handling. As it would be a different set of results from the normal, it may take a dive.

Yes, using the ORM it created a table_name_id primary key automatically. I appreciate the suggestions. I know less about SQL than I do about workflow, but i have a couple of folks in the building I can get to assist me with the query. Thanks

I found a resolution for the issue. In the Integration Library I added a SELECT Components component. Follow the Read All up with the SELECT Component and it pulls in the data from the DB, then after editing in the form and using the Write component it updates table as desired. Might not be the pretty way, but it works for this time. Might try other methods for the next one I try. Thanks