Basic calculation in a editable grid

Applications Forms
#1

Perhaps it is a simple question, but it keeps me busy.
I have an editable grid, basically 3 fields. After the user had inserted the data and pressed the update bottom the total should be displayed in a forth field.
field_1 + field_2 + field_3 = field_4
field_ 4 is a read only field, but depends on the input of the others, the sum should be displayed after the update bottom was pressed.
Any ideas?
thx Juergen

#2

Re: Basic calculation in a editable grid

Try playing around with the ->SQL statements for the grid PDF reports

Here’s a simplified example of SUM and AVG values that you calculate during almost any data lookup phase (in the SQL code dialog for a grid/pdf) instead of performing the calculations in the browser.
[size=8pt]Note: The SQL statements below are for example/idea only, showing how to do simple math in the SQL statement. (substitute the ROUND() function with your own sql math operations) (substitute any [variable] calls with your own variables or hard-coded statements)[/size]

The simplest way in 1 SQL statement is:
(table fields = id,value1,value2)

SELECT id,value1,value2,ROUND(value1 + value2) AS customfield WHERE id = [your_id]

A more complex example:
Data can come from more than 1 table, but the field count and naming must be identical, but you can use UNION queries while naming fields from different tables to be the same name for result dataset

Scenerio:
2 tables with same data structure, but each table represents a different branch office’s data
How to grab data from 2 tables for a single SubSelect in a PDF Report ??

SELECT id,value1,value2,value3,ROUND(sumfield),ROUND(avgfield)
FROM
(
select
   a.id AS id,
   a.value1 AS value1,
   a.value2 AS value2,
   a.value3 AS value3,
   ROUND(a.value1 + a.value2 + a.value3) as sumfield,
   ROUND((a.value1 + a.value2 + a.value3) / 3) as avgfield
FROM
   tablename1 a
WHERE a.id = [your_id]
UNION
select
   b.id AS id,
   b.value97 AS value1,
   b.value98 AS value2,
   b.value99 AS value3,
   ROUND(b.value1 + b.value2 + b.value3) as sumfield,
   ROUND((b.value1 + b.value2 + b.value3) / 3) as avgfield
FROM
   tablename2 b
WHERE b.id = [your_id]
) c

This is all probably overkill for your question, but I thought others might like the info.

Then you can get into multiple SubSelects, the first one to return datasets with SUM values on the right of each line/row, and a second SubSelect to return SUM values as the last record (this is done same as above, but the second SubSelect uses the outer-most SQL wrapper like:

SELECT sum(value1) AS value1,SUM(value2) AS value2,SUM(sumfield) AS sumtotal
FROM (
...(above select statement in here)...
) z
#3

Re: Basic calculation in a editable grid

If you are using a form (editable grid), the other thought would be to create field in the grid not tied to a table and create an AJAX function to update the 4th field.

Regards,
Scott.

#4

Re: Basic calculation in a editable grid

Thanks for the fast replies. However creating an SQL script is not the problem, I can do it :wink:
The issue is that I think in an development environment I simply want to know how to code that in scriptcase that the grid is doing what I want.
Very simple: user inserts the following data: Field1 $100, Field2 $200, Field3 $400. Now he press the update bottom, data are stored, the values in the fields are now in the database and (that is the target) in Field 4 the sum of $700 is displayed, Would be nice to have this data the database too. Field4 cannot be edited by the user directly. How can I do this with all the events AfterUpdate, BeforeUpdate etc. or macros or Ajax etc. So far I failed to get work.

Juergen