Discussions

 View Only
Expand all | Collapse all

Order results returned in Pipeline search query

  • 1.  Order results returned in Pipeline search query

    Posted 04-28-2021 12:42
    I'm trying to return list of records in a table for a certain user and time frame but I want to limit the return to just the earliest record.  There is a "Limit" field so I can get the pipeline search to return 1 record but there doesn't appear to be a way to sort the returned value by ascending date so I can retrieve just the earliest entry.

    Anyone figured out how to sort the return of a pipeline search query?

    Thanks

    ------------------------------
    Stephen Study
    Software Development Manager
    Robishaw Engineering Inc
    Houston TX
    ------------------------------


  • 2.  RE: Order results returned in Pipeline search query

    Posted 04-28-2021 15:20
    Edited by Justin Torrence 10-02-2021 21:15

    @Stephen Study

    Because the pipeline object that is returned from the ​Search Records action is a "list" you will need to be clever about how you reference the value. Unlike when you iterate through the list using the built in loop action, you can't simply reference the value by dragging and dropping fields from the visual builder. Using Jinja, you can (similar to working with an array in other languages) be explicit about which item in the list you'd like to get the value from. For example: If I wanted the Record ID# from the first record in a list object, instead of using the expression that shows up when I drag and drop the field {{a.id}} I would specify that I wanted the value from the first record in the list like so: {{a[0].id}} and if I wanted the second I might use: {{a[1].id}}

    Here's what that looks like in action:

    Cheers



    ------------------------------
    Justin Torrence
    Quickbase Expert, Jaybird Technologies
    jtorrence@jaybirdtechnologies.com
    https://www.jaybirdtechnologies.com/#community-post
    ------------------------------



  • 3.  RE: Order results returned in Pipeline search query

    Posted 04-30-2021 10:26
    I'm brand new to Jinja and should have realized there would be a way to index into the return list/array.  Thanks for that nugget Justin!

    ------------------------------
    Stephen Study
    Software Development Manager
    Robishaw Engineering Inc
    Houston TX
    ------------------------------



  • 4.  RE: Order results returned in Pipeline search query

    Posted 05-19-2021 12:47
    This doesn't solve the issue with sorting the returned results though.  Does anyone know how to set sort order in the search query?

    ------------------------------
    Kevin R
    ------------------------------



  • 5.  RE: Order results returned in Pipeline search query

    Posted 06-11-2021 17:10
    I am also interested in sorting the search query. Does the order have to be sorted before the query?

    ------------------------------
    Ryan Peart
    Database Analyst
    Minerva Knowledge Management
    ------------------------------



  • 6.  RE: Order results returned in Pipeline search query

    Posted 08-10-2022 16:00
    Hey Justin,
    Thanks for posting this . Like Stephen, I never realised that search items are index in arrays too ! Awesome , and thanks for posting this. 

    Almost 2 hours of hunting !

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



  • 7.  RE: Order results returned in Pipeline search query

    Posted 10-06-2021 10:11
    Edited by Melissa Doran 10-06-2021 10:12

    I stumbled upon this thread last night while looking for a similar solution. I found something that worked after tons of trial, error, and googling. Here's a screenshot. 

    It takes the array of results returned in step B, looks for the one with the highest attribute for the field called "specificity_score" and returns that RID to a field in the action step. It's not exactly sorting results but if you were looking for the "earliest" record, you could set the attribute to a "created_date" or similar, and use a min() filter instead of max()

    ex: 

    {% set desiredrecord = b|min(attribute="created_date")%}
    {{desiredrecord.desired_field}}
     



    ------------------------------
    Melissa Doran
    Jr. Solutions Consultant
    Quandary Consulting Group
    mdoran@quandarycg.com
    ------------------------------



  • 8.  RE: Order results returned in Pipeline search query

    Posted 10-07-2021 09:57
    Edited by Danny 10-07-2021 10:02
    This worked out great for me! Thanks a lot for sharing this, Melissa!!


    ------------------------------
    Danny
    ------------------------------



  • 9.  RE: Order results returned in Pipeline search query

    Posted 10-07-2021 10:22
    Edited by Melissa Doran 10-07-2021 10:22
    Glad I could help! This will definitely be an often-used element in my toolkit going forward!

    ------------------------------
    Melissa Doran
    Jr Solutions Consultant
    Quandary Consulting Group

    linkedin.com/in/melissardoran
    mdoran@quandarycg.com
    ------------------------------



  • 10.  RE: Order results returned in Pipeline search query

    Posted 01-28-2022 22:30
    I get an error when I try and do this, it says there are too many results and I should use LOOP. (there are only 400 records from the search) 
    A = Trigger
    B = Search Results
    C = Update Record.

    I am trying to return the max version number (field) from the contacts table so I can use it in a Fetch JSON query so it limits the results returned from the external source.
    Too obscure? :-)

    ------------------------------
    Chris Jefferson
    ------------------------------



  • 11.  RE: Order results returned in Pipeline search query

    Posted 01-31-2022 09:31
    Edited by Melissa Doran 01-31-2022 09:31

    If it's the maximum from all records on the table, I would suggest using a 'helper' table. 
    You would relate all the contact records to the one single record on that table.
    Related help table = 1 (Default this field to 1, so all contacts are automatically related to the only record on that table) 

    Then get the max version number through a summary up to the help table, then either
    a) reference the help table directly in your pipeline
    or
    b) If the contact records are already part of your pipeline, lookup the max version number from the help table down to the contacts table. 



    ------------------------------
    Melissa Doran
    Solutions Consultant
    Quandary Consulting Group

    linkedin.com/in/melissardoran
    mdoran@quandarycg.com
    ------------------------------