Delete or avoid duplicates with triggered action

[NickO]

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.

 

[NickO]

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.

 

[NiceDuck]

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

[CoopperBackpack]

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. 

[NiceDuck]

I think we dont need the whoe "if Then block" if you place the conditional statement  on the join field itself:

 

The join also acts as a condition statement and using an if then block on your case appears to be a kind of redundant filtering. 

I hope this helps
 

[Benades]

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.

[kpcollier]

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.

[KlisaN137]

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

[NiceDuck]

You may want to try this one as well:
 

 
Its just a join setup but works like not in/ not exist function.