Boolean Operations

Mimicking other database functions like AND, OR and XOR

Probably the single most used expression tool is the if(...) expression. I use them everywhere from the slideshow caption to rename from properties to custom panes, columns or library fields. With a little lateral thinking, we can format MEDIA CENTER database expressions that while not quite as elegant, perform the subject functions using a series of "if(...)" questions. We use the other expression tools in conjunction with if(...) to ask questions, to which there can only ever be a yes or no answer, and then output text based on those answers. Most commonly, if(...) will be used in conjunction with the IsEqual expression, or the IsEmpty expression. A comprehensive list of available functions can be found here.
Let's run through some examples using the IsEqual expression.

First we ask the question, then we tell the expression evaluator what to do if the answer is yes, followed by what to do if the answer is no. In its simplest form, it might look like so:
if(isequal([artist],cliff richard,1),this will be rubbish,this might be OK) This asks: is the artist Cliff Richard? If yes, display "this will be rubbish", and if no, display "this might be OK" Use that expression to make an expression based column (right click on a column header and choose columns > expression column) to see the expression in action:
http://www.theganghut.co.uk/pics/ia/12/cliff.jpg

Now that's a nice, simple example of an if(...) based expression. It is quite possible to do far more complex things with these because it is perfectly acceptable to ask further if(...) based questions. Building on our "cliff richard" example:
if(isequal([artist],cliff richard,1),if(isequal([name],wired for sound,1),please don't play this,this will be rubbish),this might be OK)
Here, we've asked the question, "is the artist Cliff Richard" and if it is, is the track named "wired for sound"? If the track is called wired for sound, display "please don't play this", if the artist is cliff richard, but the track is not wired for sound, display "this will be rubbish", and if it is not cliff richard, display "this might be OK"
http://www.theganghut.co.uk/pics/ia/12/cliff2.jpg

Placement of parenthesis and commas is important. For each opening parenthesis, there must be a closing one to complete the expression. Look at the first, simple expression above again. 1st, we ask if the artist is cliff richard, the italicised part of the expression deals with what to do if the answer is yes and the expression ends, while the following normal text deals with what to do if the answer is no, and the expression ends. We could quite feasably ask another question here instead of ending the expression. When writing complex expressions, I find it helps me to deal with each branch as I come to it, so I deal with each yes answer until I've finished asking questions, then back track down the string to deal with what to do if the answer is no.

One final point when using the IsEqual expression... Note that I've used the number one in the artist and name questions above? This tells the evaluator to do a case-insensitive text compare when asking the question. There are 8 different compare modes that can be used and you can read about them on the expressions page. Number eight can be particularly useful as it causes the expression to search for the given string anywhere in the field.

Real World Examples

Now, three 'real world' examples which for me, are always the best way to work out what is going on:

OR

   IF ([Keyword]="gangster" OR [Keyword]="criminal"), bad guy, good guy"

The keywords field could possibly contain hundreds of keywords seperated by semi-colons. Here, we use the number 8 to force a case-insensitive substring search of the [keywords] field. First, we ask if the word gangster is there, and if yes, show "bad guy". If no, we then ask if the word criminal is there, and if yes, show "bad guy". For all others, show "good guy". We've asked two questions, and in this example, the two closing parenthesis come at the end of the string, the first of the two closing the second question, and the last one closing the first question.
if(isequal([Keywords],gangster,8),bad guy,if(isequal([keywords],criminal,8),bad guy,good guy))

XOR / AND

   IF ([Keyword]="demon" XOR [Keyword]="angel"), supernatural, IF ([Keyword]="demon" AND [Keyword]="angel"), invalid, non-supernatural

Pay attention to the comma that comes after "invalid". The evaluator seperates its commands using commas, so, to tell the evaluator to treat this comma as output text rather than command seperator, it must be escaped using a forward slash. Let's begin by breaking the expression into sections...

   is "demon" a keyword? if yes, is "angel" also a keyword? if yes, then [keywords] contains "demon" AND "angel" so display "invalid, non-supernatural"

if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,

That takes care of AND. Next, we deal with what to do if "demon" is present, but "angel" is not, so we tell the evaluator to output "supernatural" and close this branch of the expression. This takes care of the first half of the XOR question.
if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural)

This branch of the expression ends here. There are no more questions to ask and a closing parenthesis is entered. Next, we deal with what to do if "demon" is not in the [keywords] field..

   The [keywords] field does not contain "demon", does it contain "angel"? if yes, output "supernatural"

,if(isequal([keywords],angel,8),supernatural,
and that takes care of the second part of the XOR question.

The only thing left now, is to tell the evaluator what to do about all the files that contain neither "demon" nor "angel" If you want it to return nothing for those files, all that's left to do is close the expression, as shown below. If you would like a label for those other files, place your text between the closing comma and parenthesis. The final expression looks like so:
if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural),if(isequal([keywords],angel,8),supernatural,)

There is one fly in the ointment here and I don't think it can be worked around. Note that we are doing "substring" searches here. This means that keywords like "demonstrate" or "angelic" would produce matches for "demon" and "angel", possibly skewing the results somewhat, as shown below:

http://www.theganghut.co.uk/pics/ia/12/supernatural.jpg

AND

   IF ([Keyword]="smart" AND [keyword]="hottie"), compatible, incompatible

First ask if keywords contains smart, if the answer is yes, ask if it contains hottie, if the answer is yes, output compatible, otherwise, output incompatible. Output incompatible if keywords does not contain smart.
if(isequal([keywords],smart,8),if(isequal([keywords],hottie,8),compatible,incompatible),incompatible)

Q & A

   What if the there are other keywords... In other words, will [Keyword]=jim match if the keyword field is actually filled with "jim, bob, sue" (three different keywords)?

Expressions work with raw data, so, [keywords]=jim will match from "jim;bob;sue" if you use one of the substring compare types (7 or 8).

   Are quotes needed around search items that contain spaces?  Does quoted text work?

again, expressions work with raw data, so if you use quotes, the evaluator will try to match on them too. spaces are fine. Spaces are interpreted literally in all areas, except immediately after a comma in a function. However, a space can be inserted immediately after a comma by precediing it with a / escape character.

   These pages are hopeless, I still have unanswered questions, what to do?

If you don't mind, you could ask your questions in this thread in the MEDIA CENTER support forums, where you should get a quick response. If all the questions are contained in a single place, it should help these pages to develop by keeping references in a less fragmented manner.

marko