Ticket Number Control (Custom Invoice Numbers with Reset)

[QMcKay]

The built in mechanism for generating Ticket Numbers is something that should not be altered in any way, since resetting a Number Generator will lead to duplicate Ticket Numbers which is not allowed and will break the system.

So if you want control over your Ticket Numbers in a custom fashion where you can reset them, you need to use an Automation flow that updates, increments, and/or resets Global Program Settings (values stored in the Database), and applies those Settings to your Tickets using a Ticket State and/or a Ticket Tag.

This Tutorial shows how control 2 separate Invoice Numbers for different Ticket Types. It can be adapted to control more than 2 Ticket Types, or 1 single Ticket Type. It demonstrates setting both a Ticket State and a Ticket Tag for flexibility, but you can choose one or the other for your implementation.

Suppose we have 2 Ticket Types, and they should have their own numbering system that is controlled separately …

• Ticket
• Ticket2

For each of the Ticket Types, we will control their Numbers separately and individually. Our format for the Invoice Numbers will be similar, in that we have a leading 4-digit “Serial” number following by a 6-digit “Document” number…

  0000-000000
  ^^^^ ^^^^^^
serial document
number number

We will increment the Document Number for each new Ticket. When the Document Number reaches a certain threshold (ie. 999999), it will Reset to 0 and the Serial Number will increase by 1.

We will have 3 Program Settings for each Ticket Type:

• INUMserA (Ticket) Serial Number
• INUMdocA (Ticket) Document Number
• INUMinvA (Ticket) Invoice Number (formatted Serial + Document)
• INUMserB (Ticket2) Serial Number
• INUMdocB (Ticket2) Document Number
• INUMinvB (Ticket2) Invoice Number (formatted Serial + Document)

We will also have a separate Ticket State and Ticket Tag for each of the Ticket Types:

• InvA (Ticket Invoice Number)
• InvB (Ticket2 Invoice Number)

---

Quick Links

• Script
• Actions
• Rules
• Printer Template Tags
• DB Tools
• Invoice Search

---

Flow

[QMcKay]

Script

IMPORTANT: Edit the value in this script named defaultLimit to contain the last value allowed before a reset occurs.

INUM [inum] (Script)

Script Name:	INUM
Script Handler:	inum

Script:

function upd(invoiceType,docLimit) {
	invoiceType  = (typeof invoiceType!=='undefined' && invoiceType!=='' ? invoiceType : 'A');
	var iNumName = 'INUMinv' + invoiceType;
	var iSerName = 'INUMser' + invoiceType;
	var iDocName = 'INUMdoc' + invoiceType;
	
	var defaultLimit = 999999;

	var iDocLimit = (typeof docLimit!=='undefined' && docLimit!=='' ? docLimit : defaultLimit);

	var qry = '';

/// initial INSERTS should only ever happen once
	qry = "SELECT count([Value]) FROM [ProgramSettingValues] WHERE [Name]='"+iSerName+"'";
	var res = sql.Exec(qry);
	if (res[0]==0) {
		qry = "INSERT INTO [ProgramSettingValues] ([Name],[Value]) VALUES ('"+iSerName+"','0')";
		var res = sql.Exec(qry);
	}
	qry = "SELECT count([Value]) FROM [ProgramSettingValues] WHERE [Name]='"+iDocName+"'";
	var res = sql.Exec(qry);
	if (res[0]==0) {
		qry = "INSERT INTO [ProgramSettingValues] ([Name],[Value]) VALUES ('"+iDocName+"','-1')";
		var res = sql.Exec(qry);
	}
	qry = "SELECT count([Value]) FROM [ProgramSettingValues] WHERE [Name]='"+iNumName+"'";
	var res = sql.Exec(qry);
	if (res[0]==0) {
		qry = "INSERT INTO [ProgramSettingValues] ([Name],[Value]) VALUES ('"+iNumName+"','-')";
		var res = sql.Exec(qry);
	}

/// get current Values
	qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+iSerName+"'";
	var res = sql.Exec(qry);
	var iSerVal = Helper.ToNumber(res[0]);
	qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+iDocName+"'";
	var res = sql.Exec(qry);
	var iDocVal = Helper.ToNumber(res[0]);

/// increment Values
	iDocVal++;

/// check limit and reset if necessary
	if (iDocVal > iDocLimit) {
		iSerVal++;
		iDocVal = 0;
	}

/// update Program Settings
	qry = "UPDATE [ProgramSettingValues] SET [Value]='"+iSerVal+"' WHERE [Name]='"+iSerName+"'";
	var res = sql.Exec(qry);
	qry = "UPDATE [ProgramSettingValues] SET [Value]='"+iDocVal+"' WHERE [Name]='"+iDocName+"'";
	var res = sql.Exec(qry);

/// format the Invoice number like 0000-000000
	var iNumValue = Helper.Format(iSerVal,'0000') + '-' + Helper.Format(iDocVal,'000000');

	qry = "UPDATE [ProgramSettingValues] SET [Value]='"+iNumValue+"' WHERE [Name]='"+iNumName+"'";
	var res = sql.Exec(qry);

/// get latest updated Invoice Number
	qry = "SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='"+iNumName+"'";
	var res = sql.Exec(qry);

	return res[0];
}

---

[QMcKay]

Actions

INUM Update Setting [Update Program Setting] (Action)

Action Name:	INUM Update Setting
Action Type:	Update Program Setting

Parameters:

Setting Name:	[:settingName]
Setting Value:	[:settingValue]
Update Type:	Update
Is Local:	False

---

INUM Update Ticket State [Update Ticket State] (Action)

Action Name:	INUM Update Ticket State
Action Type:	Update Ticket State

Parameters:

State Name:	[:iNumName]
Current State:
State:	[:iNumValue]
State Value:
Quantity Exp:

---

INUM Update Ticket Tag [Update Ticket Tag] (Action)

Action Name:	INUM Update Ticket Tag
Action Type:	Update Ticket Tag

Parameters:

Tag Name:	[:iNumName]
Tag Value:	[:iNumValue]

---

[QMcKay]

Rules

Invoice A

INUM Update Invoice Number A [Before Ticket Closing] (Rule)

Rule Name:	INUM Update Invoice Number A
Event Name:	Before Ticket Closing
Rule Tags:

Custom Constraint List (3):

Execute Rule if:

Matches

{TICKET STATE:InvA}	Is Null
[=TN('{ORDER COUNT}')]	Greater	0
Ticket Type Name	Equals	Ticket

Actions (1):

INUM Update Setting

Constraint: (none)

settingName:	INUMinvA
settingValue:	{CALL:inum.upd('A')}

---

INUM Apply Invoice Number A [Ticket Closing] (Rule)

Rule Name:	INUM Apply Invoice Number A
Event Name:	Ticket Closing
Rule Tags:

Custom Constraint List (3):

Execute Rule if:

Matches

{TICKET STATE:InvA}	Is Null
[=TN('{ORDER COUNT}')]	Greater	0
Ticket Type Name	Equals	Ticket

Actions (2):

INUM Update Ticket State

Constraint: (none)

iNumName:	InvA
iNumValue:	{SETTING:INUMinvA}

INUM Update Ticket Tag

Constraint: (none)

iNumName:	InvA
iNumValue:	{SETTING:INUMinvA}

---

Invoice B

INUM Update Invoice Number B [Before Ticket Closing] (Rule)

Rule Name:	INUM Update Invoice Number B
Event Name:	Before Ticket Closing
Rule Tags:

Custom Constraint List (3):

Execute Rule if:

Matches

{TICKET STATE:InvB}	Is Null
[=TN('{ORDER COUNT}')]	Greater	0
Ticket Type Name	Equals	Ticket2

Actions (1):

INUM Update Setting

Constraint: (none)

settingName:	INUMinvB
settingValue:	{CALL:inum.upd('B')}

---

INUM Apply Invoice Number B [Ticket Closing] (Rule)

Rule Name:	INUM Apply Invoice Number B
Event Name:	Ticket Closing
Rule Tags:

Custom Constraint List (3):

Execute Rule if:

Matches

{TICKET STATE:InvB}	Is Null
[=TN('{ORDER COUNT}')]	Greater	0
Ticket Type Name	Equals	Ticket2

Actions (2):

INUM Update Ticket State

Constraint: (none)

iNumName:	InvB
iNumValue:	{SETTING:INUMinvB}

INUM Update Ticket Tag

Constraint: (none)

iNumName:	InvB
iNumValue:	{SETTING:INUMinvB}

---

[QMcKay]

Printer Template Tags

…

[QMcKay]

Printer Template Tags

We can use the Ticket State and/or the Ticket Tag when we want to show the Invoice Number on the Ticket Print.

{TICKET STATE:InvA}
{TICKET TAG:InvA}

{TICKET STATE:InvB}
{TICKET TAG:InvB}

If we use [square brackets] around the Tags, then the Tags will only print when they contain a value…

[LAYOUT]
<J00>{TICKET DATE} {TICKET TIME}|Server: {USER NAME}  #{TICKET NO}
[<J00>A# {TICKET STATE:InvA}|A# {TICKET TAG:InvA}]
[<J00>B# {TICKET STATE:InvB}|B# {TICKET TAG:InvB}]

[QMcKay]

DB Tools

Contains all Automation, prefixed with “INUM”.

INUM_InvoiceNumber.zip (2.3 KB)

[QMcKay]

Invoice Search

Because the Invoice Number is stored as a Ticket State and/or Ticket Tag, it might be difficult to locate them at a later time, because the Ticket Explorer does not display States or Tags. So here is a mechanism we can use to Search for and Display Tickets based on the Invoice Number stored in the Ticket State/Tag.

Flow

---

Automation Command

INUM Ticket Search [no Category] (Automation Command)

Name:	INUM Ticket Search
Category:
Button Header:	Search Invoice
Color:	#FF6E8E2C
Font Size:	26
Confirmation:	None

Values (0): (none)

Navigation Settings

Symbol:	
Image:
Auto Refresh:	0
Tile Cache:	0
Navigation Module:
Nav Module Parameter:

Template:

Mappings

Terminal	User Role	Department	Ticket Type	Enabled States	Visible States	Visibility
*	*	*	*	*	*	Display on Navigation

---

Actions

INUM Ask Question [Ask Question] (Action)

Action Name:	INUM Ask Question
Action Type:	Ask Question

Parameters:

Question:	[:question]
Buttons:	[:buttons]
Description:
Automation Command Name:	[:AMCname]
Execute Command In Background:	False
Background Color:
Transparent Color:
Inactivity Command Name:
Inactivity Timeout Seconds:
Execute Inactivity Command In Background:

---

INUM ExecAMC [Execute Automation Command] (Action)

Action Name:	INUM ExecAMC
Action Type:	Execute Automation Command

Parameters:

Automation Command Name:	[:AMCname]
Command Value:	[:AMCvalue]
Background:	False
Delay:	0

---

INUM Load Ticket [Load Ticket] (Action)

Action Name:	INUM Load Ticket
Action Type:	Load Ticket

Parameters:

Ticket Id:
Ticket Number:
Ticket Uid:
Tag Name:	[:tagName]
Tag Value:	[:tagValue]
State Name:	[:stateName]
State:	[:stateValue]
Entity Type Name:
Entity Name:

---

Rules

INUM Ticket Search [Automation Command Executed] (Rule)

Rule Name:	INUM Ticket Search
Event Name:	Automation Command Executed
Rule Tags:

Custom Constraint List (1):

Execute Rule if:

Matches

Automation Command Name

Equals

INUM Ticket Search

Actions (1):

INUM Ask Question

Constraint: (none)

question:	Choose Invoice Type
buttons:	Type A=A,Type B=B
AMCname:	INUM Ticket Load

---

INUM Ticket Load [Automation Command Executed] (Rule)

Rule Name:	INUM Ticket Load
Event Name:	Automation Command Executed
Rule Tags:

Custom Constraint List (1):

Execute Rule if:

Matches

Automation Command Name

Equals

INUM Ticket Load

Actions (2):

INUM Load Ticket

Constraint: (none)

tagName:	Inv[:CommandValue]
tagValue:	[?Invoice Number;;;OCN]
stateName:	Inv[:CommandValue]
stateValue:	[?Invoice Number;;;OCN]

INUM ExecAMC

Constraint: (none)

AMCname:	INUM Ticket Display
AMCvalue:	{TICKET ID}

---

INUM Ticket Display [Automation Command Executed] (Rule)

Rule Name:	INUM Ticket Display
Event Name:	Automation Command Executed
Rule Tags:

Custom Constraint List (1):

Execute Rule if:

Matches

Automation Command Name

Equals

INUM Ticket Display

Actions (2):

MSG TEST

Constraint: [:CommandValue] <= 0

MessageToDisplay:

No Ticket Found!

Display Ticket

Constraint: [:CommandValue] > 0

TicketId:

[:CommandValue]

---

TIP: The MSG TEST Action shown above is a Show Message Action using [:MessageToDisplay] as the Message parameter.

TIP: The Display Ticket Action shown above is a Display Ticket Action using [:TicketId] as the Ticket Id parameter.

[QMcKay]

… reserved for more updates …

[TCSGuernsey]

Hi. Is this still working three years later? I have followed all of the above but am unable to get the InvA number to print on my tickets… the rest works great

Anything that might need to be altered for the most recent version?

[JTRTech]

Can see at a glance anything that would effect it. Likely a mistake in your automation but cant offer any sigestions without seeing your implementation.

[TCSGuernsey]

Thanks for the reply. I will go back and double check everything and if no luck I will post examples

Cheers!

[Treswar]

Hi,
I’m new in sambapos and is setting up my first sambapos unit.
Is this tutorial on creating own invoice number still valid for version 5.5.8.
I was about to go live using sambapos when I realize there’s a lot of 0 value on tickets that are move or merge. This will become big issue when. We get tax audited, they will said it can be a potential fraud on that ticket.

I see the tutorial is very advance and apply for 2 ticket type and using serial + number with limits for the invoice number . When my system is a very basic one. I just need the number sequence without skipping from the day the sambapos alive until forever we use it, so no need to limit it.

Can anyone help on how to do it the simplest way? As I don’t really understand the scripts command, I understand a bit on the code flow but don’t know the exact codes or tags to use.

Thanks before

[Posflow]

So what is your expected result? Can you write some samples?

[Treswar]

I’m hoping that the ticket number can always in sequence even though we merge the ticket, there’s no ticket with 0 value even if we merge ticket or void all item of the ticket. Cause the 0 value will become a big issue during tax audit.

Thanks

[Jesse]

How is that possible? It moves items to new ticket… number is generated in sequence… old ticket is old number… there is no way to logically do that. Also for tax regulators you can add Ticket Logs to show moving of items to new tickets.

Example you start ticket 1. THen you do ticket 2, then 3 then 4. Then you decide you want t move items from ticket 2 to ticket 4. How can you ignore ticket 2? It must stay with 0 balance or the sequence is wrong.

[JTRTech]

As jessie says, surely if audit is that speculitave on fraud a lower ticket number date stamped after numbers following it will scream fraud just as much if in that mindset.

Perhaps you should look at stamping a sequenced number via ticket tag at time of final payment where balance greater than 0, that should give a sequence of non 0 values.

[Bob_be]

I’m curious how other POS’s handles this situation? (It’s not the first time this topic has been mentioned)

I believe something could be built where all the items on an original ticket could be voided (or some other similar state) and copied to a new or existing ticket. Also updating the original ticket with a ticket tag of the ticket number the ticket was transfer to…sorta like a paper trail. Maybe also creating a ticket tag on the new ticket of the old/original ticket number.

Again, I’m curious how other POS’s handles this situation.