In the default system the StoredProc datatype is used to generate the reference id values for
- Case_referenceid
- Soln_referenceid
When a new record is created, these columns invoke a call to the a Stored procedure called: eware_default_values. This by default has the following definition. You will see from the code that it in turn calls another stored procedure called eware_get_identity_id.
I have mentioned the use of eWare_get_identy_id before in the article "
How can I edit the RelatedCompaniesList?".
The eware_default_values stored procedure had the following code:
CREATE PROCEDURE eware_default_values
@table_name nvarchar(50),
@identity_name nvarchar(50),
@field_name nvarchar(50),
@logon_no int,
@extravalue nvarchar(50)='',
@default_value nvarchar(128)='' OUTPUT
AS
BEGIN
/* Declare variables to be used */
DECLARE @unique_value int
DECLARE @ErrorSave INT
SET @ErrorSave = 0
IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Solutions') AND (@field_name = 'Soln_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE BEGIN
SELECT @default_value=''
END
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave
END
GO
The effect of the default StoredProc (eware_default_values) is to generate a number based on ID of the user creating the new Solution or Case plus the next value pulled back from the eware_get_identity_id stored procedure. You are able to change the pattern of the returned value as you need.
The stored procedure will only fire when the record is being created. This is much the same as trying to use the defaultvalue property of a field in a oncreate script in a screen.
From the above code you can see that the two tables Solutions and Cases are specifically mentioned. Therefore if you wish to use the StoredProc field type on another table then you will have to alter this stored procedure.
Example of a Changed Stored Procedure
The SQL example below shows how the stored procedure "eware_default_values " can be altered to allow a field of type "StoredProc" be added to a custom entity.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eware_default_values]
@table_name nvarchar(50),
@identity_name nvarchar(50),
@field_name nvarchar(50),
@logon_no int,
@extravalue nvarchar(50)='',
@default_value nvarchar(128)='' OUTPUT
AS
BEGIN
/* Declare variables to be used */
DECLARE @unique_value int
DECLARE @ErrorSave INT
SET @ErrorSave = 0
IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Solutions') AND (@field_name = 'Soln_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Invoice') AND (@field_name = 'invo_number')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE BEGIN
SELECT @default_value=''
END
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave
END
This assumes that the field "invo_number" has been added to the 'Invoices' table and is of type 'StoredProc". It is a good idea to make the field read only. The field can then be added a screen.
Self Service
If you create a case via Web Self Service API via a screen block. Then the generated reference id will be in the form 0-XXXXXXXX.
Creating a Record in Code
The use of the screen block to create the record is critical. The stored procedure will only fire when you create the record via the user interface. So if you insert a record using the COM API in for example a table level script or an external script the stored procedure will not fire and the default value will not be populated.
var username = "Admin";
var password = "";
var CRM = new ActiveXObject("eWare.CRM");
CRM.FastLogon = 3; //this prevents the meta data from loading.
CRM.Logon(username,password);
//Create a Record
//Table referenced can either be an eWare table or externally referenced table
var myRecord = CRM.CreateRecord("case");
myRecord.case_description= "Test Case";
myRecord.case_primarycompanyid= 28;
myRecord.SetWorkflowInfo("Case Workflow", "Assigned")
//Use the myRecord.SaveChanges method to have the record persist in database
myRecord.SaveChanges();
Running this script will result in a case with a "null" case_referenceid. This limitation will also apply to the Web Services. If you insert a case or a solution using web services then the referenceid will not be populated.