New Calculated field for each row.

Applications Grid / Reports
#1

Hello SC users,

First of all, please note that I’m pretty new to all of this.

Right now our company has a working application using a MySQL database and I’m trying to create reports for this app as a separate app. I’m using Grid.

The table I have is as follows: ID, value1, value2, value3 etc…

What Im trying to do is add a calculated field at the end of the table, that will be showing (value1 + value2 - value3), for each row - its own value.

So what I did so far is: I’ve created a new field “total” and entered the following sql statement for it:

SELECT value1 + value2 - value3
FROM mytable

what it does - it shows the correct calculation but on each row it puts this calculation for all rows. (a list of all of them in one field)

Example: Lets say I have 3 records in mytable, using the SELECT value1 + value2 - value3 FROM mytable I get the following:

[table=“width: 500”]
[tr]
[td]ID[/td]
[td]value1[/td]
[td]value2[/td]
[td]value3[/td]
[td]total[/td]
[/tr]
[tr]
[td]1[/td]
[td]25[/td]
[td]30[/td]
[td]5[/td]
[td]50[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]45[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]40[/td]
[/tr]
[tr]
[td]2[/td]
[td]35[/td]
[td]20[/td]
[td]10[/td]
[td]50[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]45[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]40[/td]
[/tr]
[tr]
[td]3[/td]
[td]45[/td]
[td]35[/td]
[td]40[/td]
[td]50[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]45[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]40[/td]
[/tr]
[/table]

and I obviously want it to be like that:

[table=“width: 500”]
[tr]
[td]ID[/td]
[td]value1[/td]
[td]value2[/td]
[td]value3[/td]
[td]total[/td]
[/tr]
[tr]
[td]1[/td]
[td]25[/td]
[td]30[/td]
[td]5[/td]
[td]50[/td]
[/tr]
[tr]
[td]2[/td]
[td]35[/td]
[td]20[/td]
[td]10[/td]
[td]45[/td]
[/tr]
[tr]
[td]3[/td]
[td]45[/td]
[td]35[/td]
[td]40[/td]
[td]40[/td]
[/tr]
[/table]

Please advise. Im on SC version 8.

#2

You do not need to bother with SQL for that.

Use your “total” custom field, but do not assign it any SQL or anything. Instead, in the grid’s OnRecord event add PHP:

{total} = {value1} + {value2} - {value3};

This will for each record take that record’s field values and and perform that calculation.

#3

[QUOTE=adz1111;26414]You do not need to bother with SQL for that.

Use your “total” custom field, but do not assign it any SQL or anything. Instead, in the grid’s OnRecord event add PHP:

{total} = {value1} + {value2} - {value3};

This will for each record take that record’s field values and and perform that calculation.[/QUOTE]

Thank you very much! It worked like a charm!

I’m on the trial period for 2 days only and I already have full customizable reports for our DB! And I’m yet to dive into any other features of ScriptCase. It’s an amazing piece of software! Im definitely buying it.

#4

Let us know if you need anything elese! We are glad that you are enjoying Scriptcase.

#5

You are most welcome - just as an extra clarification - note that you wrap variables in {curly brackets} to tell SC to use the values in that app’s fields (as listed under the Fields tree item on the left).

If something is in [square brackets] it’s a global variable, whilst $variables are local to that event

#6

Hi Ruslan,
I got a similar problem on V8
I have added a simple formula {TotalRow} = ({UnitPrice} * {Quantity}); on Ajax Events of Quantity_OnChange in order to refresh the total once the user change quantity field.
UnitPrice and Quantity are decimal
The total now is 100 times the correct calculation … I’m really stucked here.
Did you understand what to do to have a right calculation ??
Thanks

#7

What type is TotalRow?

Plus, for all 3 fields, check the fields’ General settings | Values Format… these can affect how the number is represented. Pic below is for Decimals too, to 2 decimal places…

Note: Maximum size of 5 allows “23.75” (incl decimal point).

Capture.JPG

#8

Ok Thanks - Sorry for duplication but I was in great … hurry !! ;-))

#9

:slight_smile: understood