Forum Discussion

Sbender5's avatar
Sbender5
Qrew Member
3 months ago

Troubleshooting Advanced Query Filter

Hello, This is my first time reaching out for some help on hear so apologies if this is a bit long. I have a pipeline set up in quickbase to stamp an unqiue PO Number to a purchase order after final approval  occurs. The PO numbers are set up as [Location Code]-[Order Department]-Date Approved in YYYYMMDD format-[Occurrence]

For each order approved in the same day with the same Location Code and Department the occurrence will increment by 1. Right now the pipeline stamps the first PO of a given location/department/day correctly with an occurrence of 1. My trouble is getting the search step (d) to find the previous matching POs from that day, lookup the latest ones occurence #, and add 1 to it. I have copied below the filter I put on my search step (d). This references the info from step c which is a lookup of the current PO that needs stamped.

[Location Code] (Field 25) is type Formula - Text

[Order Department] (Field 21) is type text but is a lookup field

[Ap Date Approved] (Field 16) is type Date/Time

Field 39 is type Date

[Occurrence] is type Numeric

 

{

"from": "bvur62pw9",

"where": "{'25'.CT.'{{c.location_code}}'} AND {'21'.CT.'{{c.order_department}}'} AND {'39'.EX.'{{time.now|date_ymd}}'} AND {'16'.BF.'{{c.ap_date_approved}}'}",

"sortBy": [

{

"fieldId": 40,

"order": "DESC"

}

],

"options": {

"top": 1

}

}

formula query

1 Reply

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

    I think you are inventing syntax here that is simply not supported. But I have a suggestion.

    First, have the trigger type be "On New Bulk Event".  That will ensure that the records get processed one by one and you don't have a race condition where multiple records might trigger at virtually the same time if records were somehow created or approved by some kind of mass update like grid edit. 

     

    Do a normal search step (no Jinja) in the Pipeline to look for records matching the same 

    [Location Code] 

    [Order Department] 

    [Date Approved] (if necessary make a helper field in Quickbase so you have a clean filter on date .. or maybe that is what you meant by fid 39).

    Then ask Chat GPT this:

    "Is there Jinja Syntax which can be used in a Quickbase Pipeline which can return the size of a prior Quickbase Search step"

    Yes — in a Quickbase Pipeline (which uses Jinja syntax) you can get the size of a prior Quickbase Search step.

     

    ✅ The key pattern:

     

    If your previous step is something like “Search Records”, it returns a list (array) of records. In Jinja, you can use the built-in length filter.

    {{ a.records | length }}

    • a = the step name (replace with your actual step label)
    • records = the list returned by the Quickbase search
    • | length = counts how many items are in the list

     

    I think what I would do would be to have a Conditional Branch right after the search step  to check of the search was empty.  Then branch off to logic where your numbering needs to start at 1.  Chat GPT says it will not return a clean zero if the search result is null.

    Then for the other branch where the search is not null, so per ChatGPT to add 1 to the result, the syntax should be like this.

    {{ (search_customers.records | length) + 1 }}