Forum Discussion

UriGoldstein's avatar
UriGoldstein
Qrew Trainee
5 months ago

Pipelines: Lookup newest record?

Hi,

In a Quickbase pipeline, I need to fetch a single record from a table. That record needs to be the newest record added to the table. 

I tried two common approaches but neither seems possible using "Search Records" or "Lookup Record" steps:
  - Filtering so that Record ID = "MAX(Record ID)"
  - Sorting by Record ID descending and limiting results to 1.

Any help would be appreciated.

Kinds regards,
Uri

Pipelines
  • MarkShnier__You's avatar
    MarkShnier__You
    5 months ago

    One way to do this is to identify the Max  Record ID# in native Quickbase first,  you can do that by making a helper table with one record in it, and relating it to all records in the details table. With the formula field of 1.  

    Then do a Summary Maximum to get the Max Record ID#.  Then you know in the pipeline which record to retrieve with a Lookup Record step.

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

    One way to do this is to identify the Max  Record ID# in native Quickbase first,  you can do that by making a helper table with one record in it, and relating it to all records in the details table. With the formula field of 1.  

    Then do a Summary Maximum to get the Max Record ID#.  Then you know in the pipeline which record to retrieve with a Lookup Record step.

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

      There is probably a way to do this in the Pipeline using the results of a query and a jinja Max function, but I don't know the syntax for that.

  • UriGoldstein's avatar
    UriGoldstein
    Qrew Trainee
    5 months ago

    Many thanks Mark!

    I ended up using another technique where I used a formula query to "reverse number" the records a "formula- checkbox" field to identify the latest formula based on that.

    Sharing the recipe in case someone might find it  useful:

    1. I added a "Formula - Number"-type field (named "Order") to my table. The formula is:

    // Query all records with Record ID greater than the current record's Record ID
var text QUERY = "{3.GT." & [Record ID#] & "}";  

// How many records returned from the query
var number ORDER = Size(GetRecords($QUERY)); 

// Because ORDER actually yields nothing when empty
If($ORDER > 0, $ORDER + 1, 1)



    2. I added a "Formula - Checkbox"-type field (named "IsLatest") with this formula:

    [Order] = 1



    3. In my pipeline I lookup only the record where IsLatest is checked.