The @formula language can do some wonderful things, but you may find times when you need to combine @formulas with LotusScript. A common solution is to either use the Evaluate keyword, or to rewrite the formula as a LotusScript function. However, although these solutions work well for simple or singular @functions, they can be difficult to code for complex multiple formulas (such as, "legacy" R3 macros) or nested @DbLookups that use their results to provide the parameters for further lookups.
This tip describes an easier, form-based method for combining the code. The way it works is that you create a form called "function worksheet" that contains all the complex multiple @formulas that you want to use with LotusScript in the database. The input fields should be editable fields, with defaults to Null. The output fields should be computed fields, and they should contain the complex @formulas that will act on the input fields. The output fields are set to compute Null if there are no input values.
In your LotusScript code, create a document and set the form name to "function worksheet." Then, when you need to apply your @formulas, simply set the input values, ComputeWithForm, then extract the output results and use them in your LotusScript. Obviously, you do not need to save the worksheet once you have extracted the values, and you can even use LotusScript as a shell to perfom @formula loops.
Generally, you can use this tip with all @functions that you can use with the Evaluate statement. DbLookups may require a slight modification in resolving server name and path arguments because an @DbName doesn't work in Evaluate. To overcome this, place two input fields, such as DbServer and DbPath, on the "function worksheet" and resolve them with LotusScript prior to the ComputeWithForm.
Gary Steele is a Principal CLP and Senior Development Consultant for ITNET (UK) LTD. ITNET is one of the UK's leading outsourcing suppliers of combined information technology, and business process management. They have specific experience in the local government, process manufacturing, retail, chemical and financial services sectors. Gary is currently working on a land registration project for one of the UK's largest city councils.