How do I insert into a table if an entry does not already exist

Questions and Help
1

Hi,

I'm trying to put together a snippet that will add an entry into a table in Data Blaze if it does not already exist, but will not add the entry if it does exist and will give me a notification to say so.

I've created the below snippet, but it's giving me "Row Already Exists" notification no matter if the row already exists or not when I press the "Add record" button, and it adds the entry all the time when I hit Insert.

Can anyone help with this one?

[snippet]
{note}
Name:
{formtext: name=Name}{button: existing_row_id = catch({dbselect: SELECT Name FROM HC KB Worknotes WHERE Name=@name; space=7cez1rFxLQMzc7RWj26t9v}, "NONE")
if existing_row_id <> "NONE"
notify("Row already exists")
return
endif
{dbinsert: INSERT INTO HC KBs SET name=@name, kb title=@kb title, kb overview=@kb overview, worknote=@worknote; space=7cez1rFxLQMzc7RWj26t9v; autoaddfields=yes; instant=yes}
; label=Add record}

Details:
{formparagraph: name=KB title; cols=80; rows=1}
{formparagraph: name=KB overview; cols=80; rows=2}
{formparagraph: name=Worknote; cols=80; rows=6}
{dbinsert: INSERT INTO HC KB Worknotes SET name=@name, worknote=@worknote, kb title=@kb title, kb overview=@kb overview; space=7cez1rFxLQMzc7RWj26t9v; autoaddfields=yes}{endnote}[/snippet]

2

Hi, and welcome to the forum! :slight_smile:

Edit: We have looked into this more and decided this is a bug and we will fix the issue. Thank you for reporting it!

In the meantime, the best way to solve the problem would be to use the multiple=yes setting on the dbselect. This will return multiple matching rows as a list. It will return an empty list if there are no matching rows. You can also use a limit of 1 for performance reasons if you think multiple rows could match but I don't think that is the case here with your query.

You can then do something like the following in your button:

has_row = count({dbselect: ...; multiple=yes}) > 0

Hope this helps!

3

Many thanks for the response, I didn't expect my first time here to be catching a bug! :grinning_face_with_smiling_eyes:

I'll give the workaround a try.