Skip to main content
Solved

Pipelines: Lookup newest record?

  • August 4, 2024
  • 0 replies
  • 512 views
Forum|alt.badge.img+1

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

Best answer by MarkShnierYou

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.

This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24

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.

Mark Shnier. Your Quickbase Coach
    Forum|alt.badge.img+1
    • UriGoldstein
    • Author
    • Registered
    • August 4, 2024

    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.

    Terms & ConditionsAccessibility statement