Question

Stored procedure

Forms

Updated June 30, 2017

asked on June 22, 2017 • Show version history

Hi all,

My goal here is to have a stored procedure lookup a table value and return another column value that is incremented (counter column). If the lookup value doesn't exist then the SP will take the input values from the Forms tokens to make a new row in the table starting at 1.

So problem 1. I made a SP but it doesn't show up in the lookup rules dropdown for the table... what am I doing wrong?

2. I don't know if I have my SP correct or not. I'm not a SQL guru but I can muddle my way through if I know what I'm looking at (reverse engineer here). This is the code I'm trying to use, and it's probably wrong :).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Joe Fabre
-- Create date: 06/20/2017
-- Description:    Create or Update the Counter for a C.A.R.s by project
-- =============================================
CREATE PROCEDURE CAR_Counter_Update 
    -- Add the parameters for the stored procedure here
    @ProjectName nvarchar(100) = 0, 
    @NextCARNum int = 1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Check to see if Project exists in table
    IF NOT EXISTS (SELECT *
                    From CARCounter
                    Where ProjectName = @ProjectName)

             --If project doesn't exist then insert variable value which holds ProjectName token from the form and 

            --and start Counter at '1'
            Begin
                    Insert into CARCounter (ProjectName, Counter)
                    values (@ProjectName, @NextCARNum)
            end

    --If Project exists in table increment the counter by one and return the new value to Forms to use in field
    Else
            Update CARCounter
                SET Counter = Counter + @NextCarNum
                Set @NextCARNum = Counter
            Where ProjectName = @ProjectName
END
GO

Thanks for the help in advance. Looking forward to the people smarter than me here on Forms and SQL to help out :).

0 0

Replies

replied on June 25, 2017

Hi Joe,

1. Did you refresh the data source after adding the stored procedure?

2. Your sp didn't quite work for me... I updated your script as below (Do not require NextCARNum input since I don't see it useful; the output has column name "Counter"):

ALTER PROCEDURE [dbo].[CAR_Counter_Update] 
    -- Add the parameters for the stored procedure here
    @ProjectName nvarchar(100) = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    Declare @NextCARNum int
    Set @NextCARNum = 1
    -- Check to see if Project exists in table
    IF NOT EXISTS (SELECT *
                    From CARCounter
                    Where ProjectName = @ProjectName)

             --If project doesn't exist then insert variable value which holds ProjectName token from the form and 

            --and start Counter at '1'
            Begin
                Insert into CARCounter values (@ProjectName, @NextCARNum)
            end

    --If Project exists in table increment the counter by one and return the new value to Forms to use in field
    Else
	Begin
            Update CARCounter SET [Counter] = [Counter] + @NextCarNum Where ProjectName = @ProjectName
            Set @NextCARNum = (SELECT [Counter] From CARCounter Where ProjectName = @ProjectName)
	end
    Select @NextCARNum as [Counter]
END

0 0

replied on June 30, 2017

So we figured it out what was wrong with the stored procedure. It was the permissions were wrong. They didn't give LF access was why it wasn't showing up.

Thanks for the SQL suggestion. In the end I think I found a better more elegant way to do this. I had forms call a workflow that executes the stored procedure to update a table that when the user opens the form the the lookup rule reads the counter from the table.

We also found out that we couldn't use instance id, date, or time as fields to uniquely identify the form in the DB since Forms doesn't generate those numbers or doesn't populate until the form is opened.

What we did was create a unique ID (UID) field that used some java/css to take the date and time and make a number out of it and have the CSS class in the field be tied to the code we put in the form:

var tel = Date.now || function() {
  		return +new Date;
		};
      $('.UID input').val(tel);

Then we were able to clean up our SQL procedure a TON and make it a lot shorter. Here is the code that we used for it:

ALTER PROCEDURE [dbo].[TrackCARReportNum]
	--Add the parameters for the stored procedure here
	--NOTE: To test procedure in Workflow set default values for Project and BPID
	@Project nvarchar(100) , --Used to store the project name from Forms to create table name or find
	@UID nvarchar(20),
	@Counter smallint = 1 --Has to be set to 1 or it will fail
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		--Increment Counter for the current project if exists in table
		If EXISTS (select * From CARCount WHERE Project = @Project)
			SELECT @Counter = 1 + MAX(Counter) From CARCount WHERE Project = @Project;
		
		--Store values in table
		INSERT into CARCount(Project,UID,Counter) VALUES (@Project,@UID,@Counter);
END

All in all this works and avoids the problem with stored procedures in lookup rules being ran twice. Plus this keeps duplicates from happening. It also accomplishes the goal of tracking the number of forms by project as well.

0 0

You are not allowed to follow up in this post.

Question

Question

Stored procedure

Replies

Sign in to reply to this post.