Need to auo create records, must be a better way than my idea

I have a Projects table with two dates (Start/End). I have a subcontractors child table (usually around 10 subcontractors per project). I want to trigger a Pipeline that creates entries in a 3rd...

App builders

MarkShnier__You

Qrew Legend

3 years ago

I have a partial answer but it's not thought all the way through.

The first part is that if the records that you are creating can be set up with a Custom key field which incorporates the date and the related contractor and the project that they were all attached to then when you create the records you will create a Bulk Upsert and merge them in. That way you don't have to worry about whether you're creating new records or not as they will just merge in as an upsert and get created where new records are needed like when the date range changes

As for creating a set of records between two dates, when I have done this in the past with automations I was able to set a value in a single record helper parent table which set a start date.

Then I had another helper table which had see a few hundred records number from one to 200. I think I just use the record ID.

Then I calculated a date based on an offset from the starting date to these records to count forward. Then I did a search where I limited the results to just records which were not further out than the end date range I was looking for. In other words I created a situation where this helper table with a few hundred records started at the start date and went way past the possible and date but I just retrieved in the search the ones that I want it and use those to create the child records.

But if you were able to get that to work it would have to be some kind of looping pipeline because you would not be able to have multiple pipelines running and currently.

On the other hand, I'm sure using Jinja there would be a way to search a helper table which had a few hundred records in it bring them back and use the results of that search in some mathematical Jinja data math formula to add a number of offset days to the start date as part of creating the bulk Upsert row content.

Maybe those thoughts will lead you towards a solution.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

MikeTamoush
Qrew Commander
3 years ago
@Mark Shnier (YQC)

Thought I would post a solution I came up with that worked well for me and was blazing fast. I abandoned the idea of searching and creating bulk upserts, and instead decided to fire a table to table import a number of times.

I created a helper record in a parent table. My pipeline simply updates this record with a number of dates and other fields. I also created a dates table (in my case only needed the first of every month, but you could create every date if needed). I made a big list in excel and just imported 20 years worth of dates.

My helper record looks fields up to the child table (date table). On the child date table, I have a formula checkbox that checks these lookup fields against the Date, and determines if it should be included in the table to table import.

So the Pipeline Updates the helper record, then runs the Table to table import. Then updates the helper record, runs the import.

In my scenario it might loop this a dozen times or even more, but each time it flags a handful of dates and imports them. It will create hundreds of records in a matter of seconds. On my first attempt of looping the pipeline, it took like 6 minutes to create the upsert and commit it, so this is light years faster!

------------------------------
Mike Tamoush
------------------------------

Forum Discussion

Need to auo create records, must be a better way than my idea