Forum Discussion

JoshWeeman's avatar
JoshWeeman
Qrew Assistant Captain
8 years ago

Cannot locate explanation or syntax for ""Group By"" (gb-) option in query string

Hello, I am attempting to learn how to write more powerful queries within QuickBase.  While I am familiar with the "Sort Order" query option (aka "so-" or "sortorder-"), I have only recently seen examples of other query options, such as the "gb-" option as seen below:

opts=so-AA.gb-VX

or

...&options=sortorder-D.gb-X.nos

The latter example came from queries in this QB article below...

https://community.quickbase.com/quickbase/topics/ever-wanted-to-make-your-exact-form-load-conditiona...

The QuickBase API guide makes no mention of the "gb" switch in its 'query options' documentation page here.  However I was able to at least deduce from google searches that it facilitiates a "Group By" behavior (I believe).  Unfortunately, I have yet to find any explanation about how this switch is used or the proper syntax, etc.  I submitted a support request to QB, but got a response back this afternoon explaining that QB simply "adopted existing database query option syntax", and that it is not QuickBase specific, so that's why they have no documentation on it.  That would be fine and dandy if I could find any definitive explanation  anywhere else online, but I'm not having any luck.

Anyone with query writing experience able to shed some light on how to use the "gb-" switch within query options in QuickBase? Also, what is the ".nos" that is seen in one of the examples above used for?  Anyone?

Much thanks in advance!
  • I do not have an answer, but I just wanted to to be sure that you are aware that if you run any report and use the more button, you get to see the query code.  So at least you can infer the syntax if there is no documentation.
  • JoshWeeman's avatar
    JoshWeeman
    Qrew Assistant Captain
    Thanks for reminding me of that, that's very helpful. After toying around with my reports and reviewing their extended URLs, I now have an understanding of the "gb" option. Still have not figured out what "nos" does however.
  • I'm pretty sure Dan Diebolt knows.  I think that if I mention his name here his bots will respond for him.
  • There are two different sets of URLs used in QuickBase - a "native" set and the API set. These two sets of URLs use different query parameters although on occasion there may be a common query parameter (such as qid). All the "native" URLs return HTML and are intended to be viewed by humans. Most of the API URLs return XML and are not intended to be viewed by humans - rather the API URLs are intended to be consumed by some type of script or program.

    When you view a saved report such as the following grouped report of the Formula Functions:

    https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6

    you can discover the native parameters used by this saved report by entering this URL and formatting the resulting JSON info:

    https://login.quickbase.com/db/6ewwzuuj?a=JBI_GenExpandedURL&qid=6

    {
      "errorCode": 0,
      "errorMsg": "",
      "expURL": "&qt=tab&dvqid=6&clist=7.11.8.9.10&slist=6.7.11&opts=so-AAA.gb-V.nos."
    }

    From the above JSON you can see that the parameters that make up this native URL are as follows
    &qt=tab
    &dvqid=6
    &clist=7.11.8.9.10
    &slist=6.7.11
    &opts=so-AAA.gb-V.nos.

    The &opts parameter is period delimited into three parts:

    so-AAA
    gb-V
    nos

    The so part means "sort order" and is encoded as an A (ascending) or D (descending) and refers to the fields identified by the &slist parameter.

    The gb part means "group by" and is encoded as a V (group by equal values), I (group by first word) or F (group by first letter). In this case there is only one grouping level so only one encoded V is used.

    I have no idea what nos refers to and I have never needed it.

    The API method API_DoQuery also has an &slist parameter along with an &options parameter where you can control the sort order using sortorder-A and sortorder-D (see the docs). There is no grouping parameter as API_DoQuery always returns a "rectangular" XML response. To group and XML response you convert it to JSON and using underscore's groupBy() method.

    So basically it is a small mess.
    • JoshWeeman's avatar
      JoshWeeman
      Qrew Assistant Captain
      Excellent, thanks for going into such detail.  Very helpful!