Alpha Video Training
Results 1 to 13 of 13

Thread: Use Rows to Calculate a Number Formula

  1. #1
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default 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. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default 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. #3
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default Re: Use Rows to Calculate a Number Formula

    Thanks for your reply:
    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. #4
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Lexington, KY
    Posts
    1,471

    Default 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
    Jay Talbott
    Lexington, KY


  5. #5
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default 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. #6
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default Re: Use Rows to Calculate a Number Formula

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

    :-)

  7. #7
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default 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. #8
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default 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. #9
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default Re: Use Rows to Calculate a Number Formula

    Thanks for your comments.

    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. #10
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default Re: Use Rows to Calculate a Number Formula

    Hey Ricky,

    Are you using DBF tables or SQL Server or MySQL?

  11. #11
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default Re: Use Rows to Calculate a Number Formula

    Hello I'm using DBF tables!

    Thanks!

  12. #12
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,819

    Default 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. #13
    Member RIckyRel's Avatar
    Real Name
    Ricky Relouw
    Join Date
    Dec 2009
    Location
    Australia, Vic, Ballarat
    Posts
    114

    Default 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.

Similar Threads

  1. How to calculate amount in repeating rows
    By Mike Reed in forum Application Server Version 11 - Web/Browser Applications
    Replies: 8
    Last Post: 11-14-2011, 08:42 PM
  2. Need Help with a formula to calculate a date
    By Jerry Gray in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 11-22-2008, 12:34 PM
  3. Calculate page number from report
    By James Instone in forum Alpha Five Version 5
    Replies: 7
    Last Post: 06-22-2005, 07:04 AM
  4. Function to Calculate week Number
    By crimmelcp in forum Alpha Five Version 6
    Replies: 4
    Last Post: 08-22-2004, 08:15 AM
  5. FORMULA TO CALCULATE WEEK OF THE YEAR
    By ken tjia in forum Alpha Five Version 4
    Replies: 2
    Last Post: 11-20-2002, 06:32 AM

Bookmarks

Posting Permissions

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