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.