Discussions

 View Only
  • 1.  Pipeline to create multiple records based on date range

    Posted 11-07-2022 15:27
    I have an application where employees can request vacation using a start and end date.  I have a duration field that calculates how many weekdays are in the range.  I need a pipeline to create a timecard record for each of the days requested once the PTO request is approved by HR, but not sure how to create multiple records based on start date and duration fields. 

    Ex: 
    Vacation Start Date:  11/3 
    Return to Work Date: 11/8 
    # of Days off: 3 (weekend in between). 

    Now I need to create 3 timecards for 11/3, 11/4, and 11/7​ for payroll.

    Any help/guidance would be appreciated!

    ------------------------------
    Curtis England, Trinity
    ------------------------------


  • 2.  RE: Pipeline to create multiple records based on date range

    Posted 3 days ago

    Hi Curtis, wondering if you ever found a way to do this. 



    ------------------------------
    Mike Brady
    ------------------------------



  • 3.  RE: Pipeline to create multiple records based on date range

    Posted 3 days ago

    There are lots of ways to do this ultimately - if you're confident with Jinja you can create a loop that builds a API load to your table. This is just off the cusp but something like: 

    Step A: Trigger, for your data or scheduled event (if applicable)
    Step B: Make a Quickbase Request
    URL: https://api.quickbase.com/v1/records
    Method: POST
    Content type: application/json
    Body: 

    {
      "to": "tableId", 
      "mergeFieldId": 3,

      "data": [

    {% for n in range({{(a.date_1 - a.date_2).days}}) %}
        {
           "fid": { 
              "value": "{{a.date_1 + time.delta(days=n)}}" 
            },
        }
      {% if loop.last == false %},{% endif %}
      {%- endfor %} 
      ]
    }

    The idea is to create the range for your loop to go through each day in your range and add it to your payload. 



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



  • 4.  RE: Pipeline to create multiple records based on date range

    Posted 3 days ago

    Mike, I  would personally tackle this in a less technical way than Chayce's solution. 

    We can make a helper table and leverage that to create the records.  It is a little much too describe the step-by-step in this forum.

    Basically we create a helper table of 100 records assuming nobody would take a vacation more than 100 working days long.  Then I would create a formula URL button to set a focus record to know which Multi day request needs the time cards created, populate that focus record with the start and end dates, look up from the focus record down to the helper table and automatic check box the ones that qualify to be created as time cards, and then run a Saved table to Table import to create those children, and land the user back on the vacation request record where the individual time cards would be created. That will happen in a single click in the blink of an eye.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------