
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.
-
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
-
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 -
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.
-
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
-
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.