How to setup a custom filter available in view lists

[ctheuring]

Hello,

I missed a comparison "between". I.e. to select a range of postalCodes.
In SQL you can do it with "between": select * from company where postalcode is between '23000' and '23999' ...
or you can do it with ... where postalcode >= '23000' and postalcode <= '23999'
In espo I can select postalcode "greater than or equal" '23000' ... but there is no possibility to use the postalcode-attribute a 2nd time for 'lower than or equal' ...
or - 2nd method - to use 'between', but than you must have TWO input-fields.
How can I create one of the possibilities?
Thanks for Advice or an example.

Christoph
P.S. I've googled and read documentations, but I didn't found any applicable hints

[shalmaxb]

This might be, because your postal code is supposed not to be a number, so >= or <= won`t work. If it would be a number, you could use a cascading filter, first <= then >=.

[ctheuring]

Is this a special espo limitation because < and > also works with strings in mariaDB (and all other databases)?
In Germany postalCode is a integer, but in other countries it contains letters.

Christoph

[shalmaxb]

The postal code is a varchar field in espoCRM due to the fact, that other countries have postal codes consisting of letters and number, sometimes further symbols like hyphen.
As far as I know, you cant calculate with varchars in a database.
You could try to change the field type to integer, if you only use german postal codes, but be aware, the next problem is waiting, as an integer does not accept the leading zero as in some of the postal codes in Germany.

[shalmaxb]

What I tested meanwhle is a kind of workaround:
1. create an integer field (Ganzzahl), call it PLZ integer or alike.
2. create a formula plzInteger=adressPostalCode;

apply that formula to all your addresses, so the PLZ integer will be filled with the PLZ value.

This field you may now use for <= or >=, but not for a range from-to in a filter.

[ctheuring]

Thank you shalmaxb, I will try your tipp.
I've made, as a first step, create PLZ as integer and with SQL in a database-manager (I use DBWorkbench):
update firmen set plz = trim(billing_address_postal_code) where billing_address_postal_code is not null and billing_address_l_k_z3 = 'DEU' and CHAR_LENGTH(trim(billing_address_postal_code)) = 5
LKZ3 is a 3-digit country-code as of ISO 3166 https://en.wikipedia.org/wiki/List_o..._country_codes

Than I made the filelds visible: if the Countrycode is DEU or not ... and put both fields in Details (sadly it is not possible to place the 2 fields one upon the other) and the integer-field in search-layout.
For MY programm this solution is enought, there are only a few companies outside Germany.

About selecting there is no problem with the leading '0' - you select i.e. 400 and 500 (insted of 0400 and 0500). The only problem is the presentation of this 4-digits PLZ in lists and details as a 5-digit number with a leading 0.

As far as I know, you cant calculate with varchars in a database

You must nothing calculate, you have to select a range. This also works (in nearly all databases) also with string-fields - they be sorted alphabetically and tested position by poaition.
There must be a special reason in espo, that they didn't allow this ...