Math operator & count in grid application - mysql > anybody else faced this?

Scriptcase 9 General
#1

Hello, it has been a while since last used scriptcase, lately had an idea wanted to make a small project to test it out, however, as it involves many math operators, I’m kind of stuck in couple of points, thought to post them here for possibility if something similar stuff…

1- in grid application, assume i have a calculated_field called {field_x} created by scriptcase that gets data by combining 2 piece of data from col A and col B (which are DB fields) results as field X = ‘XXMB1’ ‘XXMB2’, ‘XXMB3’ … in total, like 60 or 70 values repeated over a long grid full of raws…

example:

Feld A …Field b … field X
BV … 1 … XXMB1
CV… 3 … XXMB3
TG … 4 … XXMB4
NB … 2 … XXMB2
SB … 1 … XXMB1

How to state, how many XXMB1 and XXM2… are there, is there a way to count, and output, say under the grid or get it as variable or something?.. I tried all built in total options like Count, Distinct Count… those basically give the row count, which is not what I want, i obviously look to have something like XXMB1 = 2 times, XXMB2 = 17 times, XXMB3 = 40 times… etc.

2- assume this mysql table:- presented by scriptcase grid application

sn … grade . class . marks . hours
1… 1…3…2…33
2…2…1…22…44
3…14…1…24…11
4…14…1…24…22
etc.

i need a new field, created by scriptcase type integer to calcuate the difference between hours in row1 and hours in row2 so we have something like

sn … grade . class . marks …hours… different
1… 1…3…2…33… … NA (its the first row)
2…2…1…22…44 … 10 the difference between row 2 and 1/above) - field {hours}
3…14…1…24…11 … 33- the difference between row 3 and 2/above) - field {hours}
4…14…1…24…22… 11 (the difference between row 4 and 3/above) - field {hours}
etc.

Last over here, it is obvious you can’t set sorting or group by (static) using calculated fields (fields created by scrtiptcase) only database fields… anybody got a workaround?

Appreciate your thoughts and inputs

Cheers

#2

Hello Mike.

(1)

I think you’reright, you couldn’t create groups or sort by calculated fields, so the option is to code the counting.

Maybe you can use the OnInitScript to set an empty PHP array, and then use the OnRecord evento to check count, something like this:

Is the value for this record in the array? Increment the count.
Is not in the array? Create the item and put his counter to 1.

But this has limitations: the OnRecord event triggers when you get that record. I believe (not testes) that you need your grid to show all record for the count to be correct.

So, maybe a solution will be to create a view in your database that calculate the FieldX value, and then you could use standar Grouping in your grid, without modifying the original table.

(2)
What you need is to substract the previous record hours from the current one. You could use the OnRecord event to save the current value and use it in the next record, something like this:

{Difference}=[previousValue]-{hours};
[previousValue]={hours}

Just set [previousValue] to ‘NA’ in your onInitScript.

Be carefull if your grid spans over several pages, it may require some refinements.

Regards,

#3

Great! second one works, i can see the difference between 2 fields in the same columns in a new column…

However for first part, where I want to count the times of each value I didn’t quite understand your method, or how to use an array for that… i will do some tests but can you please elaborate more.

Thanks a lot.

#4

Update: never mind, i passed the count through init event as you said, but not as an array, since i have 6 distinct values then i counted each one of them and onRecord incremeanted the value then pulled the result to the toolbar as a globalVariable… it is not ideal but it works for what i wanted… thanks again

#5

I always prefer to do this stuff in SQL. For me, is easier to build, debug and visualize the data composition in that way. The I usually buis a view based on the SQL, and then, create a scriptcase over the view