Preventing Duplicates (Uniqueness) on Combined Fields in an Object

Hi there. I am trying to figure out how to prevent a user from entering duplicate records in an object defined by combined unique fields.  For example,

The "join" object is called SHOWS-TALENT:

 - connection to shows

 - connection to talent

The uniqueness I need to create is the combinations of SHOWS & TALENT.  There can be multiple references to the same SHOW in the table as well as multiple references to the same TALENT but there show only be ONE reference to a certain TALENT on a specific SHOW.

Normally in the database I would create a combine foreign key for a join table like this but in Knack that does not appear to be possible.  Any ideas on how I can enforce this uniqueness?  It is becoming a problem because many people are entered the same TALENT two or three times for the same SHOW.

Thanks, Kevin

392738901112

  1. view_32: The add guest form
  2. scene_24: The Guests page (including guests table (view_31) and form (view_32) )
  3. view_31: The guests table
  4. field_52: The phone field in guest object

Yedidiel you  are a genius.

In order to adapt the code snippet to my application, I make you a monument if you could tell me, what are in your case:

1. view_32

2. scene_24

3. view_31

4. field_52

Even better if you add a screenshot!!

I had a similar issue, my app has the following structure:

Events manager (User Role)
-- Guests (User role)

I wanted something that looks simple - allow every events manager to manage his own guest lists, with no duplicate guests (validated by phone). I created a form to create Guest record, where the logged in event manager is automatically connected.

Setting the phone field to unique wasn't working since in this case, if 2 events managers have the same guest, only the first of them will succeed to add him (even though they have access to different records).

Tried with formula fields, form conditions etc - nothing worked.

Finally, the solution came from custom JS (inside Knack app):
--------------------------------------------------

$(document).on('knack-record-create.view_32', function(event, view, record) {

    var headers = { "X-Knack-Application-ID": Knack.app.id,
   "Authorization": Knack.getUserToken(),
   "Content-Type":'application/json'};

   var url = 'https://api.knack.com/v1/pages/scene_24/views/view_31/records';

   // Prepare filters
   var filters = {
     'match': 'and',
     'rules': [
       {
         'field':'field_52',
         'operator':'is',
         'value':record.field_52
       }
     ]
   };

   // Add filters to route
   get_url = url + '?filters=' + encodeURIComponent(JSON.stringify(filters));

   $.ajax({
     url: get_url,
     type: 'GET',
     headers: headers,
     success: function(response) {
         if (response.records.length>1) {
           alert('Guest with this phone already exists');
           $.ajax({
             url: url += '/' + record.id,
             type: 'DELETE',
             headers: headers,
             success: function(response) {
               Knack.views["view_31"].model.fetch(); // refresh the table about the form so the duplicate will be removed
           }
         });
       }
     }
   });

});
--------------------------------------------------

What I did here, is to trigger an event after adding a new record. I then count the number of records that exist with this phone number. Since I am using View-based API, the results include only what the logged in user can see.
So, if I get more then 1 (notice its triggered AFTER the duplicate record creation), I call DELETE endpoint to delete the record that was just created.

I think its pretty strange such a workaround is needed for such a logical task. Record level filtering by users should also filter unique.

 

In addition to checking for duplicates server-side using Integromat or otherwise, you could interrupt form submission and check client-side, too. That means you would incur one API call per submission, but it would save you in Integromat operations. Would still suggest keeping Integromat as a failsafe, though.

372419060772 -Thanks for your Integromat solution, I use it quite a bit and it makes sense.

The only observations I have plus a question are:

a) I presume given a very large data table I would have to be careful running this scenario as it will check all records every time a new record is added for the particular search criteria? I have some tables with several thousand records which would eat though my operations bundle pretty quickly?

b) How do you notify the user that the record they added was a duplicate and that it has been deleted? I know you could add an email module but this process is always "after the event".

It's a shame there isn't any conflict management built into Knack for this type of scenario so that it checks before the record is added to the table. Nice work with the Integromat scenario through 👍😊

Alex,

 

Hopefully this screenshot explains it.  The only thing I haven't included is how to sort the bundles - not sure if it's done on record ID or on date it was created. You'll have to have a play to figure out your logic. You could always add a filter to set that up.

 

Also - in the example below, delete the "0" and replace it with a 1. It's early and I made a mistake with the 0. :D

Hey Mark, I've spent my whole Sunday trying to figure out, without success. Just one last bit of help!

I'm trying to implement the duplicate prevention without utilizing the DataStore.

In the below screenshot you can see my simple Knack Integromat scenario. On the left end, when a record gets created, then, on the center, a filter should be applied. And if in that filter, the "bundle number > 1" condition is met, the record should get deleted (on the right).

My problem is that I dont know how write that condition down in the Filter form. How do you complete the form highlighted in yellow? How do write down in the Filter form "bundle number > 1". 

Alex - I'm sorry, I don't know what Zapier can do. I have found Integromat to be much more useful so haven't bothered with apier.

 

Bobby, I'll try and explain it. First off, are you using Integromat?

When an object is created, it should be unique. I have one with dates, $ and location. When the new object is created I search for an object with the same attributes. If another one with the exact same values is created, a result will show more than one bundle. I have a filter that then deletes the duplicate where the bundle number is >1. That then leaves the original file and deletes the duplicate. I actually think you could probably prevent duplicates without the datastore now that I have gone over this in my head.

372419060772 any tips or tutorials on how to set up the data store and check for duplicates. Google hasn't been very helpful.

What would be the equivalent in Zapier of Integromat's "DataStore" ?

Mark: What you did with Integromat , can be done with Zapier?

I managed to get this to work by using Integromat.

 

When a user joins, a record is stored in a Datastore in Integromat.  The user is then added to whatever group.

 

If they try and join again, a comparison is made with existing records in the Datastore and they are prevented from creating a duplicate/it is deleted.

 

I use it to prevent people submitting multiple applications for a job role.

 

Hope that brightens someone's New Year. :)

I have the same problem.

One Member can join many clubs, but I need to prevent them joining the same club twice.

Hi Knack Team, We are new to Knack and this is the only feature holding us from signing up Knack for our use case. Let us know even if there is a workaround for this. Thanks

 

We need this as well...

That's a bump from me as well...

I am not a programmer and love the flexibility of building that Knack provides...but the lack of being able to prevent duplicates that are based on two different entries is really frustrating -

 

Knack - please note this is pretty crucial when you are trying to create a system that works with people!

One more bump for this.  Running into this problem now, using associated objects and wishing there was a simple solution.

Thanks Dagmar. I’m sure this will help some, but I’m actually using Zapier (API) to insert new records so form Rules don’t seem to be my solution.