>Hello all,
>
>I have a cursor with 4 fields - c_id, c_formula, n_amount, n_calculated_amount
>In this cursor, either c_form or n_amount is populated.
>c_id - character id (c1, c2….);
>n_amount – numeric field;
>c_formula - might be like the following
>c1+c3+c5
>or more complex: (c1*2)/c3 - could be any math operations,
>or could be sum(c7:c12) – sort of excel format.
>
>The formulas can be complicated expressions, e.g. c5 may contain reference to c1, c7, c12, where c12 is also a formula .
>
>The questions is how to populate n_calculated_amount on the fly.

If I understand you correctly you'll need 2 components:

* 1. A function that calculates the value of a given row in the cursor i.e. c1:
FUNCTION GetRowValue( tc_ID )

LOCAL ;
  lnReturnValue ;
  , ln_amount ;
  , lc_formula ;
  , lc_formula_expanded

* Get the n_amount and c_formula values for the passed c_id:
SELECT ;
  n_amount ;
  , c_formula ;
  FROM MyCursor ;
  WHERE c_id == tcid ;
  INTO CURSOR Temp

IF _TALLY > 0
  * Store the cursor values to local variables. This is important
  * because we may be calling this routine recursively:
  ln_amount = Temp.n_amount
  lc_formula = Temp.c_formula

  USE IN Temp

  * We found the row corresponding to the passed c_id:
  IF NOT EMPTY( ln_amount )
    * n_amount value is populated, simply return that value:
    lnReturnValue = ln_amount

  ELSE
    * n_amount is empty, check the c_formula value:
    IF NOT EMPTY( lc_formula )
      * We have a formula that we have to evaluate. The challenge, as you point out,
      * is that the formula may refer to the values of other rows in the same table.
      * Actually, we can do this with 2 lines of code, but you're going to have to flesh
      * out the first one yourself:
      lc_formula_expanded = GetExpandedFormula( lc_formula )
      * for what GetExpandedFormula() does, see below.

      * Now, we just use the VFP EVALUATE() function to calculate the value of the expanded formula:
      lnReturnValue = EVALUATE( lc_formula_expanded )

    ELSE
      * c_formula is empty as well, return 0:
      lnReturnValue = 0

    ENDIF

  ENDIF

ELSE
  * Passed c_id value not found, return 0:
  lnReturnValue = 0

  USE IN Temp

ENDIF

RETURN lnReturnValue
***************************************
* 2. A function that expands a c_formula value, wrapping its references to other rows
*    to get their values
FUNCTION GetExpandedFormula( tc_formula )

* The purpose of this function is to:
* 1. Wrap any references to other cells in a GetRowValue() function call, which will return its value
* 2. Convert any non-VFP syntax into something VFP can understand

* You need to write this function yourself as we have no idea how complicated your formulae will be.

* In general terms you need to parse the passed tc_formula value (a character string),
* and return a modified character string. To use your examples:

* If the formula passed is "c1 + c3 + c5", then 
lcReturnValue = "GetRowValue( 'c1' ) + GetRowValue( 'c3' ) + GetRowValue( 'c5' )"

* If the formula passed is "(c1*2)/c3"
lcReturnValue = "( GetRowValue( 'c1' ) * 2 ) / GetRowValue( 'c3' )"

* If the formula passed is "sum(c7:c12)", this syntax for SUM is not correct for VFP so you would need
* to expand it to add the individual values, so
lcReturnValue = "GetRowValue( 'c7' ) + GetRowValue( 'c8' ) + GetRowValue( 'c9' )" ;
  + " + GetRowValue( 'c10' ) + GetRowValue( 'c11' ) + GetRowValue( 'c12' )"

* For something like the sum of range above you could make it more general by
* writing a separate function that takes just the first and last row ids as parameters
* and returns the sum:
lcReturnValue = "GetRangeSum( 7, 12 )"

RETURN lcReturnValue
***************************************
FUNCTION GetRangeSum( tn_id1, tn_id2 )

LOCAL ;
  lnReturnValue ;
  , lnIx ;
  , lcCurrentRow

lnReturnValue = 0

FOR lnIx = tn_id1 TO tn_id2 STEP 1
  lcCurrentRow = "c" + LTRIM( STR( lnIx ) )

  lnReturnValue = lnReturnValue + GetRowValue( lcCurrentRow )

ENDFOR

RETURN lnReturnValue

Finally, when all the above is coded, updating your cursor values is as simple as

REPLACE ALL ;
  n_calculated_amount WITH GetRowValue( c_id )

One important thing to be aware of if you go this route is that the EVALUATE() function call in GetRowValue() could easily end up containing other call(s) to GetRowValue(). So, GetRowValue() effectively calls itself (recursive calling). This is a good approach for handling a linked list such as you have here. You must avoid circular references (as Excel calls them), e.g. a formula in c1 referencing c1, or one in c1 referencing c2 while c2 references c1 etc. This will cause VFP to exceed its maximum function nesting depth and spit out an error. When coding any functions that may be called within a recursive routine you need to be very careful to explicitly keep all your variables LOCAL, and you may need to close intermediate cursors so they don't get overwritten by a recursive call.

If you go this route, you'll end up writing routines in GetExpandedFormula() to parse all the possible functions that may appear in a c_formula. Essentially, you're partway into writing a spreadsheet application.

Rather than doing that, one completely different approach would be to use Excel and build a custom spreadsheet on the fly, and just get Excel to recalculate it for you. This would be a good approach if the formulae in c_formula actually are Excel formulae. In general, within VFP you could:

- open an Excel application object
- create a new worksheet
- populate cells c1, c2, c3 etc. with their n_amount or c_formula values, as appropriate
- get Excel to recalc the worksheet
- read the resultant value(s) from various cell(s)

If this might work for you there are various Excel automation gurus here who can help you out.