unique combination of fields in one library

98 views
Skip to first unread message

Tim Wilkinson

unread,
Aug 28, 2018, 7:49:48 PM8/28/18
to mementodatabase


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

unread,
Aug 28, 2018, 8:05:42 PM8/28/18
to mementodatabase
This would be more like a unique index in sql, not a composite primary key.

Tim Wilkinson

unread,
Aug 28, 2018, 8:49:31 PM8/28/18
to mementodatabase
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

unread,
Aug 29, 2018, 2:23:15 AM8/29/18
to mementodatabase
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

unread,
Aug 29, 2018, 9:04:09 AM8/29/18
to Tim Wilkinson, mementodatabase
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

unread,
Aug 29, 2018, 9:50:14 PM8/29/18
to mementodatabase
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

unread,
Sep 2, 2018, 11:28:03 PM9/2/18
to mementodatabase
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  
Reply all
Reply to author
Forward
0 new messages