Jump to content
  • 0

Delete or avoid duplicates with triggered action



I have the action below which adds relationship records between Description and Category tables when a user selects to "Add All". What I can't figure out, or find, for the life of me is how to add logic to this that would either skip the join if the record pair already exists or delete duplicates after adding all of the associations. The problem I have is that if some of the associations already exist and a user chooses to "Add All" then it creates duplicates for those that already exists. This causes problems in views and data pages.



Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

If anyone else runs into this, I came up with a solution. It's a little cludgey, but it is functional. I added a calculated field to my table which concatenates the two IDs together, then did a comparison to those values before adding a new record with the triggered action.



Link to comment
Share on other sites

  • 1

Hello @Benades,

I agree that it is easier to prevent submission of the duplicated values than to delete them.

Since this post is about deleting or avoiding duplicates, I would like to share the solution that I used in my account to delete duplicated records. Maybe it can be helpful for someone. 

The main point here is to determine what is a duplicate.

Lets` say this is the initial table:


1) For example, we need to remove all record with the duplicated names.

I used a Task with the following design:


The table after the Task run:


2) For example, we need to remove all record with the duplicated names and emails. 

In this case, the Task is the following:


The table after the Task run:


So, the idea is to have a  some unique field in the table (in my example it is the ID field).
Then in the GROUP BY Statement we need to specify all fields where you want to check the duplicates. 

If the ID field is an Autonumber then using the MAX function we leave the record with the maximum ID value. 

It is also possible to use the MIN function to leave the record with the minimum ID value. 

Link to comment
Share on other sites

  • 0
On 9/17/2020 at 3:24 AM, NiceDuck said:

Normally, I would rather use the not exist operator to check if the record does not exist yet. Just like this.

 I try using "Not in" before but I encounter some errors so I prefer using this more

Better preemptive action than corrective.

Link to comment
Share on other sites

  • 0

I'm trying to do something pretty similar to this.



This involves 2 tables, Equipment_Inspection_Table and Equipment_Table. This trigger is on the former. What I am trying to do is, when there is a submission in the Equipment Inspection Table, I want to use triggers to query the Equipment Table and see if the #inserted.Serial_Num is already present. If it is, I only want to update the record in Equipment Table with new information. If there is no match, I want to add a new record to the Equipment Table with the provided information.

The setup above doesn't seem to do either. 

Any help is appreciated.

Link to comment
Share on other sites

  • 0

Hi @kpcollier,

I think I have a similar workflow, mine requires me to check the table on insertion, and if record with matching Level and Value is already there, I don't want to insert anything:

Triggered Action on 'lu_dropdown' table:


'IF' part is checking if matching record is already in the table, but I guess you can use that same approach and just add 'UPDATE' part after 'DELETE'.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...