How to write other fields amount to a single field

Home | Site map

HowTo: Write other fields amount to a single field

Frequently, in addition to a simple data output, it may be necessary to perform different kinds of data manipulation, such as summation, calculation of simple average, etc.

Let's discuss a few concrete cases by the example of "Cars" table in test MS Access database (examples.mdb) that comes with ASPRunner Pro. Depending on complexity of solving problem you need to make some changes on Events tab, in SQL query or even in generated code.

1. Adding the field which calculate simple average of EpaCity and EpaHighway fields.

	Open ASPRunner/PHPRunner project and proceed to Edit SQL Query tab
	Select checkbox Edit SQL query manually
	In SQL query add string ([EPACity] + [EPAHighway])/2 as Expenses

Sample SQL Query

Select [ID],
[Make],
[Model],
[YearOfMake],
[Picture],
[Horsepower],
[EPACity],
[EPAHighway],
([EPACity] + [EPAHighway])/2 as Expenses,
[Price],
[Date Listed],
[Phone #],
[UserID],
[Tax]
From [Cars]

	Proceed to Choose fields tab
	For Expenses field unselect following checklists: Add, Edit
	Build your project and view results in browser

For next two items you should modify your database for a bit. Add to Cars table numeric field Tax. It's important, that this field should be able to hold fractional values.

2. Adding the field which calculate imposed tax after editing or adding a new record.

	Double click on Before Record Updated Event
	Choose from one of the predefined actions Custom Code and click Ok

Modify sample event code by putting following code snippets to appropriate place

ASPRunner Example

dict("Tax") = 0.01*dict("Price") + 2*dict("Horsepower")

PHPRunner Example

$values["Tax"] = 0.01*$values["Price"] + 2*$values["Horsepower"];

Make the same for Before Record Added Event

3. Adding the field which immediately calculate imposed tax when Price or Horsepower fields are edtiting.

	Build your ASPRunner/PHPRunner project and view generated files in Windows Explorer
	Open with text editor cars_aspfunctions.asp/commonfunctions.php file which located in Output directory in Include folder
	Go to Function BuildEditControl and find the following code snippets:

Replace them with:

ASPRunner Example

if fformat=EDIT_FORMAT_TEXT_FIELD or fformat=EDIT_FORMAT_TIME then
if IsDateFieldType(ttype) then
   response.Write "<input type=""hidden"" name=""" & ctype & """ value=""date" &_
     EDIT_DATE_SIMPLE & """>" & GetDateEdit(field,value,0,secondfield,edit)
else
   if ((field=="Price" or field=="Horsepower") and $strTableName == "cars") then
     stronchange="onchange=""javascript:editform.value_Tax.value = editform.value_Price.value" &_
     " * 0.01 + editform.value_Horsepower.value*2;"""
   end if
   response.Write "<input type=""text"" name=""" & cfield & """ " & GetEditParams(field,"") &_
     " value=""" & my_htmlspecialchars(value) & """" & stronchange & ">"
end if

PHPRunner Example

if($format==EDIT_FORMAT_TEXT_FIELD || $format==EDIT_FORMAT_TIME)
{
if(IsDateFieldType($type))
echo '<input type="hidden" name="'.$ctype.'" value="date'.EDIT_DATE_SIMPLE.'">'
.GetDateEdit($field,$value,0,$secondfield,$edit);
else
{
if (($field=="Price" || $field=="Horsepower") && $strTableName == "cars")
$stronchange="onchange=\"javascript:editform.value_Tax.value = editform.value_Price.value".
" * 0.01 + editform.value_Horsepower.value*2;\"";
echo '<input type="text" name="'.$cfield.'" '.GetEditParams($field).' value="'.
htmlspecialchars($value). '"'.$stronchange.'>';
}
}

Now you can edit or add new records and every time when you make changes in Horsepower or Price field correspondingly Tax field has changed. It's very comfortable 'cause right away you see imposed tax and can modify price depending on it.

Note: Changes in SQL Query maybe different according to database used. For example, for MSSQL, MySQL, Oracle and Postgre SQL databases you should use Upper operator instead of Ucase.

Applies to:
ASPRunner Pro
PHPRunner

Home | Products | Downloads | Support | Contacts

All comments send to webmaster@xlinesoft.com