There is a trick I teach my students to help them remember Boolean (true/false) syntax.  I have them enter criteria into the select expert and then click the “Show Formula”  button to see the same criteria written in Crystal formula syntax.  So if you forget how to write a formula that does “between”, “is one of” or “not equal to” just put that into the select expert and copy the formula that it generates.

And just last week I learned something new.  The Select Expert will let you mix ranges and single values when using the “is one of” comparison.   So say I want to return 62 specific order numbers.  I want all orders numbered from 1001 to 1030, all orders numbered from 2001 to 2030 and two other individual orders numbered 2035 and 2037.  In the select expert you can put all of this in one rule by entering:

Order ID / is one of / 1001  to  1030 / (click the ‘add’ button) / 2001  to  2030 / (click the ‘add’ button) / 20035 /

(click the ‘add’ button) / 2037 /(click the ‘add’ button)/ Click OK /Refresh.

I was surprised that “is one of” allows a user to enter a value like “1001  to  1030” in a single entry.  This works in v10 so it probably works in v9, but it does NOT work in the select expert of v8.5.

Then if you look at the formula it will look like this:

{Orders.Order ID} in [ 1050 to 1080 , 2050 to 2080, 2700, 2800]

Which is a second thing I learned.  You can mix ranges and single values in square brackets.   One caveat is that while it works in the Select Expert it only works the first time it is entered.  If you then go back to the Select Expert a second time you will notice that it no longer says “Is One Of” but now says “Formula” and shows the above formula.  It still works fine, but you can’t add additional items to that rule using the Select Expert.  You would have to enter them into the formula by following the same syntax pattern.

But that syntax is what I consider the most valuable part.  This syntax can be used not only in the Selection Formula but also in any IF-THEN statement (after the IF).   So if you have to enter a long list of codes, and there are some that are consecutive, you can simplify the list by using the syntax shown above to enter ranges.  And this formula syntax CAN be used in the Selection Formula of v8.5 or in v8.5 formula fields, even though it can’t be used in the Select Expert of v8.5.