Discussions

 View Only
  • 1.  Pipelines Jinja Sum

    Posted 20 days ago
    Hello,
    I have a question pertaining to pipelines and the sum function within jinja2.
    I have created a pipeline:
    Step A: searching through records on table 1
    for each A do:
    Step B: Look Up a Record on table 2 where the Query is [key field table 2] equals [a.key field table 1]
    Step C: Update Record
    *for step C I am trying to update a numeric field on table 2 which is a sum of [a.paid_amount] from table 1 where the [key field table 2] equals [a.key field table 1].
    I have tried putting the following in Step C for the field that is to showcase the summed value:

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

    and

    {{ a|sum(attribute='paid_amount') }}

    I keep getting this error however: Validation error: Incorrect template "{{ a|sum(attribute='paid_amount') }}". TypeError: 'Record' object is not iterable 


    Any recommendations of any kind would be greatly appreciated. Thank you

    ------------------------------
    Shane Miller
    ------------------------------


  • 2.  RE: Pipelines Jinja Sum

    Posted 20 days ago
    HI Shane

    Below will work .

    A much better explanation is going to come from Others , I will try in my limited Jinja

    {% set total = namespace(value=0) %}
    {% for r in range(a|length) %}
    {% set total.value = total.value + a[r].value|default(0,true) %}
    {% endfor %}
    {{ total.value }}​

    • To iterate through the records you need to find the number of records to sum up , range(a|length) achieves it 
    • to iterate through the records , you need to refer to each object you've searched , once your loop starts a[r] will allow you to refer to records/objects individually with loop value so if I have 3 records , it will do a[0] , a[1] , a[2] 

    I'm a Jinja newbie myself , so I'm sure some terminology I've used is wrong to explain

    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 3.  RE: Pipelines Jinja Sum

    Posted 20 days ago

    Hey Prashant Maheshwari,
    I appreciate you and Don Larson's feedback. I tried your approach, but sadly it did not work, I received the same error. I then tried combining yours and Don Larson's suggestion of replacing the look up a record with a search records, it failed once more.

    My field in table 1 [paid amount] is a numeric currency field, and my field in table 2 where I am trying to get the summed values to be displayed is also a numeric currency field. Does any of this matter?

    Also, you seemed confident in saying this will work, which makes me believe I am doing something wrong. With your example, and with my field that needs to be summed, I would put this in step C (update record) inside the numeric currency field, correct?:

    {% set total = namespace(value=0) %}
    {% for r in range(a|length) %}
    {% set total.value = total.value + a[r].paid_amount|default(0,true) %}
    {% endfor %}
    {{ total.value }}​


    ------------------------------
    Shane Miller
    ------------------------------



  • 4.  RE: Pipelines Jinja Sum

    Posted 19 days ago
    Edited by Don Larson 19 days ago
    Shane,

    I built a simple test application.



    Then I did a Pipeline to search for Table One then Table Two and then Update

    Total Value = Total Value + Paid Amount

    Screen shot before Run


    Screen Shot After Run




    You can see that each child record in Table Two had $100 add to the Total Value



    Here is the YAML with the Slug x-outed

    # Jinja Sum Test
    #

    # Account slugs:
    # - quickbase[XXXXXXXX]: Pipeline Token <None>
    ---
    - META:
    name: Jinja Sum Test
    - QUERY quickbase[XXXXXXXXX] record search -> a:
    inputs-meta:
    export_fields: '"Paid Amount" <6>'
    table: '"Jinja Sum: Table 1" <bszikwt45>'
    - a<>LOOP:
    - DO:
    - QUERY quickbase[XXXXXX] record search -> b:
    inputs-meta:
    export_fields: '"Total Value" <6>'
    query: '{7.EX.{{a.id}}}'
    table: '"Jinja Sum: Table 2" <bszikxmj2>'
    - b<>LOOP:
    - DO:
    - b<>ACTION quickbase record update -> c:
    inputs:
    total_value: '{{b.total_value+a.paid_amount}}'
    ...






    ------------------------------
    Don Larson
    ------------------------------



  • 5.  RE: Pipelines Jinja Sum

    Posted 19 days ago

    Wow, thank you so much for taking the time to do all that. That was above and beyond, I really appreciate it. I have tried to recreate your pipeline (tried adjusting the yaml and importing, but was having issues).

    I like how you were able to find a workout without using jinja2. If that works, then I am happy. Like yourself, I prefer to stay away from that language. I have uploaded a screenshot of my table 1 and table 2. I am now getting a different error when it tries to update the fields (screenshot). You can see that the records are pulling from the claims for that particular member and showcasing them on table 2. Just not summing them, haha.

    table 1 = Finance: Claims Detail Extract
    paid amount = numeric currency field

    table 2 = Finance: Copy of Members Staging Table
    Search Final Claims in claim detail extract = numeric currency field (AKA total value)

    YAML:
    Here is the YAML with the Slug x-outed

    # summing amounts based on key field
    #

    # Account slugs:
    # - quickbase[XXXXXX]: Pipelines <None>
    ---
    - META:
    name: New pipeline v2
    - QUERY quickbase[XXXXXX] record search -> a:
    FILTERS:
    - AND:
    - member_id equals 40087269503
    inputs-meta:
    export_fields: '"Paid Amount, Line No, UniqueID_Member_PlanYear (Spec Member
    Staging)(paste here), Member ID" <14, 11, 151, 7>'
    table: '"Finance: Claims Detail Extract" <bsqi7vyjb>'
    inputs:
    limit: 10
    - a<>LOOP:
    - DO:
    - QUERY quickbase[XXXXXX] record search -> b:
    FILTERS:
    - AND:
    - key_field equals {{a.unique_id_member_plan_year_spec_member_staging_paste_here}}
    inputs-meta:
    export_fields: '"Search Final Claims in claim detail extract, Key Field"
    <111, 60>'
    table: '"Finance: Copy of Members Staging Table" <bszhinu9m>'
    - b<>LOOP:
    - DO:
    - b<>ACTION quickbase record update -> c:
    inputs:
    search_final_claims_in_claim_detail_extract: '{{b.search_final_claims_in_claim_detail_extract+a.paid_amount}}'
    ...



    ------------------------------
    Shane Miller
    ------------------------------



  • 6.  RE: Pipelines Jinja Sum

    Posted 19 days ago
      |   view attached
    Screenshot of 2 tables and error code

    ------------------------------
    Shane Miller
    ------------------------------



  • 7.  RE: Pipelines Jinja Sum

    Posted 19 days ago
    Just figured it out. Everything you sent, and what I tried does work, but it will not work if the total amount field is blank. Once I manually changed the total amount field to 0.00 and then ran the pipeline it worked flawlessly. Thank you!
    Is there code-specific work-around for this? or any other work around that you may recommend?

    ------------------------------
    Shane Miller
    ------------------------------



  • 8.  RE: Pipelines Jinja Sum

    Posted 20 days ago
    Edited by Don Larson 20 days ago
    Shane,

    I have been greatly vexxd by Jinja and Pipelines.  @Prashant Maheshwari and @Doug Henning have been great helps so I suspect the suggestion will solve it.

    The error code you got is not very descriptive and I have had it too many times.  I believe that I can identify one case where it comes up.

    If you try to use data from search out side of the loop then you will get that error.

    Try this structure

    Step A Search
    For Each
    Step B Search​
    For Each
          Step C Update B​

    You have a loop inside of a loop.  If Step C is outside of the Loop for B you will get that error message.

    This might also solve it with a bit less Jinja which is good for my brain.


    ------------------------------
    Don Larson
    ------------------------------



  • 9.  RE: Pipelines Jinja Sum

    Posted 19 days ago
    Thank you . Very elegant solution @Don Larson ! Way more easy to follow

    @Shane Miller when I tried to create the above with a simple app and no looping shenanigans , just Jinja in itself was my focus. 

    Step A Search
    Step B - > Update Code



    ​​
    ​​​​

    ------------------------------
    Prashant Maheshwari
    ------------------------------