Question

Run Stored Procedure on Submit

Forms

Updated September 3, 2020

asked on September 2, 2020 • Show version history

Good Afternoon,

I have tried to understand how to get the stored procedure to run on submission of the form. It is still running on page load. Since I don't understand but the very very basics of using Javascript I can not figure out what I am doing wrong. Here is the information I gathered from other post. Can someone look it over and do a couple of things for me so I can learn. Would you fix any of my errors and comment the lines so I understand the actions they are taking?

I know it is not your job to teach me but I am trying to learn so I can do these myself.

Thanks for all your help with this.

Here is the version of Forms I am using. 10.4.0.1256

$('.action-btn').on('click', function(event)  
{
  if (requiredfilled() && $('.notused input').val() === "") //This is the stored proc trigger
      {
        $('.notused input').val("1").trigger('change');//This is the stored proc value to trigger the procedure
        event.preventDefault();
        waitfunction();
      }
});
function requiredfilled()
{
  return _.every($('li:has(.cf-required):visible input,li:has(.cf-required):visible textarea'), function(elm) {return $(elm).val() != '';});
}
function fieldfilled()
{
  return ($('#q164 input').val() != '');
}

function waitfunction()
{
  setTimeout(function(){    
    if(fieldfilled())
      $(".Submit").click();
    else
      waitfunction();
  }, 500);
}

0 0

Answer

SELECTED ANSWER

replied on September 2, 2020 • Show version history

This is a bare bones example, it can be fleshed it out with your specifics. It assumes a 'starting' form with basic fields and an 'approval' form that includes an Application Number field. The stored procedure will generate the new application number and workflow will make that value available to the 'approval' form via workflow. The first step after the Starting Form is submitted is to run a Service Task that calls the workflow.

Make sure the Service Task is configured to wait for the workflow to finish before proceeding.

The workflow that we will call is very simple. It calls the stored procedure and then stores the resulting application number to the approval form field.

To call the SQL stored procedure use a Custom Query workflow activity. (Note: In this example the stored procedure does not require any parameters to be passed to it. Your stored procedure might require parameters. Also, you must 'test' the Custom Query to let workflow know what fields and values are actually being returned from the stored procedure).

In the Set Business Process Variables activity we set the Application Number field from the Approval form to the application number value returned by the stored procedure. Use the token button to select the Form variable to set and the same token button to set that Form variable equal to the results of the custom query.

When you submit the starting form the workflow triggers and sets the Application Number that was generated from the stored procedure to the Approval form Application Number field.

1 0

Replies

replied on September 2, 2020

Beverly,

Unless I am missing something a much easier solution would be to run a Workflow that calls your stored procedure just after the Form submission and have the Workflow populate the appropriate Form fields with the results of the stored procedure for use in later steps in the Forms process. If you would like additional details I can provide screen shots...

2 0

View 3 previous replies

replied on September 2, 2020

Can you show me how to do that using workflow?

0 0

SELECTED ANSWER

replied on September 2, 2020 • Show version history

Make sure the Service Task is configured to wait for the workflow to finish before proceeding.

The workflow that we will call is very simple. It calls the stored procedure and then stores the resulting application number to the approval form field.

When you submit the starting form the workflow triggers and sets the Application Number that was generated from the stored procedure to the Approval form Application Number field.

1 0

replied on September 2, 2020

Thank you. I did try it this way but I was not sure on how to create the custom query. Below is my stored procedure and I am trying to figure out what information to use in the Workflow custom query for the USE and EXEC.

ALTER PROCEDURE "dbo"."GW_1_counter"

AS
BEGIN
DECLARE @nNum numeric(18,0), @rowInt int, @nserialNum nvarchar(20), @nRC int, @tYear nvarchar(10)
SET @tYear = YEAR(GETDATE())
SELECT @nRc = Count(*) FROM SerialNumber WHERE GWyear = @tYear
IF(@nRC > 0)
BEGIN

SELECT @nNum = GWNumber, @rowInt = Id from SerialNumber WHERE GWYEAR = @tYear
SET @nNum = @nNum + 1
SET @nserialNum = Convert(nvarchar(10), @tYear) + Convert(nvarchar(10), @nNum)
UPDATE SerialNumber SET GWNumber = @nNum, GWSerialnumber = @nserialNum WHERE Id = @rowInt
SELECT @nNum AS GWNUMBER

END
ELSE
BEGIN

SET @nNum = 1
SELECT @nserialNum = Convert(nvarchar(10), @tYear) + Convert(nvarchar(10), @nNum)
INSERT INTO SerialNumber (GWyear, GWnumber, GWSerialNumber) VALUES (@tYear, @nNum, @nserialNum)
SELECT @nNum AS GWNUMBER
END
END

0 0

replied on September 2, 2020

Your stored procedure is not using any parameters and it is returning a numeric value called GWNUMBER.

I would just try EXEC GW_1_counter in the custom query and test it.

However, each time you call your stored procedure to test it your GWNUMBER is going to increment so you will have to go into SQL and clean up the underlying table.

Does that make sense?

0 0

replied on September 2, 2020

This is a test table so I am trying to get it running smoothly before we go to something for me to worried about. Thank you for all your help with this.

1 0

replied on September 2, 2020

That worked perfectly! Thankyou

0 0

You are not allowed to follow up in this post.

Question

Question

Run Stored Procedure on Submit

Answer

Replies

Sign in to reply to this post.