Jump to content
  • 0

Multiple Customers, Unique Ticket Number starting at 000


Kmiller
 Share

Question

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.

Ticket_Links.png

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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:

8BvaEeb.png

 

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.

jQkrNBH.png

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:

IpdJ5Cb.png

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...