Forum Discussion

Jay's avatar
Jay
Qrew Member
3 months ago

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

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
------------------------------

4 Replies

Sort By
  • AlbertoTablada2's avatar
    AlbertoTablada2
    Qrew Trainee
    3 months ago

    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
    ------------------------------

    • Jay's avatar
      Jay
      Qrew Member
      3 months ago

      Hi Alberto,

      Thank you for your reply! Do you know if the SumValues function will work across different app? 

      Thanks



      ------------------------------
      Jay
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 months ago

        Jay, Formula Queries do not currently work cross app.  That was an initial limitation they did while they were assessing performance impacts of using Formula Queries and has not changed. 

        A thought would be to bring in the data from the other table into a Connected Sync table set to refresh every hour.  That might be OK for your needs.  You can just bring in the minimum # of fields you need to have available for the Formula Query.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------
  • DwightMunson1's avatar
    DwightMunson1
    Qrew Assistant Captain
    3 months ago

    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
    ------------------------------