How to prevent duplicate entries into db table when storing API data

[thines]

Hi, i have been working with an API to consume data and store in a db table, but i wanted not to store duplicate entries when second or more call goes to the API and store the data.

I did tried to put querying the table and condition to check if data exist and then matching a unique column and then based on the matching created the update action but it does not worked.

ex: if table 1 has column: id, code, rate where i store data from API, if I’m requesting second time or more, API data structure: {$key, $value} $key is equivalent to ‘code’

i did like this, but no luck…

[thines]

Could anyone has an idea? pls help

[brad]

Maybe this documentation will help you get on track.

Check if a Database Record Already Exists Before Inserting a New Entry

Check the data in your Database before inserting possible duplicate data Using server-side data validation from server connect you can check if a record already exists in the database. This is useful when new users register to your site so you can check if the email is already used and if it is display an error message, while at the same time stopping the server insert action from inserting duplicate data.   Step 1: Select your Insert Action(1) and right click the step before the insert recor…

[Teodor]

First of all, the condition here:

should be:

{{queryApiCurrencyRatesDb.length() == 0}}

Then, explain what you mean by:

@thines

but it does not worked.

What did not work exactly?
When explaining the issues you are having explain in details what are you expecting to happen and what actually happens (what results do you see).

“It does not work” tells us nothing. We can’t help unless you provide more details.

[thines]

Hi Teodor, thanks for the reply, i expected generic approach like any of the case when consuming API data to store in a table, mostly people try to prevent duplication by checking the if the data exist already based on a unique key or value. details explanation is not point here. the way i was understood on this conditions, are the main issue i think. As you can see first steps: has API Action to get the data via api and also has a database query to the table where i’m going to store the API data. I have started with condition to check wether the table is empty then insert action from the repeat of API result if not repeating the database record and matching the ‘code’ and based on that run update action. which seems logical, but i did not get any data stored inside the table.

Please just tell me or show in generic way, if i wanted to store the data from API without duplication. into a table. Hope you understand… it does not worked means that my steps or the way i approached is not worked. which mean no data in the table also no errors either. when testing the action in the browser i did got the data from api but the condition are seems like not working. i have not tried the way you wrote, checking on the length. will do next.

[Teodor]

Generic steps - filter a query with the result query, condition to check if the query returns a value - if it returns, don’t insert, if it does not return results insert.
Explained you what the condition needs to be in my last reply.

[thines]

which mean the way i did was correct but condition is wrong? i did other way around than you saying, both works if condition is correct ?

[thines]

it worked with this condition {{queryApiCurrencyRatesDb.length() == 0}} but i could not find length() , i wrote manually.

[Teodor]

@thines

could not find length()

You can use count formatter instead.

[thines]

in my screenshot about i have a repeat for the database query to update the rate by api rate based on matching the code (which is $key in api repeate). is this correct?

queryApiCurrencyRatesDb[0].api_currency_code == $key

[Teodor]

I don’t know, I don’t fully understand your logic and what are you doing there with that condition.

[thines]

what i’m trying to do is match a database field api_currency_code with repeated api data key to find out wether the database has existing value and based on that execute database update action, if you look into the screenshot that i sent earlier for reference. in this way i’m trying to prevent duplicate record. but i have no idea is this correct? or do i need to use validator instead?

[Teodor]

Well all you need to do is to add the following steps:

• API Action
• repeat (API Action)
  – database query (filtered by your API value)
  – condition (query returns results?)
  – then -> update record step
  – else -> insert record step

[thines]

@Teodor

API value

Great it worked!