Hi James,

Thanks a lot for your kind help, however as you might have expected I ran into some issues...

Please see the attached Screenshot, which shows how the database looks, I have the Stored procedure made, however I don't know how to make it propagate into Table?

My Database name is Diana & so is the process name, I have put it appropriately in the script, please confirm it's correct.

Then in the Lookup I set up column result to : count

Unfortunately, after all these it's not showing up the number.

Can you please help, what am I missing?

Thanks in advance,

S

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LabTestingSubCount 
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @count int;
  -- set the padding for the submission count
  DECLARE @padding int = 2;
  -- set the prefix for the submission count
  DECLARE @prefix nvarchar(50) = 'INV2016';
  -- set the name of your Forms process
  DECLARE @process nvarchar(max) = ‘Diana’;
  SET @count = (
    SELECT COUNT(submission_id)
    -- make sure to change FormsDB to your actual Forms database
    FROM [Diana].[dbo].[cf_form_submissions]
    WHERE process_id = (
      SELECT bp_id
      -- make sure to change FormsDB to your actual Forms database
      FROM [Diana].[dbo].[cf_business_processes]
      WHERE name = @process
    )
    -- step_id is 1 for the initial form submission; edit as appropriate
    AND step_id = '1'
  );
  SELECT @prefix + RIGHT('00000'+CONVERT(VARCHAR,@count + 1),@padding) AS 'count';
END
GO

The user has full rights to the DB & also to the forms process.

Hi Sahil,

Don't use the Diana database; use whatever database is configured in the Forms configuration page under the database tab. The idea is to query Forms data directly to determine how many submissions have already been made, then increment that value and display the (formatted) result. It does not actually query the Diana database with your other lookup data. However, I do highly recommend to not create the stored procedure directly in the Forms database itself.

So the setup is: in Diana, create the stored procedure. In the stored procedure, reference the other Forms database, not Diana. It should work then. The only other caveat I can think of would be if you are not on Forms 10.1, the database schema is slightly different but I do not think any differences will affect this procedure.

Hope this helps!

I'm using 10.1, so it should be fine.

I will try tomorrow & keep you posted

Thanks

Hi James,

I could not resist & tried, but again it doesn't populate the number :(

Please see attached screenshot for how I have configured the Lookup & the Stored procedure, the forms DB's name is Forms

Laserfiche Forms Version 10.1.0.559

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LabTestingSubCount 
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @count int;
  -- set the padding for the submission count
  DECLARE @padding int = 2;
  -- set the prefix for the submission count
  DECLARE @prefix nvarchar(50) = 'INV2016';
  -- set the name of your Forms process
  DECLARE @process nvarchar(max) = 'Diana';
  SET @count = (
    SELECT COUNT(submission_id)
    -- make sure to change FormsDB to your actual Forms database
    FROM [Forms].[dbo].[cf_form_submissions]
    WHERE process_id = (
      SELECT bp_id
      -- make sure to change FormsDB to your actual Forms database
      FROM [Forms].[dbo].[cf_business_processes]
      WHERE name = @process
    )
    -- step_id is 1 for the initial form submission; edit as appropriate
    AND step_id = '1'
  );
  SELECT @prefix + RIGHT('00000'+CONVERT(VARCHAR,@count + 1),@padding) AS 'count';
END
GO

Any suggestions what am I missing?

Thanks

S

Hi Everyone,

I know this post is old, but I need to know how to format the output of a stored procedure to look like this "EML-2021-0615-001".

Hoping for your response.

Thanks