Using Stored Procedures

Updated on

One of the most powerful features of Sonic Forms is its ability to invoke a stored procedure when a form is successfully submitted. Stored procedures can be used for any kind of data update or transformation based on the information provided by a user. Stored Procedures used by Sonic Forms have the following parameters:
  • @ID – ID of the iMIS user who submitted the form or on whose behalf the form was submitted (10 character field). This field also accepts variants as @iMISID, @IMIS_ID, @USERID and @USER_ID
  • @SEQN – SEQN ID of the multi-instance record updated by the user (integer field)
  • @FORM_NAME - the name of the Sonic Form (50 character field)
  • @MESSAGE - a message displayed to the user when the procedure is invoked (255 character field)

Typical examples of how a stored procedure could be used with Sonic Forms are:
  1. Set a CREATED_DATE field to today's date.
  2. Set a SOURCE field to “WEB”
  3. Set a STATUS code to “NEW”
  4. Run a billing procedure to insert new billing records for a new member.
  5. Insert an Activity record to indicate that an application was received.

Here is a simple example of a stored procedure used on form submission:

In this case a text field is updated with the name of the Sonic Form that has been run on submission.

Understanding Return Codes

Stored procedures should always end with a Return Code. The Return Code is an integer value and is normally either positive or negative.
  • A positive number return code (e.g. 1, 9 or 99) will indicate that the stored procedure was successful. The return message will be framed in a green "success" format or the user will be transferred to another page as specified on the button settings.
  • A negative number return code (e.g. -1, -10, -57) will indicate that the stored procedure was unsuccessful. The return message will be framed in a red "fail" format and the user will not be transferred to another page but will see the form they are working on reloaded and ready for them to correct the issue.
If you do not include a specific return code in your stored procedure it will return a "0" as a success message. This will be treated as a "success" condition. If a stored procedure fails for a technical reason (e.g. field missing or SQL error) then it will return a negative error code.

Global versus Targeted Messages

In Sonic Forms 2.x, a Global Error message was defined per form which is returned when a special message is not set in the Stored procedure. The stored procedure message will be displayed when a button is configured to use it. See button settings for more details.

Targetting Specific Records

When working with Multi-instance records, you may wish to apply a stored procedure to a specific multi-instance record. If a multi-instance table is specified in the Sonic Forms Admin Panel then Sonic Forms will pass a SEQN number to the stored procedure. If this Multi-instance table is not selected then Sonic Forms will only pass the ID to the Stored Procedure.

This example:

sets a field called "SUBMITTED" to today's date and updates a status code on a resource library record in iMIS but does not affect any other records in the database.

Note that while you may have more than one multi-instance table defined on a Sonic Form only one primary table can be used with the SEQN feature as defined in the Sonic Forms Admin Panel. If the SEQN table is not defined then a value of “0” will be passed to the Stored Procedure.