...
| Excerpt |
|---|
|
Unique Sequential IdThis example uses a stored procedure, a table in your database, a database connector query and a business rule to generate a unique sequential number when a form loads. This number can populate a ticket or invoice number field in your form. Step 1 - Create a table in your database (SQL Server): | Code Block |
|---|
CREATE TABLE dbo.TBLUniqueID
(
UniqueID int IDENTITY(10000,1) PRIMARY KEY,
formuid varchar (255) NOT NULL
) |
Step 2 - Create a Stored Procedure (SQL Server): | Code Block |
|---|
CREATE PROCEDURE dbo.getid
@formid varchar (255)
AS
SET NOCOUNT ON;
INSERT INTO [dbo].[TBLUniqueID] ([formuid]) VALUES (@formid);
SELECT * from [dbo].[TBLUniqueID] WHERE formuid = '@formid'; |
Step 3 - Add the query to your configuration.xml file | Code Block |
|---|
<query name="insertformid" autocreate="true">
<retrieve>
<statement>EXEC dbo.getid @formid = '{formuid}'</statement>
</retrieve> |
Step 4 - Add this rule to your form | Code Block |
|---|
/*member, UniqueID, resultSet*/
var x;
var formid;
formid = _data.getParameter('form.id');
if (form.load) {
formuid.value = formid;
if (formid.length > 0) {
http.get('http://<your server>:<port>/database/CONTROL4/insertformid?formuid=' + formid);
eval ("x=" + http.get('http://<your server>:<port>/database/CONTROL4/getformid?formuid=' + formid + '&_mediaType=json'));
ID.value = x.resultSet[0].UniqueID;
} |
|
SQL Server
Here is an example with the syntax required for a SQL server stored procedure:
...