Hello, I’m just wandering if is possible to have unique key in table created by 2 (or more) unique fields. It is nice that Knack is watching unicity on every field signed as unique, but I need to combination will be unique.
For example:
Object is Car,
having fields:
color
owner
user - unique
VIN (engine code) - unique
I would like to have more records for one car like this:
user A using car 1
user B using car 2
user A using car 2
Is possible to do that somehow?
I can use text formula field, which can combine two unique fileds. But formula field cannot be unique (so I cannot rely on automatical record check before insertion).
solution proposal:
I am thinking about some mapping object (CAR_USING) with unique field in type of string or integer in which combination of USER+VIN can be encoded.
But this will enlarge the database size a lot…
solution proposal:
On every insert/update to CAR, check uniqueness by JS.
But I found this not robust enough, JS sometimes not run/finish good.
What do you think…? I would appreciate any suggestions.
We have a feature we call Connections that enables you to build the proper relationship between Objects. In the example you provided, you would require a one-to-many relationship between the Car Object (one) and Owners object, for example (many).
The VIN can serve as the unique field assigned from Car to many separate Owner records.
Functionally, this would allow you to add car records and view a child page of owner records for the same vehicle similar to the example in this guide.
If I understand correctly, you are looking for a way to prevent records being created with duplicate values - but these values are from more that one field?
As you probably have realised this is not a Knack native feature at the moment but I have devised a way of doing this using a bit of Javascript code in Knack and an Integromat Scenario - you can read about it on my blog:
In terms of your solutions you list, assuming I understand, then I think option 1 is the place to start - then use this validation with it.
Hi, not much … We have quite complex knack app and for my example - I still need to add records directly to one object (Car). Just disable unique key and check it some other way on combination of fields.
And I would need to change lot of thinks on desing change. But I agree that design is wrong at this point, I just must to live with…
Thank you Julian! I like this! And I’ll probably use it…
I’ll just try to do JS more reusable to have more code for all form checks if possible. I need to test it but I believe I can do it like this:
1] have one function/header for each form i have ro check
- $(document).on(‘knack-view-render.view_332’, function (event, view, data)
2] have same body and just passing different view_id and field_id
… We have quite complex knack app and for my example - I still need to add records directly to one object (Car). Just disable unique key and check it some other way on combination of fields.