A note on colours and colour searching

The colour field is usually rendered as text using the custom colour name for the field, as specified in the Document Preferences Fields tab.

As a special case, when searching using the search parser, the right hand side of a comparison with a colour field may be any one of [the default english colour name, the custom colour name (these can be specified for each different table that has a colour field), or a numeric colour index]. The search parser is used for expressions passed to functions such as CreateSelection() and Find(); the search expression in a ForEach part in a report; or in the Advanced Find dialog. Even then there is a caveat that this only applies if the search parser is able to optimise the search into a series of raw single-field database queries (this usually requires that the expression be a simple series of field comparisons separated by ANDs or ORs (and not a mixture thereof)). In this case it will interpret the right hand side to determine the colour index to search for and will search the raw numeric colour field for a colour index match. If the search expression is too complex to optimise, then the entire expression will be evaluated per-record using the general expression parser and in that case it will end up doing a text compare with the custom colour name because the colour field will have been rendered as text. Note that this means that if the right hand side is text containing a wildcard (@), the results will differ depending on whether the search was optimised or not. In a numeric colour index search, "B@" will be interpreted as Blue (being the first colour in a top down search of the default colour name table that matches the given name), whereas in a textual search, "B@" will match both "Blue" and "Brown". The same would apply to custom names.

When deploying a report or script where the custom colour names in the document will not be known, it is necessary to understand this distinction to be able to use the default name or index with reliable results.

Login File