Multiple Customers, Unique Ticket Number starting at 000

[Kmiller]

I am building a system that will support multiple clients.  The ticket numbers should be TKT0000001 for each client.  

I am trying to avoid a ticket DB for each client because I do not want to have to maintain different tables and forms for each client when updates happen.  Additionally, I am hoping to allow for a self-signup where a client can sign themselves up for the application, and therefore have a hands off approach.

I am stuck with how to have a customer table that has a number unique to that client starting at 0000001 that only increments when THAT client creates a ticket, and even if I DID find a way to do it, that the ticket numbers would not be unique.

[CoopperBackpack]

Hello @Kmiller,

I have some ideas on how to implement this in the table. Maybe this is helpful.

It is possible to generate the ticket number using the Triggered Action. 

1) Let`s say you have the following table design:

 

2) In case the users insert the new records to the table and add Company_ID and Company_name, Triggered Action can generate the Company_Ticket_ID for each company.

The idea is to update the inserted record and set the Company_Ticket_ID. If the inserted Company_ID does not exist in the Company table, the Company_Ticket_ID is 1. 

And if exists, the Company_Ticket_ID is the max number + 1 (we increments it by one).

3) Then you may add the Formula field to the table to generate the needed  Ticket_ID.

Formula can be like this:

CASE 
WHEN [@field:Company_Ticket_ID] < 10 
THEN 'TKT' + '000000' + CAST([@field:Company_Ticket_ID] as nvarchar)
WHEN [@field:Company_Ticket_ID] >= 10 AND [@field:Company_Ticket_ID] < 100 
THEN 'TKT' + '00000' + CAST([@field:Company_Ticket_ID] as nvarchar)
WHEN  [@field:Company_Ticket_ID]  >= 100 AND  [@field:Company_Ticket_ID] <= 999
THEN 'TKT' + '0000' + CAST([@field:Company_Ticket_ID] as nvarchar)
END

You may refer to this forum post https://forums.caspio.com/topic/14538-formula-field-add-leading-zeros-to-numeric-value

Here is the example of the output:

4) Of course, the table cannot store the same values in the Unique field.

To prevent duplicates, so-called composite key can be helpful. Here is the tutorial video: https://www.youtube.com/watch?v=TvNfQDZysDQ&t

In your example, you may add the second formula field to the required table that concatenates Company_ID and Ticket_ID and set that field as unique. 

Feel free to update this thread if you have any questions.