[Kexi] Re: Calculated fields in kexi

[Neil Winchurst]

On Wed, 8 Dec 2010 09:19:21 +0100
Jaroslaw Staniek <staniek at kde.org> wrote:

> On 7 December 2010 13:03, Neil Winchurst <neil at pamneil.com> wrote:
> > On Mon, 6 Dec 2010 22:30:18 +0100
> > Jaroslaw Staniek <staniek at kde.org> wrote:
> >
> Hi Neil,
> Thanks for your explanation and espacially sharing your desktop
> database experience.
> We would like to have such specialists with such history in this community!
> I am also ex-MS Access user after all.
> 
> First, regarding bugs.kde.org - it accepts both bugs and wishes. At
> the beginning of reporting it looks like you're reporting bug, but
> later you could set the type of bug as a *wish*. This is the right way
> of organizing our work, so I enourage to go this way.
> Also if someone will report similar wish in the future, the new report
> will be redirected as a duplicate to your wish.
> 
> Regarding the merits, the calculated fields could be supported  "out
> of the box" of course. There are questions though:
> 
> If the form is based on query like "SELECT width, height, width*height
> AS area FROM rectangles", then area column is calculated based on the
> database engine. Newer apps like the newer MS Access allow to edit
> fields in such forms even if the query itself is not editable as a
> result of computation. I am aware of this very feature and has marked
> in as TODO maybe 3 years ago or so.
> If the form is based on "SELECT width, height FROM rectangles" and
> computation of "area" is entered directly in a text field e.g. as
> "=width*height" like in spreadsheets, then there's question of what
> language (of scripts) to accept in the fields.
> I am wondering allowing to select script type here (as in the script
> object) would introduce chaos, because some users would use
> javascript, some python, and some ruby expressions. I tend to think
> about javascript/ECMAscript. With optional ability to call functions
> defined in other languages through a bridge like KROSS.
> 
===================================================

I don't know about Access because I did not use it but in Paradox
calculated fields were available from the start. I should perhaps have
mentioned that Paradox has its own scripting language called PAL which
is the Paradox Application Language. This is used to create any
calculations etc behind the fields, buttons etc. So when setting up a
calculated field **there is no need to use a query**. The form is a
standard form which is linked to the relevant tables.

An example, I have a form for creating invoices. Among the fields are
Price, Quantity and SubTotal.

Price is a lookup field linked to the product table.
Quantity is a field in the Invoice table which is entered by the user.
SubTotal is a calculated field which appears on the form only and is
not linked to any table.

The SubTotal field has a small script set up using PAL. This script is
simply Price * Quantity.  This tells the program to go to the field
named Price and to find the contents, then to multiply that by the
contents of the Quantity field and to display the result in the. 
SubTotal field. Please note that this result is not stored anywhere. It
is recalculated 'on the fly' as you view the records.

As the user is creating the record, after putting in the quantity and
pressing tab the result of the calculation appears in the SubTotal
field automatically. It is of course is a read only field.

In this example, there will be other calculations to make such as VAT,
possibly discounts and totals at the bottom of the page. Lots of
calculated fields.

In Paradox there was only one language available, PAL, so there was no
confusion about which to use. I can see that there is perhaps too much
choice for Kexi. May I suggest that if calculated fields had been
included right from the start, which IMHO they should have been, then
the choice of language could also have been set. A bit too late now?

Finally, before I shut up, may I stress that, in my example above, the
form is a normal form based on linked tables. It is not based on a
query. It would be used to create new records and to view records
already on file. And finally, as said before, I created many databases
and only one did not have any calculated fields.

Sorry to go on so long,

Neil