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

Question

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.&nbsp;

I tried the following:&nbsp;
Search records --&gt; Look up the record that I want to update --&gt; 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:&nbsp;
{% 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.&nbsp;
Thank you!&nbsp;------------------------------Jay------------------------------

albertotablada2 · Answer

Hello Jay,
If possible on your app,&nbsp;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&nbsp;

Let me know if you have questions.
------------------------------Alberto Tablada
Alberto@bluewellgroup.comLead Software EngineerBlueWell GroupFlorencia+50688083124------------------------------

jay · Answer

Hi Alberto,
Thank you for your reply! Do you know if the SumValues function will work across different app?&nbsp;

Thanks------------------------------Jay------------------------------

markshnier__you · Answer

Jay, Formula Queries do not currently work cross app.&nbsp; That was an initial limitation they did while they were assessing performance impacts of using Formula Queries and has not changed.&nbsp;
A thought would be to bring in the data from the other table into a Connected Sync table set to refresh every hour.&nbsp; That might be OK for your needs.&nbsp; 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 · Answer

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.&nbsp;As a little bit of an example, where I loop through records and create a time card up until a timestop variable:&nbsp;My current Step B:&nbsp;
{#&nbsp;FID 8 = Related JobFID 13 = PGE Work Request Record ID (KEY)FID 33 = PM Hours Remaining &gt; 0FID 35 = Total Hours Remaining &gt; 0FID 36 = Job - Related CustomerFID 37 = PM Automation EligibleFID 38 = Alternate PM Automation EligibleFID 41 = Project Completion %#}{&nbsp; "from": "MyTableID",&nbsp; "select": [&nbsp; &nbsp; 8,&nbsp; &nbsp; 13,&nbsp; &nbsp; 33,&nbsp; &nbsp; 35,&nbsp; &nbsp; 36,&nbsp; &nbsp; 41&nbsp; ],&nbsp; "where": "{37.EX.'1'}AND{8.EX.'{{a.related_job | int}}'}",&nbsp; "sortBy": [&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; "fieldId": 41,&nbsp; &nbsp; &nbsp; "order": "DESC"&nbsp; &nbsp; },&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; "fieldId": 33,&nbsp; &nbsp; &nbsp; "order": "ASC"&nbsp; &nbsp; },&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; "fieldId": 35,&nbsp; &nbsp; &nbsp; "order": "DESC"&nbsp; &nbsp; }&nbsp; ]}My current Step C:&nbsp;
{
{% 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) %}
&nbsp;
"to": "{{myDataSet}}",
"data":[
&nbsp; &nbsp; {% for row in (b.json.data) if runningTotal.val &lt; myTimeStop.val %}
&nbsp; &nbsp; &nbsp; &nbsp; {% set remainingTime = myTimeStop.val - runningTotal.val %}
&nbsp; &nbsp; &nbsp; &nbsp; {% set currentTimeLimit = remainingTime if remainingTime &lt;= myTimeLimit.val else myTimeLimit.val %}
&nbsp; &nbsp; &nbsp; &nbsp; {% set currentValue = row['33'].value if row['33'].value &lt;= currentTimeLimit else currentTimeLimit %}
&nbsp; &nbsp; &nbsp; &nbsp; {% set runningTotal.val = runningTotal.val + currentValue %}
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "37":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{row['36'].value | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "39":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{row['8'].value | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "28":{
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{relatedWorkCode.val | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "42":{
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{relatedClass.val | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "655":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{row['13'].value | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "227":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{currentValue}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "34":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{relatedTimeSheet.val | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "742":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "{{a.id | int}}"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "32":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "0"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "108":{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "value": "M-Level Time Automation"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; {% if loop.last == false and runningTotal.val &lt;= myTimeStop.val %},{% endif %}
&nbsp; &nbsp; {% endfor %}
]
}------------------------------Dwight Munson------------------------------

Forum Discussion

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