Custom ID Numbers

[Doug Bank]

I am building a form to document a collection. I would like to have a field that captures an ID number that will be used to physically mark the item in the collection so that I can easily identify it and look it up in the future. Brendan has told me how to use an auto-incrementing number field for this purpose, but I foresee some issues.

I would like the field to include a prefix and possibly a custom suffix. I figure that I can have a hidden auto incrementing number, and then use a script to concatenate the prefix and suffix to the ID number and then only display the result. However, I don’t know how to create such a script, and even when I do it, I want it to show 5 digits, which the number field doesn’t like.

For example, one possible ID NUmber could be DJB00345-F

Does anything like this exist, or do I need to figure out how to script?
Is there any way to ensure that the numbers will never change and never be duplicated?
What happens if I create a new record by accident and then delete it. Will that ID number be gone and unused forever?

Thanks,
Doug

[Doug Bank]

I figured out how to format a number like I want using a calculation.

CONCAT(“DJB”;FORMAT(test number
; “00000”) ; Suffix
)

I’m still not sure how to ensure that the base ID number never changes once it is assigned and is never duplicated, or what happens to the ID number if I delete a record (which happens more often than I desire because I keep accidentally creating new records when I am trying to edit something else….)

Will the “Calculate one time only” option in the Edit Formula page solve the problem of ensuring the number doesn’t change? If I actually want the suffix to be able to be changed, is there a way to have a similar “calculate once” for the auto-increment function?

[Sam Moffatt]

The Tap Forms built in autoincrement option for numbers shouldn’t duplicate even if you delete a record, the autoincrement count will increase. You could use a calc field to copy it across but in reality if you hide it then it is likely enough. The exception is if you use sync and multiple devices because each device might not be aware of the other and new records on both devices might duplicate an ID.

You can use a form script to override a value of the calculation field if you need to regenerate it later. Almost every field type can be modified by the scripting engine (exception being created date, modified date and modified by device), so realistically nothing is truly read only.

[Gregory martin]

You could check a field for being duplicated by adding this to a script field on your form, and the script is triggered when you press enter on the field you specify in the mainfield_id. Not sure how fast this would be on thousands of records though! You need to change your id for the field your are checking. This code snippet writes out ALREADY EXISTS in the console, otherwise the values of the records, so amend as needed of course

// ***** this needs to be your field ID
var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9';
// ===========

var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();

function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
    // ***************************
	// do not check current record
    // ***************************
	if (records[index].getId()!=thisrecord) {
	
  	   console.log(records[index].getId());
  	   console.log(records[index].getFieldValue(mainfield_id));
  	   console.log(newvalue)
  	   
  	    // check other record values for duplicate
    	if (records[index].getFieldValue(mainfield_id) == newvalue) {
           console.log("Already exists");
        }       
}}};

[Gregory martin]

Here is a more compact version. Sets the script field to 1 if exists or 0 if unique.

var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9'; // change this to your field ID
var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();
function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
	// do not check current record
	if (records[index].getId()!=thisrecord) {	
  	    // check other record values for duplicate
    	if ((unique == 0) && (records[index].getFieldValue(mainfield_id) == newvalue)) {
    	   unique=1;
           console.log("Already exists");
           break;
        }     
        return unique;  
}}};

[Doug Bank]

So these scripts don’t handle the creation of ID numbers, but they keep watch behind the scenes to make sure that the IDs are never accidentally duplicated?

[Author]

Posts