FIXED select_multiple search operators, wrong documented behaviour

[dh]

I am finding this tooltip guidance a little ambiguous:

please note that when you search a value in a select_multiple_* field using is_equal, you will find records having the selected value for the field (which means only that value or that value AND also other ones if you selected several values).

Does this mean Boolean AND -- that is, the search returns records having *all* the values selected or checked in the select_multiple? I think so, but need to verify.

And if that's correct, is there any way to get Boolean OR instead? That is, the search returns records having *any* of the values selected or checked in the select_multiple, but any given record may match only a subset of the choices selected/checked?

ALSO -- another question. Further up on the forms configurator, tooltip guidance on select_multiple says:

Note that the corresponding database field type must be varchar.

Must the varchar length be greater than the length of the primary key in the lookup table -- say, if the primary key in the lookup is char(2), and there are 10 unique values of that key, should this variable be set to varchar(20) in the underlying database being configured in DaDaBIK?

Thank you!

Your current DaDaBIK version​

You are using DaDaBIK version 11.7-Elba enterprise, installed on 09-16-2022 (installation code: 0), the latest version of DaDaBIK is

There are problems with the Internet connection, we cannot check if there are upgrades available.

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enterprise to Platinum) please contact us.

System info​

PHP Version: 8.1.20

mysql version: 8.0.33

Web server: Apache

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.5.1 Safari/605.1.15

 

[eugenio]

I think you have spotted something wrong in the documentation, thanks for that.

Let's take the ERP online demo and let's say that one of the products has two categories: music and others.
A - If you search for music, you will get that product
B - if you search for music + others, you will get that product
C - If you search for music+other+games, you won't get that product in the results.

Case A contradicts the documentation.

I think the labels that better describes the current search behavior for select_multiple_* fields is contains/doesn't contain (and not is equal/not equal, as currently stated) and each element you select is boolean AND combined with the others.

Let me know what you (and other users reading the forum) think about it, but I think in the next release upgrade I need to automatically replace, for select_multiple_* fields, all the:
is_equal, is_different
settings with:
contains, doesnt_contain,

and update the related documentation.

About the varchar question, consider that some separator characters are also used so the length should be more.

Best,

 

[dh]

Thanks for the guidance on varchar... that makes sense.

The problem with the "contains" language is I think it could still suggest a Boolean OR (which is actually what I was trying to achieve). I'm not familiar with the ERP demo, but here's my situation, which may be different from that demo:

My main table has a mySQL field that holds only one char(2) value (whose English name is in a lookup table) and I want to retrieve all records that match *any* of the select_multiples I check. However, using Boolean AND, any time I check more than one value in select_multiple, a search will be empty because there's only one value in the field. What I actually want is to return all records that match at least one of any of the checked values (which will be listed by English name in the check boxes or menu). So that's an OR.

So (a) I'm not sure "contains" describes that situation and (b) maybe select_multiple is entirely the wrong construct for getting the behavior I want.

 

[eugenio]

Hello,
I think you are using select_multiple for the wrong purpose; if the field can contain only one value, it should be select_single.
In DaDaBIK you can search using the boolean OR but only combining different fields, not on the same field.
There is boolean search on textboxes, though. If you open the ERP demo and then follow these instructions you will see it in action:

• Boolean search: choose products then search, contains and fill the description with "Synthesizer OR Keyboard" (without quotes)
  

About the documentation, I have thought about it and yes, probably is_equal, is_different is still the most correct description, however I think this part

please note that when you search a value in a select_multiple_* field using is_equal, you will find records having the selected value for the field (which means only that value or that value AND also other ones if you selected several values).

should be replaced by

please note that when you search a value in a select_multiple_* field using is_equal, you will find records having all the selected value(s) for the field (plus, optionally, other values).

 

[dh]

Thanks -- I think your new language is accurate.

I realize I could use a Boolean search on a varchar field like desc_product in the ERP demo, but that's not the situation for me. The field I'm interested in is just a char(2) that's also the primary key of a lookup table that gives me the alphanumeric name of that field. So I can't use the Boolean search construct in this situation, right?

To recap: select_single allows me to search for all records that have one single value of the key. But what if I want to search for all records that contain any of a subset of values of that one single key field? That is actually my use case.

This 'WHERE (key = a OR key = b)' would be trivial to hard-code in MySQL, but I'm looking for a way to leverage DaDaBIK's flexibility so I don't need to know in advance how many values to include in the OR set and so I can use the lookup ability to select my OR options by English-language text.

It seems to me that giving select_multiple an OR option (as the config file gives for search generally) might work. Is there a philosophical or practical reason for DaDaBIK not to work that way? I know I'm off-topic now on the "bug" issue, but it's the heart of my question.

 

[eugenio]

I confirm you can't use the boolean search as in the demo ("Synthesizer OR Keyboard") when you have a lookup field.

There isn't any philosophical or practical reason for not adding the OR boolean as an option for search on select_multiple, I think it's not a very common scenario and it hasn't been implemented. You can add this to the feature suggestions forum. However, I don't think this would perfectly fit your use case: maybe I am missing something but you would still have a select multiple field for a field that, in edit and insert, must be used to select just ONE value.

What you are really looking for is, for each field, in the search form, the possibility to add in boolean OR other possible values for the same field (you can sometimes see this feature in library search forms); this is something different that you can also add to the feature suggestions forum.

Just to clarify for future readers: in DaDaBIK you can choose to use the AND/OR boolean operator to combine the fields of your search form, what we are discussing here is an additional boolean operator to use for combining several options of the same field, e.g. select the books published in 2022, in (Italy OR Germany).

 

[dh]

However, I don't think this would perfectly fit your use case: maybe I am missing something but you would still have a select multiple field for a field that, in edit and insert, must be used to select just ONE value.

Yes, I agree. That may be exactly the reason it can't be done in the specific construct used by DaDaBIK.

What you are really looking for is, for each field, in the search form, the possibility to add in boolean OR other possible values for the same field (you can sometimes see this feature in library search forms); this is something different that you can also add to the feature suggestions forum.

Yes, this might be closer.

Just to clarify for future readers: in DaDaBIK you can choose to use the AND/OR boolean operator to combine the fields of your search form, what we are discussing here is an additional boolean operator to use for combining several options of the same field, e.g. select the books published in 2022, in (Italy OR Germany).

Exactly.

Thank you for this dialogue!

 

[eugenio]

Documentation Bug fixed in V 11.10