Sorting high to low within a Pipeline search

Question

I am trying to create a dynamic report that will stay updated with our top 25 clients based on their ACV.

I have a checkbox field on each account labeled 'Top 25'. My goal is to have this automatically updated based on each account’s ACV. Ideally this proccess would:

Look at all eligible Accounts

Rank them by ACV from highest to lowest

Identify only the top 25

Check the Top 25 checkbox for those Accounts

Uncheck Accounts that fall out of the Top 25

Recalculate this automatically on a schedule

The main issue I'm having is the Search Records step lets me set a record limit of 25 without giving me an option to sort the results by ACV. Because of that, I’m concerned that limiting the search to 25 records would not necessarily return the 25 highest ACV accounts. Any help will be appreciated. Thanks!

kmizuno · Accepted Answer

Update: I may have found a workaround. If i sort my default report to highest to lowest and have the search return only 25 that might work.

Top Step · Answer

I'd skip the default report and do the sort and limit in one API call. Use a Make Request (or Fetch JSON) step against records/query with a sort on ACV descending and a top of 25, then iterate over the results and check Top 25 on each.

Example body:

{"from":"YOUR_TABLE_DBID","select":[3],"sortBy":[{"fieldId":ACV_FID,"order":"DESC"}],"options":{"top":25}}

Relying on the default report sort works, right up until someone changes that report without knowing your pipeline depends on it. Then your Top 25 quietly goes wrong. Putting the sort in the request keeps the logic inside the pipeline.

You'll also want a step that clears Top 25 on records that drop out. Otherwise they stay checked forever. Search for records where Top 25 is checked, then uncheck any that aren't in the new 25.

https://developer.quickbase.com/operation/runQuery

https://help.quickbase.com/docs/http-channel

https://help.quickbase.com/docs/search-records-in-quickbase-and-iterate-through-the-results

Sign up

Login to the community