Forum Discussion

MikeTamoush's avatar
MikeTamoush
Qrew Commander
3 years ago

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 table (which is a child to both). The entries will be - one entry per month per subcontractor between the two dates. For example, if start date is 1/1/21 and end date is 12/31/21, I want it to create entries for February, March, April, Etc (a date field on the entry can simply be dated 2/1/21, 3/1/21, and so on. Ill probably include the first and last month but that is moot, I can control that with >= formulas).

The trick: If the dates are changed I want the Pipeline to add on any more months now needed (deleting is probably unnecessary as I can filter out unnecessary months on reports).

The second trick: I cannot have my pipeline delete all the entries and recreate them if a date is changed. I need the Pipeline to search for what exists, and create the missing ones.

Where I currently can do:
I already have a formula field which makes a text list of all the missing months (what dates still need to be created). I thought this might be helpful but haven't figured out a way to incorporate it. I thought maybe I could make yet another table that just simple has month dates listed for the next decade, and somehow use that in a loop, but I still can't come up with how I would utilize it.

I have figured out one way but it involves triggering the pipeline over and over again. I thought if I have 10 subcontractors on a 2 year project, that would trigger the pipeline 240 times in a row and I thought that would use too many resources, but not sure if that is true?

Ideas?

------------------------------
Mike Tamoush
------------------------------
App builders

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew 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's avatar
      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
      ------------------------------