Example of using Custom SQL Template - Screen Form

I thought it would be an instructive example of a advanced Custom SQL Template on a form's connector to a SQL Server.

Premise:

Need to update several tables in SQL Server

Need to ensure all are updated, or none

Need to INSERT new rows, OR UPDATE existing rows.

First about INSERT/UPDATE: I found a neat tip; Try to UPDATE then after the statement add IF @@ROWCOUNT = 0 INSERT

If the record does not exit, the IF statement will trap it and instead insert. Dont need to mess around with MERGE statement.

Used BEGIN TRANSACTION / COMMIT TRANSACTION nested in a TRY / CATCH.

I had some early errors about the transaction not being commited, hence the empty COMMIT at end.

I also am considering another set of tables that will always be INSERTED into, perhaps for debugging.

I could have a SQL block that checks the value of a FOF object, {{WRITE_LOG}}='YES', as example. If true, INSERT the row.

Check the attachment, there are many examples of using repeating groups.

HTH somebody 

Rob