Deleting Data that does not exist in the new file upload
Application Type
Traditional Web

Hi 

Hope you all had a great weekend. I have an employee database. This will be updated regularly as employees come and go or change details etc. The data comes in Pipe delimited format and successfully uploads. The thing i'm struggling to get it to do is to Delete existing records where they no longer exist.

i.e. The user will upload the new file, it successfully updates any existing records if changes are made (thanks to Jose Goncalves for helping with this)  it ignores records that have not changed, it adds new records (New Employees). But, If an employee leaves the company and therefore do not exist in the new file i cannot get it to delete said record from my table. 

It feels like such a silly thing to get stuck on. Ive done some research and it looks like i may need to use Use Indexing to do this.

Heres some screenshots and data hat may help:

TvId (index[3]) is my Primary Key as it contains the Unique EmployeeId.  I need to somehow get it to compare the New uploaded file with the exiting database and If the TvId in the exisiting Database does not Exist in the new upload - Delete. 

Thank you in advance for your advice.

Mike


You don't need that If. Delete in bulk after the loop.


Hi Mike,

how do you control this part? "it ignores records that have not changed"

It looks like it always update no matter if the data changed or not. If so, that is good for deleting process. After running the loop you can delete all the records with Date != CurrDate(). This will delete all the records that were not created or updated.

Regards

Hi Jose, 

Thank you again. My apologies it doesnt ignore the row. It updates the Date. i have tried various things that donet work, the thing i keep coming back to is this

It doesnt work but i keep coming back to it as it makes some sort of sense....Look at the Existing Table, IF TvId Value does not exist in at index [3] in the String Split. True = Delete from the Entity, False = Go ahead and Create or Update.

I have tried using both String_Split and StringSplit_Record, ive also tried placing the condition at various places within the flow.

**TvId is the Primary Key       The update rule is set to Delete. 


Thanks as Always

Mike

You don't need that If. Delete in bulk after the loop.


I believe this Deletes the existing table in its entirety and uploads again from scratch. The problem with doing this is that i have another table which uses the Employee_table Id. 

Its a booking application. So if i delete records from the Employee_Table it also clears my booking table.

This is why i need to isolate and delete only the records that don't exist in the new upload. 

This will only delete the records that don't exist in the new upload because the existing records will have today's date and the old ones will have a previous date.

You need to manage as well the bookings table, you can set the delete rule of Employee_tableId to delete and it will delete also the bookings when you delete the employee.

Ok I have tested this, rigorously put it through its paces., I cant believe it was this simple and i didnt see it. i was trying all sorts of funky things, and all it needed was a line of SQL! 

im a little embarrassed at this, but you live and learn :)

Thanks once again Jose.

Another Solution marked for you

Mike

With time you will see the flows more clear and which actions to adopt where :)

mvp_badge
MVP

Hi Mike,


According to me you should add one more attribute in entity for marking record is processed or not in existing loop(Marked as true if record processed). and at the end fetch all records from existing entity where attribute value is false(not processed) and delete those records.

once deletion done update the attribute value false again for next run.

One more thing we can do it multiple ways ,i mean adding a temporary entity like that.

I hope this will help you.


Thanks.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.