Pipelines - Delete unmatched records

Question

I've been using pipelines to replace some synced tables that I need to be updated "live".&nbsp; Basically two nearly identical tables with the same primary key.&nbsp;
I've created a pipeline for when a record is created in table A to create a record in table B.
I've created a pipeline for when a record is modified in table A to update the record in table B.
I've created a pipeline for when a record is deleted in table A to delete the record in table B.
Now i'm working on a daily refresh pipeline to ensure the tables are synced in case of errors.&nbsp; The bulk upsert process is what I have been using for that, and it works great.&nbsp; My problem is I don't understand how to include the logic to delete records out of table B that no longer exist in table A as part of my daily refresh.&nbsp; Is there a way to do this within my daily refresh pipeline or another pipeline?&nbsp;&nbsp;------------------------------Drew Voge------------------------------

chayceduncan · Answer

Do you have some way of binding the two together that you can match between? Such as - when you create a record in Table B from Table A, are you logging the Record ID# in Table B so you can match them back and forth?&nbsp;

If you are - you could run a daily process that takes ALL the records in Table A and imports the current date into table B as a 'Date Last Seen from Table A', and then search for all the records in Table B that don't show as 'Today'. The assumption there is that if you don't update a value in Table B as having been seen today then it must have been deleted from Table A.&nbsp;------------------------------Chayce Duncan------------------------------

drewvoge · Answer

I had a similar thought using a checkbox field called 'delete'.&nbsp; Basically the update process first sets all checkboxes to true, then the upsert process unchecks the boxes.&nbsp; After the upsert is committed, I delete all records where the checkbox is still checked.&nbsp; My concern there is an interruption in the process that results in all records being deleted.&nbsp; &nbsp;&nbsp;
I had hoped to do it within the pipeline not using new fields as I assume there is a method that my peabrain can't figure out.&nbsp; &nbsp;------------------------------Drew Voge------------------------------

chayceduncan · Answer

In all likelihood if you tried to identify it in the pipeline and then delete at the same time - you'd have to do the search and deletion one by one inside of a loop which would be taxing on the Pipeline, Quickbase, and be pretty slow. It would likely entail searching ALL your records in Table B - looping each - querying to see if you can find a match in Table A and if the query responds with 0 records in Table A, then delete the record from B.&nbsp;
That setup though means you're individually querying for every record one by one.
I haven't personally had a use case for it - but you might also look at the Pipeline table/row object. Again - never used it myself - but you could query table A - loop each record and add a row in a Pipeline table with the RID. Then, query for all records in Table B, then loop each record in that and instead of directly querying back to Table A - search the table to see if the associated Table A entry is in your Pipeline table and if it doesn't exist - then delete the Table B record. Same concept as above - but you're using the table like an array that you can index versus querying QB each time. Still slow - but less taxing on QB directly. This would definitely be the better option but comes with more trial and error.&nbsp;------------------------------Chayce Duncan------------------------------

joedrosen · Answer

We often use Pipelines to set "a flag". This could be a check box, or a number, where you take all of the records and give them this flag. This is similar to the above "delete" checkbox. You would then import the data from the other table &amp; make sure that all matched erase that flag... so you can then delete ONLY non-matched records.As far as looping through records, it will take a long time (depending of the size of your table.) We have found that it is somewhat quicker if you use the bulk Record Upsert (Basically as fast as a CSV import is).&nbsp;------------------------------Joe Drosen------------------------------

Forum Discussion

Pipelines - Delete unmatched records