Simple form/table based calculation tool

Problem

In our department we needed a generally accessible calculation solution to enable our sales people to create budgetary prices for our configurable systems.

Context

This article presents our T/Foswiki based solution, hoping the design idea is useful for others as well:

Solution

Item	Quantity	Price per item	Total price per item
Power Supply AC		550	0
Power Supply DC		250	0
Analog Input		20	0
Digital Input		10	0
Housing			800
Assembly			200
		Total	1000

Enter quantities and press Calculate button to update

Implementation Details

Altough the solution is quite simple, it's explanation is targeted to non T/Foswiki or HTML experts.

Overall Structure

The whole table is encapsulated in an HTML form field and has a submit button which calls the same page in view mode when pressend.

<form name="calcprice" action='%SCRIPTURLPATH{"view"}%/%WEB%/%TOPIC%'>

... The table is here ...

<input type="submit" class="twikiSubmit" value="Calculate" /> 
_Enter quantities and press Calculate button to update_
</form>

The table is structured as described in the following sections.

Column 1

| Digital Input |

Trivial, as it's just a label.

Column 2

| <input type="text" name="numdigital" size="4" 
value="%URLPARAM{"numdigital" encode="quote" default="0"}%"
/> |

This is the entry where the user can make it's input. It is implemented by HTML text-type input field. It has the name numdigital which can later be used to refer to the value of this input. Input fields store their value in URL parameters. From the TML level, you can use %URLPARAM{...}% to access these values. %URLPARAM{"numdigital" encode="quote" default="0"}% gets the value of numdigital or zero if it does not yet exists. The later happens when the topic is opened first. With enocode="quote" you force the value to be surrounded by ", so we can use the result of %URLPARAM{}% as argument to the value option of the input field. This is the method how a storage without session management or database is implemented.

Column 3

| 10 | 

This is a price-constant for our calculation.

Column 4

| %CALC{"$PRODUCT($T(R$ROW(0):C$COLUMN(-1)), %URLPARAM{"numdigital" default="0"}%)"}% |

This is the actual calculation. This is done by using SpreadSheetPlugin, which is utilized by %CALC{}%. Within the spreadsheet calcualtion R$ROW(0):C$COLUMN(-1) refers to the cell left to itself, which is the price constant in our example. With $T(...) you get the value of this cell. As before %URLPARAM{...}% gets the value from the user entry (this time without the qoutes) and both values are multiplied with the spread sheet function $PRODUCT.

Column 4 of the last row has %CALC{"$SUM($ABOVE())"}% to add up all values of the above rows. SpreadSheetPlugin is smart enough to recognize the heading as non number.

Known Uses

Known Limitations

See Also