Guide To Writing Queries

Values

14min

Values are always part of the RHS, and denote the specific thing you're searching for.

Certain values are only compatible with particular fields on the LHS - for example, if the LHS is a number or currency, the RHS value cannot be text. QuickQuery will always let you know if you accidentally mismatch types on the LHS and RHS.



Text

A value representing text is always enclosed in double-quotes ", e.g. "Some Text". Unless specifically stated in the documentation on operators, these values are case-sensitive (i.e. "Some Text" is not the same as "some text", "sOmE tExT", etc.)



Dropdowns

When querying on a dropdown field, QuickQuery will automatically suggest the appropriate values.

Document image


Other than that, they are identical to text values.



Numbers

Numbers are entered as you would expect, e.g. 25. If you need a negative number, prefix it with -, e.g. -25. You can also prefix numbers with an optional +, if you want to, e.g. +50. For decimals, enter the fractional part after a period ., e.g. 3.14.



Currencies

When entering a currency, prefix the numerical value with the currency enclosed in double-quotes ", e.g. "USD" 7000. Be sure to include the space between the currency and the value.

The numerical value can be any valid number, e.g. "USD" -5.8.

Any of the following can be used as the currency specifier

  • the currency code, e.g. "USD"
  • the currency symbol, e.g. "$"
  • full name of the currency, e.g. "US Dollars"

QuickQuery will always suggest all available currencies.



Binary

The available binary values are true and false. You use them when searching by e.g. checkboxes.

[Do Not Call] = false


Temporal

There are two types of temporal values: fixed and interpreted. In both cases, time-zone settings are respected.

Fixed

Fixed values have a fixed form, and are entered exactly as seen here. They involve the units second minute, hour, day, week, month, year and can be any of the following:

  • now
  • today
  • this <unit>, e.g. this hour
  • last <unit>, e.g. last month
  • next <unit>, e.g. next year
  • Any combination of <whole number> <unit>, e.g. 3 years 5 minutes. The units must be sorted in descending order, i.e. 5 minutes 3 years is not allowed.
[Date Created] = today

All fixed values are converted to a time range. Therefore, the above query is actually translated to:

[Date Created] = <00:00 today>, <23:59 today>

This only happens with fixed temporal values, and not with interpreted temporal values.

Interpreted

Interpreted values allow for a "free-form" textual description of a temporal amount, such as next monday, -7 weekdays, first day of next month etc. Interpreted values are surrounded by @[], e.g. @[tomorrow 3pm].

The range of interpreted values is incredibly broad, and is documented in detail here.

Technical details



Interpreted values are interpreted as a series of modifiers/commands which change the value, starting from the current date and time.



Therefore, writing e.g. -7 weekdays starts with the current date and time, and subtracts 7 weekdays from it. The resulting value will be "now", but 7 week days ago.



As another example, writing first day of this month 13:00 starts with the current date and time, and first interprets "first day of this month", which changes the date appropriately. It then interprets "13:00", which changes the time.

In practice, the easiest way to find out what they can do is to try them out. Read this if you need help figuring out what a specific interpreted value translates to.

Here's an example:

[Date Created] strictly after @[tomorrow 3pm]

Interpreted temporal values are not converted to time ranges. Therefore, @[tomorrow 3pm] means tomorrow at exactly 15:00:00 down to the second.

When entering an interpreted value, be sure to enter the "more general" specifiers first, and the "more specific" ones later!



As stated above, each part of an interpreted temporal value is translated to a command that modifies the date and time. Therefore, writing @[tomorrow 3pm] will first interpret "tomorrow" and set the value to "<tomorrow date> <current time>", and then it will interpret "3pm" and set the time value to 15:00:00 (while keeping tomorrow's date).



However, writing @[3pm tomorrow] will first interpret "3pm" and set the time value to 15:00:00, and then interpret "tomorrow" and set the value to "<tomorrow date> <current time>", which will overwrite the 3pm we entered earlier!

Fixed vs. Interpreted

While interpreted values give you a much greater flexibility, they are not well suited when used with equality, because they represent an exact instant, and not a time range.

Consider the following two queries:

[Date Created] = today [Date Created] = @[today]

The first involves a fixed temporal value, and will therefore be translated to "date created between 00:00:00 and 23:59:59 today", which yields the query we probably meant. The second query involves and interpreted temporal value, and is simply translated to "date created equals 00:00:00 today", which will only display records which were created at exactly midnight today.

However, there's nothing preventing you from writing out the range yourself:

[Date Created] between @[today midnight], @[tomorrow midnight -1 seconds]