Conditional Autonumber

[srufini]

Hi all,

 

I am trying to achieve the current feature.

 

 

I have a table of Inspection Reports, each one belong to a certain project ( dropdown selectable table or Projects).

 

I have the requirement to create a unique reference number for each of this report that is like:

 

IR-PRJ-xxx

 

where the prefix IR is always the same, and PRJ is the code name of the project ( that is present in the relative field of the selected project: this is easy to get with a cascading dropdown and a virtual number), while xxx is a running number that is unique only within the same project.

 

So somehow need to be implemented an algorithm that check what is the latest number with that specific project and increase by one, and finally creates the concatenate content in the field "reference".

 

I have no idea on how to implement. Should I use SQL? If so how?

 

Thanks a lot!

 

Sergio

[Jan]

Hi Sergio,

 

Is the table exist and you wants to edit or add records?

Or do you want to add unique reference numbers to new records?

[srufini]

Hi Jan,

 

I want to add records. In the moment I generate a new record, based on the project, it should generate an automatic reference. Therefore I imagine I can open the created record and edit the other fields.

 

Unfortunately the automatic field is very limitated and would be a good opportunity to use in a more complex way to generate any kind of referencing of the record.

 

Is there any way to implement with the current Caspio release?

 

 

Thanks

 

Sergio

[srufini]

not possible?

[Jan]

Hi Sergio,

 

I am sorry for delayed response.

 

You can try doing the following steps:

 

For example, names of Fields:

Id - the name of "ID" field;

"Project" - the field with name of "Project", if you use this field;

"UniqueReference" - the field for Unique Reference.

 

1) Create a Single Update Form:

1a) On the "Record Identification" step, in the field "Parameter name:" enter like:

[@InsertRecordID]

1b) On the "Select Fields" step, select your "Project" and "UniqueReference" field (if "Project" is used);

1c) On the "Configure Fields" step

1c*) Set "TextField" Form element for "Project" and "UniqueReference" fields;

1c**) Add a Header&Footer element, select the Header element, click the "Source' button" enter the following code:

 

<div align="center"> <p>Processing...</p> <img src="images/progress_bar.gif" alt="Progress Bar" width="200" height="15" /> </div>

 

1c***) Select the Footer, click the "Source" button and enter the code like following:

 

<SCRIPT LANGUAGE="JavaScript">
var position1 = document.getElementById("EditRecordProject").value;
var position2 = "[@InsertRecordID]";

var allpositions = "IR-" + position1 + "-" + position2;

document.getElementById("EditRecordUniqueReference").value = allpositions;

if(document.getElementById("caspioform")) {
document.getElementById("caspioform").style.display = 'none';
setTimeout('document.forms["caspioform"].submit()',1000); }
</SCRIPT>

 

If you do not use the "Project" field, you can use the following code:

 

<SCRIPT LANGUAGE="JavaScript">
var position2 = "[@InsertRecordID]";

 

var allpositions = "IR-ProjectName-" + position2;

document.getElementById("EditRecordUniqueReference").value = allpositions;

if(document.getElementById("caspioform")) {
document.getElementById("caspioform").style.display = 'none';
setTimeout('document.forms["caspioform"].submit()',1000); }
</SCRIPT>

 

I have marked names of fields. Please make sure that you enter names of your fields.

 

2) Edit your Submission Form.

2a) On the "Select Fields" step, check the "On exit, pass AutoNumber ID field as parameter" checkbox. It is under the "Available fields" section.

2b) On the "Destination and Emails" step, select

- if you use Embed Deployment: "Go to a new page" and enter the URL of the page where the DataPage from step 1 is deployed;

- if you use iFrame or URL Deployment: "Go to a new DataPage" and select your DataPage.

 

Now, when a new record is added, the Autonumber is passed as parameter to Single Update Form. On this form Unique Reference is created, entered to the field, and the form is autosubmitted. A user sees "Process" instead of the Single Update Form.

 

I hope, it helps.

 

Fell free to ask, if anything is unclear.