Forum Discussion

StephenStudy's avatar
StephenStudy
Qrew Trainee
4 years ago

Order results returned in Pipeline search query

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
------------------------------

10 Replies

  • 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
    ------------------------------
    • Danny's avatar
      Danny
      Qrew Trainee
      This worked out great for me! Thanks a lot for sharing this, Melissa!!


      ------------------------------
      Danny
      ------------------------------
      • MelissaDoran3's avatar
        MelissaDoran3
        Qrew Cadet
        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
        ------------------------------
    • ChrisJefferson's avatar
      ChrisJefferson
      Qrew Member
      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
      ------------------------------
      • MelissaDoran3's avatar
        MelissaDoran3
        Qrew Cadet

        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
        ------------------------------
  • @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
    ------------------------------
    • KevinRybicki's avatar
      KevinRybicki
      Qrew Member
      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
      ------------------------------
      • RyanPeart's avatar
        RyanPeart
        Qrew Trainee
        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
        ------------------------------
    • StephenStudy's avatar
      StephenStudy
      Qrew Trainee
      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
      ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      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
      ------------------------------