Multiply 2 fields and write and show the result in a third field

[Volker Löwer]

Hi all,

we are just trying to implement a multiplication of 2 fields from one table.

amount (int) * price (int) = result (int)

or

amount (dec) * price (dec) = result (dec)

The result should be written to a 3rd field in the same database table and displayed in the display view.

Although we found some code examples here it is not clear to us how it works.

Can anybody give some advice or example?

Thanks in advance,

Volker

[Patrick Pliessnig]

Hi Volker

Please forgive me my comment here. But it is generally not a good idea to calculate a result from 2 fields and put the result in a 3rd field of the same table. Better is to calculate on the fly whenever you need it.

Having said that you have several methods you could use depending on your use case. Among them, these methods here could be of interest:

calculate on the fly with a nodb schema:

you need to know how to create sql queries.

You might encounter problems only if you include it in the 'default' view in the main schema.

1. you create a nodb_schema with a sql-query that does the calculation on the fly
   
2. you include this in the main schema like this:
   <view name="some_name" template="display" schema="nodb_schema"></view>
   

create a trigger in the db to calculate and store

you need to know how to create triggers in your database.

You use the calculated field in the normal way in SGS. But be aware, SGS does not know about this trigger.
So you need to document well.

1. create field 3 normally
2. create a trigger to do the calculation and store the result in field 3
   
3. use normally but take care to make field 3 non editable in SGS.
   

use a php class to implement a custom php function

you need to know how to program in PHP, how to define a class and how to define a static function within the class.

this receipe ensures that you have all the custom functions for a particular schema in a central place.

probably you need to test a little bit with $params to know how SGS fills them from the schema.

1. create a custom class in file mySchema.php.
   If your schema is called mySchema you could create a class like this:
   class mySchema {
      static function myFunction($id, $data, $params) {
        // to some stuff, programming and calculation, etc;
        // you can reference the fields like this: $data["field1"]
        // eg: $data["field3"] = $data["field1"] * $data["field2"];
        // to update the db you can use the function 'db_update'
        // this function is defined in functions.php class sys
        // but you can basically use any valid php method to store data in a db
       }
   }
   
2. include a trigger_edit tag in your schema like this:
   <table trigger_edit="mySchema::myFunction"></table>
   

Regards

Patrick

[Vlad 0304]

Hi Volker,

You can find many examples of triggers for PostgreSQL here:
https://severalnines.com/database-blog/postgresql-triggers-and-stored-function-basics

Using triggers has many advantages over external code. First, the database ensures data consistency: your result field changes each time you change the input values. In the case of external computing, when you receive operands at the same time, another process will change one of the fields. As a result, you can get 1 + 1 = 3. This is important when the operands are in different tables or are selected by several transactions.

Creating a trigger is not always a simple process, an error can block the insertion of new data into the table or distort it, but if you succeed, then this works better than other methods.

Regards

Vlad

пт, 15 лист. 2019 о 14:14 Volker Löwer <v.lo...@ifbcon.de> пише:

--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simple-groupwa...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/simple-groupware/dc936641-0a66-4978-b75d-e5324297e398%40googlegroups.com.

[Volker Löwer]

Dear Vlad,

thanks you for your help and advice.

It is just good to know there are still people around that are willing to help. This is just great ...

BTW we are running SG now for several years as our main office administration tool for almost everything and it is still the most comprehensive and flexible software we could find.

Thanks once again,

Cheers Volker

On Thursday, November 21, 2019 at 12:18:40 AM UTC+1, Vlad Zaritsky wrote:

Hi Volker,

You can find many examples of triggers for PostgreSQL here:
https://severalnines.com/database-blog/postgresql-triggers-and-stored-function-basics

Using triggers has many advantages over external code. First, the database ensures data consistency: your result field changes each time you change the input values. In the case of external computing, when you receive operands at the same time, another process will change one of the fields. As a result, you can get 1 + 1 = 3. This is important when the operands are in different tables or are selected by several transactions.

Creating a trigger is not always a simple process, an error can block the insertion of new data into the table or distort it, but if you succeed, then this works better than other methods.

Regards

Vlad

пт, 15 лист. 2019 о 14:14 Volker Löwer <v.l...@ifbcon.de> пише:

Hi all,

we are just trying to implement a multiplication of 2 fields from one table.

amount (int) * price (int) = result (int)

or

amount (dec) * price (dec) = result (dec)

The result should be written to a 3rd field in the same database table and displayed in the display view.

Although we found some code examples here it is not clear to us how it works.

Can anybody give some advice or example?

Thanks in advance,

Volker

--
You received this message because you are subscribed to the Google Groups "Simple Groupware" group.

To unsubscribe from this group and stop receiving emails from it, send an email to simple-g...@googlegroups.com.