...
| Code Block |
|---|
eval ('x=' + http.get('http://localhost:8082/database/BIRT/getOrderNumber'));
onum.value = x.resultSet[0].onum; |
Unique Sequential Id
This 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:
...
| Code Block |
|---|
<query name="customers">
<retrieve>
<statement>
SELECT * FROM customers WHERE emailAddr='%{domain}%'
</statement>
</retrieve>
</query> |