Check existing element within query before inserting into database

Hi Team,

I am trying to figure out how to validate if a specific database record item exists within a query set under the same server action before inserting it into the database.

Any idea on how to proceed ? :slight_smile:

Thank you!

Hello,
What are you trying to achieve - what should happen if it exists and what if doesn’t?

Hi Teodor,

Under the validation I have an insert action. So if it exists the insert action will stop.
I following this Check if a Database Record Already Exists Before Inserting a New Entry and the only difference is the validation is going to be checked within a query.

Thank you!

You can’t use the validation with a query like that. The validation checks your whole table.

With a query you can use a condition.

  1. Add a query, filter it by the value you need to check
  2. Add a condition step, use the {{query_name}} as an expression for it
  3. Add what needs to happen if the condition is true, i.e. when the filtered query returns a result in the THEN step
  4. Add what needs to happen if the condition is false, i.e. when the filtered query does not return a result in the ELSE step

Thanks for your reply Teodor.
I understand the concept using the condition. I am still confused on how to validate the insert since a lookup process will run to check if a specific value (input via a form) exist within the query.
Thank you!

The validation step cannot run on a query, it checks the whole table.

Hi I will briefly describe how I manage this.

SERVER CONNECT
Before inserting into database I create a query that checks if any data already exists.
After the query I create a repeat region and inside ther I set a condition that says if query.length>0 then
I set a response status = 800 (random number that I created can be anything)

APPCONNECT
In the appconnect if I get the status==800 I display a modal that says ‘Data already exists’

SERVER CONNECT
Again Inside the repeat region previously created
If the query.length<1
the I insert the data

Hope it helped you understand the concept.

Thank you!

1 Like

@t11’s idea is good, if you want to achieve this. Just an addition, the status is good to be the HTTP Status, not just a random number :slight_smile:

Yeah this is true but even if you set a number that you know what it means for you, you can display any message you want. So for example just like in my example I know that if I return an (let’s say 800) I know that for me this means ‘data already exists’.

This might not be the most appropriate approach but it works and gives flexibility.

Thank you!

1 Like

I think I need to be a bit more hehe :slight_smile:

I am trying to proceed with this way:

  1. Create an action file
  2. Add a database connection
  3. Add a query with the parameter to filter
  4. Add a condition with the query set in step 3. in the properties
  5. Add the database insert in the Then
  6. Nothing in the Else
  7. Create a database insert form
  8. In app connect, create a serverconnect and link the parameter to filter in step 3 to one of the field in the insert form.

Not sure if in the right direction, but I am somehow stacked here…
In other words, the check depends on the value added in the new insert form.
Any suggestion? Any help? :slight_smile:

You filter the query by the POST variable you want to check from your form.

It must be in the ELSE if you want to insert the record if the record is NOT in the database…
THEN will run if the {{query}} returns a value, i…e. if the record exists.

See my explanation above

1 Like

Wow! Thanks for your quick reply Teodor!
Let me correct this and get back to you asap.

Hi guys,

I have this working now with the corrections brought by Teodor.
Now I am challenging the validation. I need to apply the validation to the form before it submits the data insert to the database.

Thanks

@Teodor, Team,

Any advise on how to apply the validation to the form? :slight_smile:
Your help will be greatly appreciated!

Custom query example

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
+----+--------+-----------+------+

Hello @Moustapha
You can apply a dynamic attribute -> class to your form input, and use the status code returned by the server action as a condition for it.
So you can apply a class .is-invalid when the status == 400 (just replace 400 with the status returned by your set response step).

Thanks for your replies guys!

@s.alpaslan
Actually my insert action is working as expected. If the entry already exists, it does not just add the new entry. I want to use a validation rule instead so that when the submit button is hit it says that something is wrong…

All right @Teodor, let me explore that and get back to you.

Thanks!

1 Like