Forum Discussion

DrewVoge's avatar
DrewVoge
Qrew Cadet
6 months ago

Pipelines - Delete unmatched records

I've been using pipelines to replace some synced tables that I need to be updated "live".  Basically two nearly identical tables with the same primary key. 

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.  The bulk upsert process is what I have been using for that, and it works great.  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.  Is there a way to do this within my daily refresh pipeline or another pipeline?  



------------------------------
Drew Voge
------------------------------

4 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    6 months ago

    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? 

    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. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • DrewVoge's avatar
      DrewVoge
      Qrew Cadet
      6 months ago

      I had a similar thought using a checkbox field called 'delete'.  Basically the update process first sets all checkboxes to true, then the upsert process unchecks the boxes.  After the upsert is committed, I delete all records where the checkbox is still checked.  My concern there is an interruption in the process that results in all records being deleted.    

      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.   



      ------------------------------
      Drew Voge
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        6 months ago

        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. 

        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. 



        ------------------------------
        Chayce Duncan
        ------------------------------