# Thread: Use Rows to Calculate a Number Formula

1. ## Use Rows to Calculate a Number Formula

Hello

I was hoping someone could point me in the right direction

I have a grid that has a date field and a person's Weight in KG

eg

Date 01/01/2012, Weight 84

Is there a way to calculate a new field that will show the Weight difference from one Row to the next inteh grid?
e.g
Date 01/01/2012, Weight 84
Date 01/01/2012, Weight 82 Difference loss 2kg
Date 01/01/2012, Weight 83 Difference gain 1kg
Etc:

Any ideas???

2. ## Re: Use Rows to Calculate a Number Formula

You bet... lots of ways. But, the best way for you may depend on how you're using the grid. Are you adding data to each row... e.g. your first row with a weight of 84... then saving... and then entering data into your second row? So you put in 82 and want the grid to calc. Then on to your third row, etc.

Or, are you grabbing all the rows and displaying a read only grid and would like the calcs to display as the rows are rendered?

Further, do you want to keep the data your calculating? Do you have, or do you want fields in your database to hold the new data?

What database are you using?

If you can explain how you may using the grid then the rest can be figured out.

Basically, it's enter data, then if row>1 and there's a Weight, then grab the data from row 1, and figure it all out for row 2.

3. ## Re: Use Rows to Calculate a Number Formula

I am entering data as I go, I have a grid that list a persons details and then have linked grid that diplays monthly weights, and a new weight can be added each month. (Or when Desired)

I would like to keep the weight difference data, I was planning on adding a field in same table the grid is running from:

Each Month a weight is added and then saved.

At the end of every three months I want to be able use the data to run reports E.g graph the Weight. But be able to capture and report on weight differences e.g. when loss if greater thatn eg 2kg, or there is conscecutive weight loss for three months:

I'm using the alpha five DBA

Hope this makse sense. Thanks

4. ## Re: Use Rows to Calculate a Number Formula

I think the best bet would be to use javascript to go through each row.
Here is the pseudo code, assuming that the most recent field is first, and the remaining ones are listed in reverse chronological order
determine how many rows are in the grid
get the first row's weight
get the next row's weight
subtract one from the other
divide the difference by the second row's weight and put that % change in the field in the first row
progress like this through the grid until you get to the last row, at which point put nothing in the field
commit the grid
This will not only do the most recent calculation, but it will also recalculate every row if you delete or modify a weight in the middle of the list.
Hope this helps.
Jay

5. ## Re: Use Rows to Calculate a Number Formula

This should get you started. I put this javascript into the onChange event of the Weight field.

Code:
```var currRow = {Grid.RowNumber};
var cntRows = {Grid.Object}._rowsInGrid;
var diffW = 0;
var prevRow = 0;
var wChg = '';
if (currRow == -1)
{
prevRow = cntRows;
}
else
{
prevRow = currRow-1;
}

if (prevRow > 0)
{
var prevW = {grid.Object}.getValue('G','WEIGHT',prevRow);
var currW = {grid.Object}.getValue('G','WEIGHT',currRow);
diffW = currW - prevW;
diffW = diffW.toFixed(2);
if (currW < prevW)
{
wChg = 'Loss';
}
if (currW === prevW)
{
wChg = 'No Change';
}
if (currW > prevW)
{
wChg = 'Gain';
}

{grid.Object}.setValue('G','WEIGHTDIFF',currRow,diffW);
{grid.Object}.setValue('G','WEIGHTCHG',currRow,wChg);
}```
Basically, if it's the first row then no calcs are needed.
If there are already rows, and you're adding a row, then that new row # is -1. Since a new row # is -1 we know that we're dealing with a new row and can do what we need to.
If you're just changing an existing value, then that's ok too.

However, if you're changing an existing value then you'll need to change everything that comes after that. The code for that isn't done, but it's straight forward - yell if you help on that one.

Hope it helps.

6. ## Re: Use Rows to Calculate a Number Formula

thanks, I will try this out tonight and let you know if I have problems...

:-)

7. ## Re: Use Rows to Calculate a Number Formula

Hi, Thought I would supply some feedback, thanks for your comments, but I am still lost:

I'm not usure where to put the code or how to get this to work:

I have a grid that the user enters a "date" ""weight" I have another field that i would like to calculate the weight difference for each row entry, but dont know where to go with entereing any code:

Grid:Example

DATE Weight Diff

01/01/2012 50kg 0
01/02/2012 60kg 10
01/03/2012 55kg -5
01/01/2012 50kg -5

If any can help me solve this, wodul be appreciated. I am a beginner and have no java script Knowledge

Thanks:

8. ## Re: Use Rows to Calculate a Number Formula

Hey Ricky,

In the Properties of your Weight field, there is a Javascript Event named onChange. Click the "..." button on this event and an "Edit onClick Event" form will open. At the top of this form there are radio buttons. Click the one labelled "Text Mode". In text mode, copy the code above and paste it into the Text Mode screen.

You're going to have to make a couple of changes to the code.

1. I use the field names "WEIGHT", "WEIGHTDIFF", and "WEIGHTCHG". You'll need to change these to the field names you are using.
2. I added a field named WEIGHTCHG to hold the text "Loss" or "Gain" or "No Change". You don't need to do this. For now you can comment out this line of code by putting 2 forward slashes at the beginning of the line

e.g. // {grid.Object}.setValue('G','WEIGHTCHG',currRow,wChg);

That should be it.

Another option would be for you to right click on your grid in the control panel and export it to a zip file. Then attach that file here in a message so we could have a look.

9. ## Re: Use Rows to Calculate a Number Formula

I have followed your instructions and I now have it up and runnning:

Thanks very much, it works a treat:

You also mentioned at one stage - if you're changing an existing value then you'll need to change everything that comes after that. The code for that isn't done, but it's straight forward - - Would this be hard to setup?

Thanks again.

10. ## Re: Use Rows to Calculate a Number Formula

Hey Ricky,

Are you using DBF tables or SQL Server or MySQL?

11. ## Re: Use Rows to Calculate a Number Formula

Hello I'm using DBF tables!

Thanks!

12. ## Re: Use Rows to Calculate a Number Formula

Hey Ricky,

Try this... I think this may be the best way. Overall, here's the problem. You have x number of records displayed in a grid. But, you have x+n records in the database for that person. That means you've got 2 or more pages of data ready to be displayed. If you change a record on the last row of the current page, then the first row of the next page needs to be updated. In this case, we can't use Javascript to get the job done (at least I don't think so). Is it correct in saying that ONLY the next record needs to be updated... no other following records... I think I have that right.

So, we have to drop down into the data, using an Ajax callback, to open the DBF table, find the record we're looking for, update it, and then refresh the grid.

Go back into the onChange event of your Weight control. Add the following as the last line of code...

Code:
`{grid.Object}.ajaxCallback('G','{Grid.RowNumber}:all','recalcweight','','');`
Close this, and, back in the Grid Properties and at the left side you'll see Code, XBasic Functions. Click on XBasic Functions and enter the following into the code pane...

Code:
```function recalcWeight as c (e as p)

dim tbl as p
dim qry as p
dim records as n
dim filter_flags as c
dim filter_filter as c
dim filter_order as c
dim currWeight as n
dim currDiff as n

currWeight = e._currentRowDataNew.Weight

tbl = table.open("tblWeightTrack")

query_filter = "WeightDate > {" + e._currentRowDataNew.WeightDate + "}"
query_flags = ""
query_order = "WeightDate"
qry = tbl.query_create(query_flags, query_filter, query_order)
records = qry.records_get()
If records = 0 then
'No records found... don't do anything
'recalcWeight = "alert('No more rows to update');"
query.filter = ""
query.order = ""
tbl.close()
else
tbl.fetch_first()
tbl.change_begin()
currDiff = tbl.weight - currWeight
select
case currDiff = 0
tbl.weightchg = "No Change"
case currDiff > 0
tbl.weightchg = "Gain"
case currDiff < 0
tbl.weightchg = "Loss"
end select
tbl.weightdiff = currDiff
currWeight = currWeight - currDiff
tbl.change_end()

recalcWeight = "{grid.Object}.submitGridPart();setTimeout('{grid.Object}.refresh()',200);"
query.filter = ""
query.order = ""
tbl.close()
end if

end function```
You'll need to change your DBF table name and field names where appropriate.

You'll see one of the last lines of code here is

recalcWeight = "{grid.Object}.submitGridPart();setTimeout('{grid.Object}.refresh()',200);"

We're sending back two commands to the browser... one to submit the changes to the database, and the other to refresh the grid. However, all this happens so fast that we have to slow down the refresh with a setTimeout. You can play with the amount of time... changing it from 200 to 100 or 300 etc. to see what works best for you. If it's happening too fast then you'll get a message from Alpha that the changes must first be saved or discarded.

13. ## Re: Use Rows to Calculate a Number Formula

Tnaks for yoru help, I will give it a try and let you know how I go..

Cheers for now.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•