Jump to content
  • 0

Project Auto Numbering


JenKri

Question

I am trying to create a project auto number with a twist. Currently our numbering is a manual 7 digit entry using a 2 digit start representing a chapter(ie. 88),   followed by 2 digits representing the year (ie. 21) followed by 3 digits which is the project number. I would like the project number to be auto generated so there are no duplicates and the person entering doesn't need to look up the last number.

My thoughts were to have the following columns in the projects table

1. Chapter Number record (select option)

2. Year record (default to current year if possible)

3. Project Number - This however is the part I am stuck on, I would like this as an auto number but I would like the auto number based on the chapter & Year ie: 88-21-001 then when it is asked to create a new project number it would set the project number to 002 so it would come up 88-21-002 (this would essentially be a combined field). But if it is going under another chapter their first project would come up 77-21-001 and then when they add another it would default the project number to 002. So essentially I want it to look at Chapter number, then Year Number then say the next project would be 1 greater.

 

I hope that makes sense

Thank you for your time

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hello @JenKri,

I think this Forum post suits better for your workflow https://forums.caspio.com/topic/19337-multiple-customers-unique-ticket-number-starting-at-000/

You may check the steps I described in that Forum post. 

And if to modify the Trigger and Formula for your case:

1) For example, I have the table like this:

D4eMKwl.png

2) We are going to insert the Chapter_Number and Year. The Project_Number will be populated with the following Trigger:

e45ckjS.png

3) Formula (the ProjectNumberFinal field) is the following:

CASE 

WHEN [@field:Project_Number] < 10 
THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '00' + CAST([@field:Project_Number] as nvarchar)

WHEN [@field:Project_Number]  >= 10 AND [@field:Project_Number]  < 100 
THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '0' + CAST([@field:Project_Number] as nvarchar)

WHEN [@field:Project_Number]  >= 100 AND [@field:Project_Number]  < 999
THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + CAST([@field:Project_Number] as nvarchar)

END

4) The result:

ND8HrHy.png

 

 

Link to comment
Share on other sites

  • 0

Hello @JenKri,

Another possible option is to use the Calculated Value in the Submission form if you add new records to the table through the Submission form.

For example, you need to have the Chapter_Number field and the Year field in the form to populate them with values.

Then the Project_Number field can be set to the Calculated value:

1UwS7lZ.png

Replace the field names with your local field names.

CASE WHEN 

(SELECT MAX(Project_Number) FROM Projects_ WHERE Year = target.[@field:Year] AND Chapter_Number = target.[@field:Chapter_Number]) IS NULL

THEN 1

ELSE 

(SELECT MAX(Project_Number) FROM Projects_ WHERE Year = target.[@field:Year] AND Chapter_Number = target.[@field:Chapter_Number]) + 1

END

The Formula in the table to calculate the final project number is still relevant (please see my post above).

 

Link to comment
Share on other sites

  • 0

I have a version of this solution implemented (see attachment) and it works great. It is a counter FROM (TockeOd) TO (TockeDo) for different projects (instead of clients as in the example above). If the field 'TockeOd' is empty the counter starts at '101' and if not, it will add the value from the counter TO (TockeDo)+1 to the counter FROM (TockeOd). It works great, thank to everyone contributing to this thread!

Here is my question: I need to introduce an IF...THEN...ELSE situation. IF the field 'TRIGGER' has value '3', then the above solution is a go and it inputs the calculated value in the field 'TockeOd'. However (ELSE?), if the 'TRIGGER' value is '4', then the field 'TockeOd' must remain empty. I know that this is possible, but I just can't figure it out. Any ideas?

Best

Benjamin

Trigger.jpg

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...
×
×
  • Create New...