Skip to main content
Question

How to use Pipeline to find the sum of a field?

  • February 28, 2024
  • 0 replies
  • 486 views
Forum|alt.badge.img+2

Hi all,

I am trying to use pipeline to first search/filter out the records that I want, then within those records, I want to sum up a field ("hours") and update a record on another app. 

I tried the following: 

Search records --> Look up the record that I want to update --> update record with sum

For the update record step, I have the following jinja code but it's giving me an error because it exceeded 100 records: 

{% set total = namespace(value=0) %}
{% for r in range(90) %}
{% set total.value = total.value + a.hours|default(0,true) %}
{% endfor %}
{{ total.value }}

Is there another way to do this? I wanted to try the "For each" loop but there isn't a global variable that I can assign. 

Thank you! 



------------------------------
Jay
------------------------------
    This topic has been closed for replies.

    Hello Jay,

    If possible on your app, I suggest creating a summary field to obtain the Hours total (on a parent record that would be associated with all the children that contain those hours you want to sum up.

    Then with the pipeline you can grab the Hours total from that summary field you created and send it to the other app.

    Alternatively, you can obtain the Hours total by creating a formula query field, that will query (as your pipeline does) all the records and return the sum of Hours. SumValues() formula query function – Quickbase Help 

    Let me know if you have questions.



    ------------------------------
    Alberto Tablada

    Alberto@bluewellgroup.com
    Lead Software Engineer
    BlueWell Group
    Florencia
    +50688083124
    ------------------------------

    DwightMunson1
    Forum|alt.badge.img+8
    • DwightMunson1
    • Registered
    • February 29, 2024

    Instead of using search records, I would the Make a Request step to search for your records and return a JSON object of them, then use another Make a Request step to sum the data. 

    As a little bit of an example, where I loop through records and create a time card up until a timestop variable: 

    My current Step B: 

    {# 
    FID 8 = Related Job
    FID 13 = PGE Work Request Record ID (KEY)
    FID 33 = PM Hours Remaining > 0
    FID 35 = Total Hours Remaining > 0
    FID 36 = Job - Related Customer
    FID 37 = PM Automation Eligible
    FID 38 = Alternate PM Automation Eligible
    FID 41 = Project Completion %
    #}
    {
      "from": "MyTableID",
      "select": [
        8,
        13,
        33,
        35,
        36,
        41
      ],
      "where": "{37.EX.'1'}AND{8.EX.'{{a.related_job | int}}'}",
      "sortBy": [
        {
          "fieldId": 41,
          "order": "DESC"
        },
        {
          "fieldId": 33,
          "order": "ASC"
        },
        {
          "fieldId": 35,
          "order": "DESC"
        }
      ]
    }

    My current Step C: 

    {
    {% set myDataSet = "MyTableID" %}
    {% set myTimeStop = namespace(val=a.hours) %}
    {% set myTimeLimit = namespace(val=a.time_limit_variable) %}
    {% set runningTotal = namespace(val=0.0) %}
    {% set relatedTimeSheet = namespace(val=a.related_time_sheet) %}
    {% set relatedWorkCode = namespace(val=a.related_work_code) %}
    {% set relatedClass = namespace(val=a.related_class) %}
     
    "to": "{{myDataSet}}",
    "data":[
        {% for row in (b.json.data) if runningTotal.val < myTimeStop.val %}
            {% set remainingTime = myTimeStop.val - runningTotal.val %}
            {% set currentTimeLimit = remainingTime if remainingTime <= myTimeLimit.val else myTimeLimit.val %}
            {% set currentValue = row['33'].value if row['33'].value <= currentTimeLimit else currentTimeLimit %}
            {% set runningTotal.val = runningTotal.val + currentValue %}
            {
                "37":{           
                    "value": "{{row['36'].value | int}}"
                },
                "39":{           
                    "value": "{{row['8'].value | int}}"
                },
                "28":{
                    "value": "{{relatedWorkCode.val | int}}"
                },
                "42":{
                    "value": "{{relatedClass.val | int}}"
                },
                "655":{           
                    "value": "{{row['13'].value | int}}"
                },
                "227":{           
                    "value": "{{currentValue}}"
                },
                "34":{           
                    "value": "{{relatedTimeSheet.val | int}}"
                },
                "742":{           
                    "value": "{{a.id | int}}"
                },
                "32":{           
                    "value": "0"
                },
                "108":{           
                    "value": "M-Level Time Automation"
                }
            }
            {% if loop.last == false and runningTotal.val <= myTimeStop.val %},{% endif %}
        {% endfor %}
    ]
    }


    ------------------------------
    Dwight Munson
    ------------------------------
    Terms & ConditionsAccessibility statement