Article: AN0001920Updated:
In some cases we need to calculate a value based on values in other columns or in other records.
Example: Total server price
In the below example we have class server with several basic configuration parameters (vm_cpu, vm_ram a vm_hdd) and reference to a pricelist, that contains unit price for these parameters. We need to ensure that the column total_price contains the total server price according to its configuration and defined pricelist (the pricelist reflects e.g. various service level that we are providing to the server).
![](../ImagesData/Help-cs-CZ/0195.png)
We have to ensure that the total price of the server is calculated and stored in column total_price when a (new or existing) record is saved. We will create a rule of type Script Before record saving.
Where to use this script: Class rule
var drPL = OGActualDataRow.GetDR('pricelist');
if ( drPL != null)
{
var vm_cpu = OGActualDataRow['vm_cpu'];
var vm_ram = OGActualDataRow['vm_ram'];
var vm_hdd = OGActualDataRow['vm_hdd'];
var pl_cpu = drPL['price_cpu'];
var pl_ram = drPL['price_ram'];
var pl_hdd = drPL['price_hdd'];
if ( vm_cpu == null) vm_cpu = 0;
if ( vm_ram == null) vm_ram = 0;
if ( vm_hdd == null) vm_hdd = 0;
if ( pl_cpu == null) pl_cpu = 0;
if ( pl_ram == null) pl_ram = 0;
if ( pl_hdd == null) pl_hdd = 0;
OGActualDataRow['total_price'] = vm_cpu * pl_cpu + vm_ram * pl_ram + vm_hdd * pl_hdd;
}
else
{
OGActualDataRow['total_price'] = null;
}
Rule from the above example updates the record before its saving from the web form. When we update the data in a mass way, we have to use SQL update.
Where to use this script: Import - Script after import end
var m = OG.Model.GetByCode('it');
var clP = m.ClassDefs['server'];
var clPL = m.ClassDefs['pricelist'];
var colPvmcpu = clP.Columns['vm_cpu'];
var colPvmhdd = clP.Columns['vm_hdd'];
var colPvmram = clP.Columns['vm_ram'];
var colPtotal = clP.Columns['total_price'];
var colPprice = clP.Columns['pricelist'];
var colPLpricecpu = clPL.Columns['price_cpu'];
var colPLpricehdd = clPL.Columns['price_hdd'];
var colPLpriceram = clPL.Columns['price_ram'];
var sql = 'update ' + clP.DBTableName +
' set ' + colPtotal.DBColumnName + ' = isnull(p.' + colPvmcpu.DBColumnName + ',0)*isnull(pl.' + colPLpricecpu.DBColumnName + ',0) + isnull(p.' + colPvmram.DBColumnName + ',0)*isnull(pl.' + colPLpriceram.DBColumnName + ',0) +
isnull(p.' + colPvmhdd.DBColumnName + ',0)*isnull(pl.' + colPLpricehdd.DBColumnName + ',0)' +
' from ' + clPL.DBTableName + ' pl join ' + clP.DBTableName + ' p on p.' + colPprice.DBColumnName + ' = pl.id';
OG.Sql.RunSql(sql);