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

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

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

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

[MikeD]

I am looking for a solution to send a view each hour with a scheduled export but my issue is how not to send data I've already sent. I tried triggered action using timestamps an so on but due to the limitation of triggered and/or task only being able to work on the 1/4 hour and then my scheduled export being under the same limitation on on the 1/4's only if I run them both at the same time the export always wins which means some records will not be sent and will be lost on the next trigger if I use a hour minus 1 field. Any thoughts?

[NiceDuck]

Okay, heres an idea.

1. First, we need to tables, table a and b.

table b is clone/duplicate copy of table A.

2. Use the table b for the view.

3. For table A, have an extra yes/no field.

4. Then we use a task with this pattern:

4-1. Delete all records on table b

4-2. Insert the records from table A where the yes/no field is not yet checked to table b. ALso, make sure that you have a criteria to compare the unchecked values to the checked ones to make sure not to transfer records that from the unchecked ones that doesn't have a match on the checked ones will be copied to table B.

4-3. Update all unchecked records from Table A. to Table B.

5. Set this task to run before the export task you are using, for 15 minutes is the earliest we can set.

With this setup, we will not have to worry about the records that will not be included on the first run, since those new records will have their yes/no field unchecked, they surely be included on the next task and transferring them to table b.

I hope this helps.




 

[MikeD]

How do you get the checked boxes updated?

[NiceDuck]

@MikeD Imma use an update block right below the insert block to table B. 

All new records on table A should have their checkbox uncheck.