How to Overcome Query Limit

  • 1
  • 2
  • Question
  • Updated 3 years ago
  • Answered
Has anyone come up with a method of overcoming the QuickBase query limit of only 100 lines in a query or 100 values searched?  For example, let's say I have 500 Account Numbers I need to retrieve from an Accounts table using a Report that prompts for Account Number, or using an Advanced Search.  I am aware of the technique using the Equals operator along with OR between search values, but you are also limited to 100 values by QuickBase.

I'm thinking of writing a tool to take the values and chunk them into 100 values at a time, run the query using either API_GenSearchsTable or invoking a query URL directly, then stitching the results together.  However, if someone has done this already, no need to reinvent the wheel.

Thanks,
Joe A.
Photo of Quickbase Generic User

Posted 3 years ago

  • 1
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Well since I only really know native QuickBase I will suggest a brute force native solution for searching the specific case of 100 Account numbers. Not sure of your actual need is more general than that.

You can make a new table call Account Selection with one and only 1 record I it. That will be Record ID# = 1.  Then relate that back to your child table with a reference field formula field of 1.

Make 200 fields for 200 account numbers and then concatenate them together using List (";"   ).  Look that up down to ALL details record and then on the detail record have a formula checkbox field [Account matches search criteria?]

Contains([All account numbers],totext([Account number]))

Then put a report link field on that Account Selection record to show all records  for which
[Account matches search criteria?] is checked.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
You have to chunk your criteria in groups of 100. I did this here where I queried if the [Record ID#] was one of several values:

https://haversineconsulting.quickbase.com/db/bkfwuwxzn
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Mark,

Points for your creativity in solving this in native QuickBase.  However, looking up 500  Account Numbers is just an example.  Sometimes there could be 10 values to lookup, and other  times there could be tens of thousands of values to be looked up.  So I'd hit the QuickBase field limit, not to mention the pain in formatting to get ready to import into those fields.  So I don't think this approach is viable in my situation.

Joe A.
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Dan,

Looking at that sample app of yours (including downloading and looking at module.js), I don't see how this relates to my original issue.  I see the code in module.js that adds the button and grabs the rids where the field "Select" is checked.  But my situation is taking an external list of values, where there could be many thousands, and doing a looking in the QuickBase table to locate those matching records.  Am I missing something here with your sample?

As I said in my original question, I realize I have to chunk it up into searches retrieving no more than 100 records at a time, then stitch it back together.  Just thought if someone had already done that, and were willing to share, I could reuse it.  Thanks.

Joe A.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
You are sort of right here - I was just going from memory. There is a bit of code in the example that was not used but was created to chunk the set of rids into groups of 100 (100 is the maximum number of query criteria QuickBase allows):

    var n= 100;
    var ridsChunked = _.chain(rids).groupBy(function(element, index){
      return Math.floor(index/n);
    }).toArray().value();
    //console.log(ridsChunked);

I guess I never used it because I thought it would be unlikely that a user would manually select over 100 records. But if over 100 records were selected you would have to handle them in batches of 100 using the rids that were returned and composing query parameters like this:

query1={3.EX.1}OR{3.EX.2}OR...OR{3.EX.99}OR{3.EX.100}
query2={3.EX.101}OR{3.EX.102}OR...OR{3.EX.199}OR{3.EX.200}
...
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Okay, I see where you are going with this.  Thanks.
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Mark,

You have inspired me to come up with a native QuickBase solution as follows.

I created a parent table called "Search for Accounts" with just an Account Number field in that table, and made that the key field.  Then on the child table, the reference field is a formula that is the Account Number. On the parent table, I can count the number of children, which will be either zero or one if it matches.  In a report on the parent table, I show only those with a count greater than zero, along with a Formula URL to view the child record invoking a report that has "ask the user" for the Account Number supplying the Account Number in the URL.

Now all you have to do is first delete all existing records in the parent table, then second import a new list of Account Numbers you want to find, and run that report to see a list of matches.  (That's the one of the big difference in my approach versus yours -- just import a single column of Account Numbers instead of fiddling around with thousands of fields where you may hit a limit.)

Next I may add a reverse relationship to pull up some of the fields from the child table into the report on the parent.

Nine years ago, Kirk Trachy told me "If you can say it, you can do it" regarding native QuickBase.  I'm starting to believe him.  :-)  Of course with Dan's IOL technique, all things are possible if it cannot be done in native QuickBase.



Joe A.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Yes if you struggle night and day there may be a way to do it without JavaScript but why struggle with a bunch of one-off solutions which have all sorts of limitations and special conditions when you can uniformly do everything with script.

What QuickBase needs to do it make it easier to use script.
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
You make a good point.  I certainly vote for making scripting easier in QuickBase.  In products I've developed in the past, I would always provide a "standard method to do nonstandard things".  QuickBase needs that.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Well when Service Workers lands in Edge (and it is development now) it will not make any difference as you will be able to inject JavaScript on every page and QuickBase will not be able to do anything about it.

https://youtu.be/awUgAqRSxxI?t=30s
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Agreed, Service Workers will be the "standard method to do nonstandard things" *once* released in all major browsers, *and* people have updated to the version supporting it.  Correspondingly, I see you are now the "Pres. of Service Worker Union" instead of IOL.  Thank you Mr. President.  :smiley:

BTW, love the video!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
We need to do nothing but non-standard things since standard things can't do everything.

All major browsers are evergreen and self update today.

I am willing to bet you have Service Workers installed in your browser and you don't even know it. To check visit this page:

chrome://serviceworker-internals/
Photo of Joe Acunzo

Joe Acunzo, Champion

  • 240 Points 100 badge 2x thumb
Yes, I have service workers running and saw those a while back after our conversation on the topic.  However, I'm not like many large organizations with IT people who are control freaks and, sadly, lock down desktops preventing browsers from automatically updating.  But we'll be there one day, hopefully sooner than later.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Joe,
 Thank you for posting that native solution. Once you say it it is so obvious and also so obviously better than what I posted.