Forum Discussion

TomHarwood's avatar
TomHarwood
Qrew Trainee
5 months ago

Help Needed with Quickbase Pipeline to Accumulate Values Instead of Overwriting

Hi Quickbase Community,

I am facing an issue with a pipeline designed to add quantities to an existing field in my Quickbase app, but it keeps overwriting the existing values instead of accumulating them.

Context:

  • Tables Involved:
    • Transactions: Records of items being added or removed from containers.
    • Containers: Contains fields for tracking quantities of different items in specific compartments.
  • Fields Involved:
    • Transactions Table:
      • Action Type: Indicates the type of transaction (e.g., "Add Item to Container").
      • Container Compartment: Specifies the compartment (e.g., "Compartment A").
      • Container ID: The ID of the container to which the item is being added.
      • Item Name: Name of the item.
      • Item Quantity: Quantity of the item being added.
    • Containers Table:
      • Compartment A Quantity: Field to store the quantity of items added to compartment "Compartment A". 

Current Pipeline Configuration:

  1. Trigger: On new or modified records in the Transactions table where Action Type is "Add Item to Container".
  2. Condition: The compartment is "Compartment A".
  3. Lookup Step: Fetch the current value of Compartment A Quantity from the Containers table.
  4. Update Step: Add the new quantity to the existing value of Compartment A Quantity.

Issue:

Despite various attempts, the Compartment A Quantity field in the Containers table is being overwritten with the new quantity instead of adding the new quantity to the existing value. Here is the current Jinja expression used in the update step:

jinja

{{ (b["Compartment A Quantity"] | default(0) | int) + (a["Item Quantity"] | int) }}

Steps I Have Taken:

  • Verified the field types and names.
  • Ensured the pipeline permissions are correct.
  • Tested with different variations of the Jinja expression to accumulate values.
  • Checked pipeline logs for errors or issues.

Request for Assistance:

I am looking for a solution to ensure that the Compartment A Quantity field accumulates the new quantity with the existing value, rather than overwriting it. If anyone has faced a similar issue or has any suggestions on how to resolve this, your help would be greatly appreciated.

Thank you in advance for your assistance!

Best regards,

Tom

Pipelines
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 months ago

    I do not have the skills to assist you with the technical Jinja syntax issue but if you don't mind a comment, why don't you just have summary fields to calculate the current item quantity.  If you have a relationship where one item has many transactions you can just roll up the ins and outs and have a summary field which represent the running total and that way you have a perfect audit trail with perfect math that will always add up and never be subject to say perhaps a pipeline failure.  

    • TomHarwood's avatar
      TomHarwood
      Qrew Trainee
      5 months ago

      Summary fields is what I have settled on. I am managing many compartments in these containers and was afraid there may be a limit on the number of summary fields I can generate between the tables through relationship but if there is I haven't found it yet. I am hoping the number of them wont hurt performance. I think I am still going to be forced to create pipelines to manage related Item information, and other validation rules, but there is a Zen to pipelines and I happen to enjoy them. 

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 months ago

    Summary fields are very efficient. You are unlikely to run into performance issues unless you hundreds of thousands of records and say 100+ users. 

    • TomHarwood's avatar
      TomHarwood
      Qrew Trainee
      5 months ago

      Mark,

      Update: This is working well with the summary fields to manage quantity. Also, I have two pipelines managing the item name data. One pipeline for adding item/ product name to a compartment when a quantity is sent and a second that activates when quantity levels are " <= 0" to remove item/product name from compartment. For posterity here is the jinja example, 

      {% if b.item_count <= 0 %} {{CLEAR}} {% else %} {{b.item_name}} {% endif %} 

      Note: "b" from above is the look-up step from the pipeline.

      Thanks for your help!