Ben Langhinrichs

Photograph of Ben Langhinrichs

E-mail address - Ben Langhinrichs

September, 2004
   01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30

Search the weblog

Genii Weblog

Civility in critiquing the ideas of others is no vice. Rudeness in defending your own ideas is no virtue.

Tue 21 Sep 2004, 01:09 PM
I have been playing around with a spreadsheet sample using our @Midas Formulas.  It is fun, although I'm not sure yet how applicable it is to "real" applications.  Basically, I am using the following table which was pulled from the Lotus Sales Tools db, and which is probably not even accurate anymore, but will serve for this purpose:

Inline JPEG image

Now, I want to use this as the basis for a spreadsheet, which I have built below:

Inline JPEG image

This latter table will use both the original table and its own values to calculate other values in the table.  All of this is a single rich text field, incidentally, with no fields at all.  The cells in blue are "editable", in that changes will be reflected elsewhere.  The cost per unit and total cost per product columns are all built with computed text.  For example, the Cost per unit column of the Messaging Express row has the following formula:

Inline JPEG image

Basically, we look up the tradeup value (either "Y" or "N") from the spreadsheet table and the price from the pricelist table.  The tricky little line @If(tradeup = "Y"; 3; 2) determines which column of data the correct price will be.  The price is determined to be $96.00.  Note that this is just a computed text hotspot in the cell.  The Total cost per product for the same row reflects this prices times the count of users, or 155*96.00=14880.00.

So, what happens if we change one of these values.  For example, if I change the tradeup flag from N to Y, the prices will change.  After I press the Refresh button, which just does a and uidoc.refresh, the result is:
Inline JPEG image

Compare that again with the original table (below so you can see them side by side) and see that the Cost per unit of the Messaging Express row has changed to $48.00 to reflect the tradeup price, the Total cost per product cell has also changed to 155*48.00=7440.00 and the grand total at the bottom has changed to $29896.25 from $37336.25.  Voila, a spread sheet in a rich text field.
Inline JPEG image

Hmm, I may also write a sample that uses the Midas Rich Text LSX to build the spreadsheet sample in the first place.  That would be a cool use of the two products together.

Copyright 2004 Genii Software Ltd.