Pipeline to Search 130k Records in Another App and Create if the Record Does Not Yet Exist

Question

I'm trying to optimize my pipeline and I'm having trouble deciding on the best way.

I have 2 apps in the same realm. App "A" has approx. 30k records, and app "B" has approx. 130k records. When a new record is created in the Clients table of App A, I'm searching the Clients table in App B to make sure it doesn't already exist using the unique identifier of Login ID.

My problem is, the pipeline takes SO long searching through 130,000 records to see if the Login ID already exists in App B. If the Login ID does not already exist, I'm having the pipeline create a new client record, and if it does exist, do nothing.

I know this isn't best practice and I'm sure there is a Jinja reference that I can use to speed this up but I'm a rookie when it comes to jinja. Can anyone offer assistance? Thanks in advance!

markshnier__you · Answer

What if you have the Pipeline create a Bulk Upsert, and add the one new row to it for the newly created record, and then commit the upsert for that one record. It will either edit the existing record or add a new one if it does not exists. If you are just creating one field in table B, then the upsert for an existing record will do nothing, as it already exists.

Since the Login ID in table B is unique, you should be able to use it as a Merge field for the Bulk Upsert.

toddmolino · Answer

Thanks Mark! I'll research this and give it a shot. Thanks for the quick help!

My Advanced Query continues to return an error:

Validation error: Incorrect template "{{ a.create_date - time.delta(days=1) }}". TypeError: unsupported operand type(s) for -: 'NoneType' and 'relativedelta'

I'm trying to search for Client records that were created yesterday and add those to my upsert.

markshnier__you · Answer

I'm not very proficient in Jinja Date Math. You can cheat and make a native Quickbase field and then hook onto that. Also, You have not explained how you plan to use a date/ time field in a fitter in the pipeline.

In native Quickbase you would just have a formula checkbox field

ToDate([Date Created]) = Today() - Days(1)

toddmolino · Answer

Def leaning towards the formula checkbox filter at this point LOL. Problem is, in my pipeline, ONLY the advanced filter option is available. I can't even use the simple filter option which is where I would reference the formula checkbox. Perhaps that's not allowed when doing upserts? Still very green at jinja and bulk upserts. I'll keep plugging away at this one. Thanks again.&nbsp;

markshnier__you · Answer

Why not just fire the Pipeline one by one as new records arrive in Table A?

Forum Discussion

Pipeline to Search 130k Records in Another App and Create if the Record Does Not Yet Exist