Limiting Results

By limiting the results retrieved from a query, several goals are achieved:

• The user may only be interested in the first x records in any case
• Results are returned faster and Central CPU time is not wasted unnecessarily on overly large results
• It helps avoid extreme cases where a user requests an unfiltered list of records from a table which has hundreds of thousands of records. This can even cause crashes on systems that don't have enough memory.

There are two ways to limit the amount of records retrieved using a single query:

1. ClayCentral Settings. This is an administration setting, and it is global for all users and queries. There are three settings:

a) “Default Maximum Results”: This affects lists, analysis, charts, pipeline, gantt, calendar, etc. This value is used as the default maximum results for queries that aren't limited (see below).
b) “Absolute Maximum Results”: This is the absolute maximum allowed and it overrides all query limitations when their values are greater than this one.
c) “Maximum Results for Drop-Down Lists”: This affects linked fields, drop-down search windows and many-to-many linked fields (unless the table is set to Advanced Search mode), and filter values.

The reason these settings are separated is because a company may need to see all 10000 customers in a linked drop-down, but no-one would need to see more than 2000 Customers in a dashboard.

2. In lists and analysis, the total amount of records (bottom right) is clickable. When clicked on, it allows the user to enter a limit for that query only. This limit is saved along with the query.

With items in a document template, this setting is also available in the item properties.

This value is per query and overrides the “Default Maximum Results” setting, but cannot surpass the “Absolute Maximum Results”.

This limit per query is different from the Central settings: The Central setting is absolute and precise (5000 means 5000), but the limit per query is a ranked limit that works according to unique sorted values. For example, if the results are:

[Company] [Sales]
Microsoft 1000 
IBM 500
SAP 500 
Oracle 100

If the list is sorted by Sales and the limit is set to 2, then it will return the first 3 records. If the list is sorted by Company then it will return only 2.