advanced search: multiple or selection in different fields

[#1]

I want to do an advanced search with an or selection in different fields.

For example:

I need to select all values in the field product with wine in it but I also want to select all values in the field substance with alcohol in it but I don't want to exclude values because of the relation between them (in other words I can't use and).

In product is a wine without alcohol and I don't want my search to exclude this wine because of my search in the field substance for alcohol. (hope I make myself clear ;-)) .. Thing is I don't want to affect the searches in the different fields on eachother. Sounds like I need to use or...

advanced search in a listbox

= product like 'wine' or substance like '*alcohol*'

Only thing is, when I do this, I get different selections depending on the listbox where I do the advanced search. In my opinion it shouldn't matter on which listbox I do the advanced search because I'm defining the fields in the advanced search (bcs of defining these fields in the advanced search it should be possible to do the search in the listbox age for example)..

[#2 · Accepted solution]

Hi William,

I'd guess that this is dependent on how values in the field (listbox) you're searching on are linked to other fields, including the 'product' and 'substance' fields, and the field(s) you're using in expressions.

One way this can go wrong is with a table that contains (among others) the following records:

[country, product, substance, sales]
France, wine, alcohol, 14000
France, cognac, alcohol, 7000
France, wine, water+artificial flavour, 1200
France, fat-free milk, milk, 20000
Belgium, mineral water, water, 10000

If you select on the 'country' listbox using an expression "=product like 'wine' or substance like '*alcohol*'", the expression will select every 'country' value that's linked to either a product value 'wine' or a substance value containing 'alcohol'. What happens is that the 'country' value 'France' is selected, and therefore everything in the cloud that's linked to 'France' is selected; this includes the values from the record 'France, fat-free milk, milk, 20000' because they're linked to the 'France' value which you selected.

If you're trying to calculate some expression for only the product 'wine' or any products containing substance 'alcohol', you should try using set analysis in your expression. In the example case:

sum({1<product={wine}> + 1<substance={"*alcohol*"}>} sales)

(which is: sum of sales for everything linked to product 'wine' or linked to some substance containing 'alcohol').

Hope this helps,

Martijn ter Schegget

[#3]

Thanks, totally clear for me now.. I will use this solution with some inputboxes and variables to make it more flexible and user friendly. Thanks Martijn!