@Formulas from The Bear

This is the last of my seven-piece tribute to Bill Ernest and no tribute to him would be complete without some serious @Formula jockeying.

I learned about @Formulas at the feet of the Master. By slogging my way through the unbelievably complex @Formula code underlying Bill's Quality at Work masterpiece I eventually learned an amazing amount about how to use this strange and interesting language (and wore out a Lotus Notes Reference Manual in the process).

But, no matter how good I got, he was better. We used to joke that Bill's Indian name was He Who Walks On Water With LotusScript, but it was really with @Formulas that he was the most unbelievable.

Therefore, it's fitting to end this series with a quick lesson in the power of the formula language.

Back to the basics

To do anything useful with @Formulas, you need to really, truly, understand several basic functions:

@Left (and @LeftBack)
@Right (and @Rightback)
@Word
@Replace
@ReplaceSubstring
@Subset
@Explode
@Implode

Learn these inside and out and you're most of the way to @Formula nirvana.

No, these may not be the ones you use all the time. And, yes, you need to know the others; @Prompt, @DbLookup, @DbColumn, and the hundred others that are the bread and butter of @Formulas. Of course you need to know them, but they're not as interesting. At the end of the day there's only so much you can do with them. But my list...that's where you start unleashing the power of the language.

One of the most interesting things about @Formulas is that almost all of them work their magic not just on single values but on lists. That's "arrays" for those of you raised on other, possibly inferior, languages. This is a good thing because in Notes you have a lot of lists.

Compared to Notes, most other databases don't take much of a liking to lists. Tell most DBAs you have mutiple values in your fields and they start rolling their eyes because, well, their tools don't like that a whole lot. Notes, on the other hand, loves 'em. We get lists all over the place: from @DbLookups and @DbColumns, from field values, from dialog box choices, from view selections, from...well, you get the idea. Lists are all over the place in Notes, which is part of the power of the tool.

Since @Formulas leverage the power of lists, they are an integral part of doing things in Notes. Yes, there's LotusScript and JavaScript and even Java for the real masochists in the crowd but, whatever it is you're trying to do, if you can do it with @Formulas you can be almost certain it will execute faster than it will in one of those other languages. As Rocky Oliver likes to say, it's closer to the metal.

So, get to it already

OK, enough of the sales pitch. Let's see some code.

Better yet: Here's a challenge: You have a single string containing the history of several different transactions:

"11/11/2004 Joe G. Davis changed the status from New to Submitted. 11/12/2004 Larry Bird changed the status from Submitted to Approved. 12/12/2004 Walter Jones, Jr. changed the status from Approved to Confirmed."

It's your job to extract some meaningful data from it. Into multiple-value fields called Dates, Names, StatusFrom and StatusTo you need to put lists of the corresponding data from each entry. In other words, the Dates field should contain the values...

11/11/2004
11/12/2004
12/12/2004

The Name field should contain the names of the people named in each entry (in the proper sequence, of course), and the StatusFrom and StatusTo fields should end up with lists of the beginning and ending stati. Are you with me?

OK, so how would you do it?

No, seriously. How would YOU do it? That's really the question here. I can show you how I would do it but how much will you learn from that? One of the most important things I learned from 12 years of working with Bill Ernest is there is no shortcut. If you're going to make your living doing the things we do, you have to just plain-old learn them.

There's room for creativity and improvisation and excellent user-interfaces are always highly appreciated but, I can't say this any simpler, you have to learn how to use the tool.

I'm going to show you the answer (at least my opinion of it) in a second but I recommend you try to solve it yourself first. Do your best. Work it out, THEN come back and see how yours compares to mine. If it's better, or it's close, post it here (include a defense if you like). If not, well, that's OK as long as you learn something from the process.

For that reason, in the great tradition of The Bear (our nickname for Bill), I'll show you the answer without explanation. If you don't understand it, take half an hour to tear it apart and figure out exactly how and why it works. That will be a great start toward you becoming a power @Formula coder, too.

Here's my solution:

L1 := "11/11/2001 Joe G. Davis changed the status from New to Submitted. 11/12/2001 Larry Bird changed the status from Submitted to Approved. 12/12/2001 Walter Jones, Jr. changed the status from Approved to Confirmed.";
REM;
Nums := @Explode("0~1~2~3~4~5~6~7~8~9"; "~");
From := " changed the status from " : " to " : (Nums + " ") : (". " + Nums);
To := "~" : "~" : (Nums + "~") : ("&" + Nums);
L2 := @Explode(@ReplaceSubstring(@LeftBack(L1; "."); From; To); "&");
REM;
FIELD Dates := @Word(L2; "~"; 1);
FIELD Names := @Word(L2; "~"; 2);
FIELD FromState := @Word(L2; "~"; 3);
FIELD ToState := @Word(L2; "~"; 4);
""

Good luck. Let me know how you came out. Oh, and Bill? Thanks for everything.

1. Richard Shergold05/10/2005 06:54:28 AM

Scott, I think that's brilliant. Thanks, I've enjoyed all 7 in the series.

2. Erskine Harris05/10/2005 08:18:31 AM

Scott,

This has been a great series of Blog entries, that I have read everyday. I think Bill would be proud of you passing on the knowledge and making people think about how to figure things out as opposed to just giving it to them.

Thanks,
E