Wednesday, October 17, 2007

Keyword/Value lookup fields

I sometimes get a bit frustrated by the lack of associative arrays in the formula language (more specifically in keyword lookup-fields). I’ve thought about ways to emulate this a couple of times, but never got anywhere.

Today, I’ve come up with one way. It’s not beautiful, and one can argue that it’s bad practice.

standard @DbLookup( .. )

Column Value-syntax:
"Error message - keyword-list not found" :
"keyword" : value
"apple" : banana;

Syntax for getting value:
lupField[ @Member( "keyword" ; lupField ) + 1 ]

Error handling
  • Error message on "keyword value not found" maintained on a column basis.
  • First item in the Column Value list should be the error message. Reason being @Member( "itemNotInList" ) = 0 (+1=1)
  • "keyword-list not found" maintained in the lookup-field.

  • Put keyword/value wherever you want in the column formula
    • Great for design-templates if you want to have configuration-settings in a logical order in the lookup-column (reorder them whenever)
  • Print all settings through a simple @For-loop
  • Readable
    • @Word( settings ; "|" ; 24 )
    • versus
    • settings[ @Member( "stylesheet" ; settings) + 1 ]
  • ..?

  • Unreadable syntax for people not aware of the technique
  • ..?

>> Ugly flash-demo
>> Ugly demo-application

If any of you have an even simpler syntax/better concept, please let me know!


Dan Sickles said...

I also wish @formula had associative arrays. I also wish Lotusscript has real lists. Thanks for the tip.

Tommy Valand said...

No problem.

After writing the post, I've also found this "technique" useful for other stuff.

E.g. generationg icons for attachment-links.

iconList :=
"defaultIcon.gif" :
"jpg" : "/path/to/jpeg.gif" :
"ppt" : "/path/to/powerpoint.gif";


extension :=

icon :=