Hopefully, by now you know about Rocky Oliver's new initiative for all of us (YES, you, too) to give a little back, or out, or somewhere, to the general Notes community every Thursday. It's a collaboration tool; let's share.

Great idea, Rock. It was, as we are fond of saying around here, a Blinding Flash of the Obvious. Glad you had the sense to think of it. Wish I had.

So, without further ado, and in the spirit of Rocky's initiative, I'll show you mine if you'll show me yours (figuratively speaking, of course):

I love the formula language. I really do. It's fast, it's powerful, and (shhh, don't tell anybody) it's something a lot of newer developers, those who came at this from a Computer Science or, perish the thought, Visual Basic background, often have a hard time getting their heads around.

And yet, the things you can do with @Formulas are often a little like magic. Or at least, they can seem that way.

The mystery of the disappearing list elements

Let's imagine you have two fields, Company and State. Both are multiple-value fields. Both have several values:

Company	State
Abercrombie	OH
Boeing	WA
Cummins Engine	IN
DaimlerChrysler	MI
Evenflo	OH
Google	CA
Home Depot	GA

Ok, so let's say you need to find all the companies in the list located in particular state. Ohio, let's say. How would you do it? Go ahead. I'll wait. How would you write the code to extract only the names of the companies from Ohio?

If you could use LotusScript, which would be cheating, you could write a simple looping construct more or less like this:

Dim w As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Set uidoc = w.CurrentDocument
Set doc = uidoc.Document
Dim x As Integer
Dim companyList As Variant
x = 0
y = 0
Forall coName In doc.Company
   If doc.State(i) = "OH" Then
      Redim Preserve companyList(y)
      companyList(y) = doc.Company(x)
      y = y + 1
   End If
End Forall

That oughta do it, but that's a lot of work for what little you actually get out of it, and way too much typing. So what about not cheating and using the formula language? Well, you could do pretty much the same thing using the new ND6 looping constructs:

@For(n := 1; n <= @Elements(Company); n := n + 1;
   @if(States[n] = "OH";
      CoList := @Trim(CoList : Company[n]);
      ""
   )
);

That's certainly better, and a lot less verbose, but I'd rather keep it simpler still. That's still too much typing for my tired old fingers.

Formula language to the rescue: It turns out, all you really need to do everything we're trying to do here is this:

@Trim(@Left(Company + "~" + States; "~OH");

Really. Try it for yourself if you don't believe me.

What's going on here is elegantly simple. Company + "~" + States creates a list of concatenated values; Abercrombie~OH, Boing~WA, Cummins Engine~IN, and so on. @Left(...; "~OH") gets you everything that's to left of the string "~OH." That gives you a null for any list item that doesn't end with "~OH" and the name of the company for any that does. In other words, in my example above, you get this:

"Abercrombie" : "" : "" : "Evenflo" : "" : ""

@Trim( ) simply removes the null values and leaves you with "Abercrombie" and "Evenflo," the Ohio companies. You've gotta love the formula language.

1. Chad Schelfhout02/15/2006 11:25:15 PM
Homepage: http://www.chadsmiley.com

Very cool, I always did the @Explode with the third parameter of @False to get ride of empties but the @Trim is even more slick.

2. Peter von Stöckel02/16/2006 07:03:23 AM
Homepage: http://www.bananahome.com/

You've got to love those @Formula constructions! In cases like these, there's just no beating them!

3. Donovan Quesenberry02/16/2006 03:21:06 PM

Greetings,
Very KIS. This is what I was hoping S&TThurs would be. Simple ideas, simple code, extremely usable.
I appreciate it.
Stay Well
dq