TN 1305 How to Create an Auto Incrementing Ticket/Log Number in j5

Description

This article from InSource shows how to create an auto incrementing ticket/log number in j5

• Author: Ron Tanner
• Published: 12/13/2022
• Applies to: j5 2019

Details

How to create an auto incrementing ticket/log number in j5

Open the IDE project you want to modify. Double click the general_logbook and navigate to the “Auto Increment” parameter section. 

Input the following parameters:

• Display Field: ticket_number
• Pad to Length: 0
• Start Number 1

Save the general_logbook modification and close the tab.

Open the logbook fields and add a new field called “ticket_number”

Input the following parameters:

• Input type: INTEGER
• Data Type: int
• Label: Ticket#
• Grid Label: Ticket#

Add another field called “log_unique_id”

Input the following parameters:

• Input Type: TEXT
• Hidden: check the box
• IndustraForm Element ID Synonym: log_unique_id
• Attribute Name: log_unique_id
• Direction: from_industraform

Save the general_logbook fields and close the tab.

Open the “Grid Layout” and add ticket_number to the grid layout. Save the general_logbook small_view and close the tab. 

Deploy the IDE project.

Connect to the database hosting the j5 database.

Create the following Stored Procedure:

CREATE PROCEDURE [dbo].[usp_TicketNumber]

@logID nvarchar(255)

AS

BEGIN

SET NOCOUNT ON;

SELECT auto_increment_number FROM general_logbook WHERE log_unique_id=@logID

END

GO

Open the IndustraForm designer:

Create a new default form and paste the following into the file tab after the industraform has been created:

version: "2.11"

schema: "spreadsheet-form"

---

sections:

- auto_submit: true

complete_if: "=OR(Status=\"Closed\",Status=\"Cancelled\")"

elements:

Label21:

type: "label"

styles:

direct:

background:

backgroundColor: "#FFFFFF"

text:

fontWeight: "bold"

value: "Ticket Number"

ticket_lookup_value2:

type: "label"

styles:

direct:

text:

fontSize: "x-large"

fontWeight: "bold"

textAlign: "left"

value: "=ticket_number_lookup"

search_enabled: true

Message:

initial_value: "-"

predefined_message_list: null

Label_CreatedByUser: {}

CreatedByUser: {}

Label_EventTime: {}

EventTime: {}

Label_CreatedByGroup: {}

CreatedByGroup: {}

Label_Status: {}

Status:

initial_value: "Open"

Label_TimeCreated: {}

TimeCreated: {}

Label_Category: {}

Category: {}

Label_ClosedByUser: {}

ClosedByUser: {}

Label_Priority:

value: "Severity"

Priority:

value_source: "EXPRESSION"

label: "=Label_Priority"

Label_TimeClosed: {}

TimeClosed: {}

Label_Area: {}

Area:

value_source: "EXPRESSION"

Label_AddToHandover: {}

AddToHandover: {}

Label_Equipment:

value: "Premise"

Equipment: {}

Label877:

type: "label"

value: "Attach Documents Below:"

ticket_number_lookup:

type: "label"

value: "=IMPORT.SQL(\"default\",\"EXEC usp_TicketNumber ?\",log_unique_id)"

visible_if: "FALSE"

log_unique_id:

type: "text"

value: "=TimeCreated"

value_source: "EXPRESSION"

visible_if: "FALSE"

Attachments: {}

layout:

- - "Label21"

- "ticket_lookup_value2"

- "-"

- "Message"

- "-"

- "-"

- "-"

- "Label_CreatedByUser"

- "CreatedByUser"

- - "Label_EventTime"

- "EventTime"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label_CreatedByGroup"

- "CreatedByGroup"

- - "Label_Status"

- "Status"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label_TimeCreated"

- "TimeCreated"

- - "Label_Category"

- "Category"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label_ClosedByUser"

- "ClosedByUser"

- - "Label_Priority"

- "Priority"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label_TimeClosed"

- "TimeClosed"

- - "Label_Area"

- "Area"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label_AddToHandover"

- "AddToHandover"

- - "Label_Equipment"

- "Equipment"

- "-"

- "+"

- "-"

- "-"

- "-"

- "Label877"

- "-"

- - "ticket_number_lookup"

- "log_unique_id"

- null

- "+"

- "-"

- "-"

- "-"

- "Attachments"

- "-"

The industraform leverages the form creation time to create a unique ID for the IndustraForm. The id is written to the database after the initial save the log entry. The form then uses the stored procedure to look up the unique ID in the j5 database general logbook table to retrieve the ticket_number. This process insures the ticket number is displayed on the Industraform after the form has been saved and without the need to close and reopen the log entry.

Please Note: Be sure to Authorize the Query(Stored Procedure: usp_TicketNumber) in the j5 web application or via the IndustraForm designer.

Create a new log and select the save button. The ticket number will show up on the IndustraForm.

Ticket number will also display in the operations logbook display: