unique combination of fields in one library

[Tim Wilkinson]

Hi all,

I found a trigger on a previous post which successfully prevents the record from saving when a field other than the name is not unique (code below). I have implemented this in Memento desktop and synced it with the cloud. it works.

The next step is to combine 2 or more fields so the combination is unique. This is to ensure no duplicated entries. Similar to a composite primary key in SQL.

the outcome of the trigger would be to prevent a save if the combination already exists in the library.

Is there anyone out there who has completed this and can share the Javascript or have any ideas on how to implement it?

This is different from setting more than one field as Name, as Name is given to another field in the library which is a Link to Entry in another library. 

Setting more than one field as Name in a library is fine, BUT when using Link to entry from another library it concatenates all of the Names into the Link to entry field. This is not what I am after.

Thanks in advance.

Tim

code for unique entry

var myField = entry().field("myField"); // Value of myField

var entries = lib().entries(); // Array containing all the entries in the library

var unique = true; // Presuming, initially

for (var ent = 0; ent < entries.length; ent++) { // Loop through all entries

    if (entries[ent].field("myField") === myField) // If there is ever a match,

        unique = false; // Remember it

}

if (!unique) { // If not unique,

    message("myField is not unique. Try again.");  // Tell the user

    cancel(); // Disallow the save

}

[Tim Wilkinson]

This would be more like a unique index in sql, not a composite primary key.

[Tim Wilkinson]

I have tried this which executes successfully, but still allows to save the entry.

I just added another field into the myField.

Should i create an array for the var entries of the combination i want to make unique, so it can match myField?

I realise I could concatenate the combination of fields i want to make unique into a new field, but wanted avoid this solution if possible as it adds to the number of the fields in the table and looks messy.

var myField = entry().field("fm_date", "station");              // Value of myField

var entries = lib().entries();                                                  // Array containing all the entries in the library

var unique = true; // Presuming, initially

for (var ent = 0; ent < entries.length; ent++) {                     // Loop through all entries

    if (entries[ent].field("fm_date", "station") === myField)   // If there is ever a match,

[Tim Wilkinson]

I've managed to create an array of the entry().field() which are being entered in the form.

var myField = entry().field("fm_date"); // Value of myField

var myField1 = entry().field("pH");

var myField2 = [myField,myField1];      //new array of combined fields to check against

I now need to create an array of the same fields within the loop from entries already in the library and check against the myField2.

any ideas on how to create an array in the loop (bottom section of code above)?

thanks

[Bill Crews]

var myFields = [];

myFields.push(entry().field("fm_date"));

myFields.push(entry().field("pH"));

Your last line would also work, though. If you're ever in a loop, adding general stuff to the array, push() is the way to go. Since I'm only an intermediate JavaScript developer (not expert), I keep browser tabs open to certain pages of MDN JavaScript, like the pages for Date objects and the one for Arrays, like this: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array

I recommend spending some time, if you have it, just soaking up all those methods on those pages for Arrays, and also for Dates, which can be tricky, or Strings or Numbers or other data types. For Arrays, split() and push() are in there. Strings would be helpful too, for what you're doing: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String

[Tim Wilkinson]

Thanks for the links. I will check them out. I have spent my time learning python, so JS is new.

the updated script almost works. It executes successfully, but fails to pick up identical combinations.

hopefully, only a few small tweaks and it should work. if anyone out there has a solution please contribute.

var myFields = [];

myFields.push(entry().field("fm_date"));  // Value of myField

myFields.push(entry().field("pH"));       // Value of myField

var entries = lib().entries(); // Array containing all the entries in the library

var myentries = [];                                                                //create an empty list 

var unique = true;                                                                 // Presuming, initially

for (var ent = 0; ent < entries.length; ent++) {                      // Loop through all entries

     myentries.push(entries[ent].field("fm_date"));                 // add "fm_date"to array

     myentries.push(entries[ent].field("pH"));                         // add "pH" to array

   if (myentries === myFields)                                             // If there is ever a match,

        unique = false ;                                                           // Remember it  

    else (myentries =[]);                                                        //if there is no match clear the list                   

}

if (!unique) {                                                                        // If not unique,

    message("Combination is not unique. Try again.");       // Tell the user

[Tim Wilkinson]

All,

this is the completed script for enforcing unique combinations of multiple fields

var e = entry();

var mynewField1 = e.field("myField1");

var mynewField2 = e.field("myField2");

var entries = lib().entries();

var unique = true;

for (var x in entries) {

   if ( (entries[x].field("myField1") != newnewField1)    // Assumes TDS is a number field, like integer or real

      || (entries[x].field("myField2") != newnewField2) ) ;              // Assumes pH is a number field, like integer or real

  else                                                                     //when false move to else and execute

     (unique = false);                                               // Remember it

     break;                                                               // Once it's false, no need to continue looping  

}                                     

if (!unique) {                                                                  // If not unique,

    message("Combination is not unique. Try again.");  // Tell the user

    cancel();                                                                    // Disallow the save

}

if a date/time field is used the getTime() is needed. see below

var e = entry();

var newDate/TimeValue = e.field("mydate/time").getTime;

var mynewField = e.field("myField");

var entries = lib().entries();

var unique = true;

for (var x in entries) {

   if ( (entries[x].field("mydate/time").getTime != newDate/TimeValue)      //Dates always work unlike any other object

      || (entries[x].field("myField") != mynewField) ) ;                                // Assumes pH is a number field, like integer or real

  else                                                                                                       //when false move to else and execute

     (unique = false);                                                                                 // Remember it

     break;                                                                                                  // Once it's false, no need to continue looping