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.