Pipelines Question

Question

Hello,I have 3 tables within the same App:1. Claims2. Report Summary3. Report Summary CopyThousands of claims will be funneled into the Claims table with many different company names and plan years each day. (The same company may have several plan years, which means the same company may have several different report summary records)I currently have a numeric-formula field named [field 1] within the Report Summary table that is a query which searches for specific details within the Claims table and sums the dollar amounts. This works perfectly.I would like to completely wipe out the Claims table at the end of each day, however, that will delete the summed values within each record housed under the Report Summary table. My thought process was to create a Report Summary Copy table, delete the query code within the numeric-formula [field 1] of the Report Summary Copy table and then create a pipeline that runs daily and essentially does the following:SUM([Field 1]+[Field 1 (copy)])When this process occurs, that pipeline-created-summed-value inside the Report Summary Copy table will remain in the field even after the Claims table is wiped, which will inevitably wipe all values from the Claims Summary table. I have tried to create this pipeline by using the following code:&nbsp;Search a.Report Summary TableSearch b.Report Summary Copy TableUpdate b.Report Summary Copy TableField 1 on b.Report Summary Copy Table:&nbsp;{{a.Field_1 + b.Field_1}}This brings up these 2 errors - Validation error: Incorrect template "{{a.Field_1 + b.Field_1}}". ValueError: invalid literal for int() with base 10: 'Field_1'Any recommendations on another way, or a fix for this way (this way meaning the pipeline route) would be much appreciated! Thank you------------------------------Shane Miller------------------------------

doughenning1 · Answer

The error is because you're trying to reference a field for a single object (a.Field_1 and b.Field_1), but A and B return a list of objects. You can add loops to fix:

A) Search a.Report Summary Table
For each record in A
    B) Search b.Report Summary Copy Table
    For each record in B
        - Update b.Report Summary Copy Table
           Field 1 on b.Report Summary Copy Table: {{a.Field_1 + b.Field_1}}
    End Loop B
End Loop A

Note the indenting is intended to show the steps are nested inside "For each" loops (e.g. Step B is inside the Step A "For each" loop.)

------------------------------
Doug Henning
------------------------------

doughenning1 · Answer

Hi Shane,Search records step returns a list of records, so you either need a loop or if you know there's only going to be one you can reference the first record explicitly for both A and B:{{a[0].Field_1 + b[0].Field1}}If you will have multiple records for A or B then you need to add loops.------------------------------Doug Henning------------------------------

shanemiller1 · Answer

Hey Doug,&nbsp;I appreciate your input. I haven't explored loops within QuickBase Pipelines yet. I'll do some research on that and see if I can figure it out. Thank you!------------------------------Shane Miller------------------------------

shanemiller1 · Answer

Do you happen to know why I would be getting those errors I mentioned above? I've read it is because a field is not an integer, however, I have double checked and they both are in-fact numerical fields. Is the query-code I created within the Report Summary table that sums the values from the Claims table causing the issue?

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

shanemiller1 · Answer

Doug,&nbsp;That worked perfectly. Thank you for your help!------------------------------Shane Miller------------------------------

Forum Discussion

Pipelines Question

5 Replies