How I can prevent duplicate records being added?
Hi. I'm a new user on my first project and have set up a simple database. I need to find out how I can prevent duplicate records from being entered in a table. For example, if first-name and last-name and license-number are identical to an existing record, do not permit the record to be added. Thank you.
37 replies
-
Hi,.
you can use the following formula on the "Trigger after update" at the field you want to check:
let myField := Field;
if cnt(select Table where Field = myField) > 1 then
alert("This content does already exist, please enter another one!");
Field := null
endBest, Jörg
-
Thank you Jörg, but I'm unclear how that would be coded where the contents of 3 separate fields need to be compared eg field1, field2 and field3, where the new record may match on any 2 of those field values but not all 3?
-
Hi Peter,
you can connect the condicions after the if with an and like that:
let myField1 := Field1;
let myField2 := Field2;
let myField3 := Field3;
if cnt(select Table where Field1 = myField1) > 1 and select Table where Field2 = myField2) > 1 and select Table where Field3 = myField3) > 1 then
alert("This content does already exist, please enter another one!")Best, Jörg
-
Thank you Jörg - appreciated!
Peter.
-
Jorg
I tried the above but it only appears to work on the app version on on the online version. Very strange????
Any ideas?
Thanks in advance
Tim
-
'on' = 'not'
-
Hi Tim,
Yes you are right. The alert does only work in the App for now. We have it already on our change request list and hope to release the feature also for the browser version soon.
Thank you very much for your patience.
Kind regards, Jörg
-
Hi, I have a table with a field date and I want to keep this unique; I tried to use the above formula but it does nothing; can u pls help me to use this formula with dates field?
-
This code works:
let myDate := Date;
if cnt(select TableName where Date = myDate) > 1 then
alert("This Date does already exist, please enter another one!");
Date := null
endMaybe some typo;
Nick
-
Hi Nick,
I did include the code you sent me (attached screenshots) and I still can enter same date on the next fields of my table without any warning. Any hint?
-
I work with Mac app. Maybe web app is different?
-
I use the web app; seems Ninox does not support this option on web
-
Hi,
That is right: The alert function does only work on buttons in the web app at the moment, but we are already working on that. Thank you for your patience.
Best, Jörg
-
Can dialog() be used instead of alert()?
i have been playing with dialog(), and it seems to work on the web app.
-
I guess not. I forgot dialog() is only used with buttons.
It looks like the if statement is not executed if it has alert() or dialog().
If you take the alert() out, it will not allow you to add identical dates. There will be no warning, but it is better than the if statement not executing.
let myDate := Date;
if cnt(select TableName where Date = myDate) > 1 then
Date := null
end -
Hi,
You are right, also this function does only work on buttons in the web app at the moment. We are already working on that too.
Best, Jörg
-
This concerns me also. It would be helpful to have a "unique with" field attribute available as a feature rather than needing to hard code the function. EG Field-A in conjunction with Field-B (and more) must be unique. Scenario (Family-name is UNIQUE WITH Given-name and Date-of-birth). Means that where those conditions all are true then the entry is rejected.
-
Unique field is important on web app; hope Ninox will solve this soon
-
Yes I am needing the same solution. Making Inventory Table... Do not want duplicate part #s
-
Hi,
Ninox provides the unique field "Id" which is automatically created by Ninox and can not be canged.
Kind regards, Jörg
-
Hi all,
update: suggested script block (...)
let myField := Field;
if cnt(select Table where Field = myField) > 1 then
alert("This content does already exist, please enter another one!");
Field := null
end
(...) the GUI alert
* is correctly displayed on macOS app
* is not displayed on web app (Win10, FireFox 68.2.0esr (64-bit).
Pls inform.
wolf
-
@ Wolfgang, where did you put your script? In the Trigger after update of the 'Field' is the right place.
try using =2 instead of >1 in the script.
this works for me in the webapp windows 10 chrome.Steven.
-
Hi Wolfgang - We are going to be going over this very topic in our Ninox Learning Lab at 12:00 Noon EST. You can register for the FREE event here >>> https://www.nioxus.com/webinars
All attendees will receive a free Global Function that can be copied and pasted right into any database that will enable you to test for and prevent duplicates.
I hope to see you there.
Andy.
-
The script works kind of ok for me in (on the Mac) in that it does prevent me from adding a duplicate. However, it adds an empty record now. Is there a way to programmatically (i.e. in the script) remove it or do I have to klick on the waste bin icon and then confirm deletion of the empty record?
-
To delete it in the script you can use: delete this
Steven
Content aside
- 2 yrs agoLast active
- 37Replies
- 12036Views