Project Auto Numbering

[JenKri]

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

[KlisaN137]

Hi @JenKri

So, you are having a Submission Form, on which the user is selecting the Chapter number, and you want that to be used in making this autonumber which will essentially be unique record ID?

As I see it, the best option is to use Triggered Action to do this, so something like on the following Post:

 

[CoopperBackpack]

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:

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

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:

 

 

[CoopperBackpack]

And if you want the Year field to be auto-populated with the current year(last 2 digits of the year), you may test this Trigger design:

[JenKri]

Thank you, this appears to do exactly what I am looking for. Unfortunately my Caspio plan does not allow me the use of triggers in tables. I am not sure if there is another way. If not I may just be out of luck

[CoopperBackpack]

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:

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).

 

[BenjaminS]

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

[CoopperBackpack]

Hello @BenjaminS,

Do you need to apply this logic when the value of the 'Trigger' field is 3 in the inserted record? 

Which values can be stored in the 'Trigger' field? Are they only 3 and 4? 

If this is needed when Trigger=3 in the inserted record and the possible values are 3 and 4, just add the WHERE clause: