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 recor...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------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 & 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).
------------------------------
Joe Drosen
------------------------------
