DomBlog.de - Notes, Domino and Stuff :: Show-n-tell Thursday: Categorizing lists using @formula language

I think that Show-n-Tell Thursday is a great institution. Although I don't get to blog stuff every week I wanted to share a piece of code I which again shows how powerful the @formula language is. What I like very much about @forumla language is its ability to very efficiently process lists. You could do amazing things with the functions in R5 but @Transform and @Sort have been great additions in Release 6.

The code I am sharing today makes use of many of the list processing functions. It takes a list of fields and values and creates a categorized list. Of course things like that can be done in JavaScript or LotusScript or Java as well but @formula language is very efficient and performant and can be used in Notes and web apps for example in computed text or in a CFD field.

Project Background

An application I wrote a while ago is importing documents from another Notes application. The documents are basically containing information about a company's branch office or plant. Since it is a large company there may be several departments at that site. For each department the document may contain some fields containing information specific to that department at that site.

Since the departments change now and then and I do not like to hard-code stuff like this, the department names (and their abbreviations) are stored in the database setup profile document of my application. Also, I don't know which fields may be there containing the department-specific information. So this is configured in the database setup as well. What I do know however is how the field names are composed in the source application. They all comprise a basic field name, an underscore and the department abbreviation.

So I basically have two lists, one containing the basic field names like "Building", "Manager", and so on. The other contains the department abbreviations "ABC", "DEF", ...

In my application the information from these fields only needs to be displayed. Since I don't know which fields I will import the information is displayed using a single computed text hotspot (knowing that the amount of data will not grow too large).

Example

For this example let's assume I have these data fields:

DepManager_DEF=Mary Myers
DepBuilding_DEF=B2
DepMailAddress_DEF=mary.myers@example.corp
DepManager_ABC=John Smith
DepBuilding_ABC=A1
DepMailAddress_ABC=john.smith@example.corp

I want to present the data in a categorized way and I also want it to be sorted alphabetiacally by department while keeping the order according to the order of the field labels in the database setup.

ABC

Manager = John Smith
Building = A1
E-Mail = john.smith@example.corp

DEF

Manager = Mary Myers
Building = B2
E-Mail = mary.myers@example.corp

This @formula code creates the categorized display above. For simplicity I have put the lists (_labels, _departments, _fields) in the code, they are actually taken from the database setup.

REM {Create a list of all department fields for display}; _sortsep := "~"; _depsep := "_"; _catlabel := "---"; _catfield := "CategoryDummy"; REM {This is the list of the field labels to be used in the display preceded by a dummy entry.}; REM {The order of this list denotes the order of the fields in the display.}; _labeltexts := _catlabel : "Manager" : "Building" : "E-Mail"; REM {create a list containing entries with an order number, a separator and the field label}; REM { example: 0~---, 1~Manager, 2~Building, 3~E-Mail}; _labels := @Transform(_labeltexts; "x"; @Text(@Member(x; _labeltexts) - 1) + _sortsep + x); REM {This is the list of the department abbreviations.}; _departments := "DEF" : "ABC"; REM {This is the list of the base field names.}; REM {The actual field names are the base field names prepended with the department abbreviation}; _fields := _catfield : "DepManager_" : "DepBuilding_" : "DepMailAddress_"; REM {This creates a list of all permutations of both lists producing a list of all field names to display data from}; REM { example: CategoryDummy_DEF, DepManager_DEF, DepBuilding_DEF, DepMailAddress_DEF, }; REM { CategoryDummy_ABC, DepManager_ABC, DepBuilding_ABC, DepMailAddress_ABC}; _allfields := _fields *+ _departments; REM {This creates a list of all fields and their values, separated by an equals sign}; REM { example: CategoryDummy_DEF=, DepManager_DEF=Mary Myers, DepBuilding_DEF=B2, DepMailAddress_DEF=mary.myers@example.corp, }; REM { CategoryDummy_ABC=, DepManager_ABC=John Smith, DepBuilding_ABC=A1, DepMailAddress_ABC=john.smith@example.corp}; _allfieldsvalues := @Transform(_allfields; "x"; x + "=" + @GetField(x)); REM {Now we replace the field names with field labels while keeping the department suffixes}; REM { example: 0~---_DEF=, 1~Manager_DEF=Mary Myers, 2~Building_DEF=B2, 3~E-Mail_DEF=mary.myers@example.corp, }; REM { 0~---_ABC=, 1~Manager_ABC=John Smith, 2~Building_ABC=A1, 3~E-Mail_ABC=john.smith@example.corp}; _alllabelsvalues := @ReplaceSubstring(_allfieldsvalues; _fields; _labels + _depsep); REM {Sort the fields by department, keeping the field order within departments}; REM { example: 0~---_ABC=, 1~Manager_ABC=John Smith, 2~Building_ABC=A1, 3~E-Mail_ABC=john.smith@example.corp, }; REM { 0~---_DEF=, 1~Manager_DEF=Mary Myers, 2~Building_DEF=B2, 3~E-Mail_DEF=mary.myers@example.corp}; _sortedlist := @Sort(_alllabelsvalues; [CustomSort]; @If( @RightBack(@Left($A; "="); _depsep) > @RightBack(@Left($B; "="); _depsep)) | ( (@RightBack(@Left($A; "="); _depsep) = @RightBack(@Left($B; "="); _depsep)) & (@Left($A; _sortsep) > @Left($B; _sortsep)) ); 1; 0) ); REM {Add list bullet}; REM { example: - ---_ABC=, - Manager_ABC=John Smith, - Building_ABC=A1, - E-Mail_ABC=john.smith@example.corp, }; REM { - ---_DEF=, - Manager_DEF=Mary Myers, - Building_DEF=B2, - E-Mail_DEF=mary.myers@example.corp}; _catlist := " - " + @Right(_sortedlist; _sortsep); REM {Clean category entries, i.e. remove bullet, _catlabel and _depsep (" - ---_" from prefix and "=" from suffix)}; REM { example: ABC, - Manager_ABC=John Smith, - Building_ABC=A1, - E-Mail_ABC=john.smith@example.corp, }; REM { DEF, - Manager_DEF=Mary Myers, - Building_DEF=B2, - E-Mail_DEF=mary.myers@example.corp}; _catlistdep := @Replace(_catlist; (" - " + _catlabel + _depsep) + (_departments + "="); _departments); REM {REMove category suffixes from field names and implode list for display}; REM { example: ABC, - Manager=John Smith, - Building=A1, - E-Mail=john.smith@example.corp, }; REM { DEF, - Manager=Mary Myers, - Building=B2, - E-Mail=mary.myers@example.corp}; @Implode(@ReplaceSubstring(_catlistdep; _depsep + _departments; ""); @NewLine)