Custom component property value handling at run time

[Former Member]

I would like to develop simple custom component which has three properties PROP1, PROP2 & RESULT . These parameters are bound to excel sheet cells as follows..

cell A1 connected to PROP1

cell A2 connected to PROP2 and

cell A3 connected to RESULT.

In excel A1 cell has excel formula set as =IF(A2<5,10,0).

Custom component would be simple button display at runtime and on click of the same, I would like to perform following actions in sequence with N number of times.

1. First push any random number to PROP2. (which is simple task, I believe)

2. Pick new value of PROP1 which is changed due to existance of excel formula (+ using dispatchevent I could able to pass value to excel cell A2. But not able to read new value of PROP1+)

3. check new PROP1 value equal to 10. If it is 10 then count it otherwise again push new random value to PROP2.

4. Continue step 1 to 3 till N number of times. and finally

4. Push number of counts for PROP1 value 10 to RESULT parameter.

All the parameters are bindable (getter/setter with [Bindable]) in Flex component application and flex propertysheet application use binding ID to bind with cell address. with binding direction BOTH and SINGLETON

Here is flex function code which triggered at button click.

public function test002():void {

while ( N < 100)

{

this.PROP2 = Math.round(Math.random()*1000);

dispatchEvent(new PropertyChangeEvent(PropertyChangeEvent.PROPERTY_CHANGE, false, false, PropertyChangeEventKind.UPDATE, "PROP2", null, PROP2, this));

if (this.PROP1 == 10)

{

cnt++;

}

}

this.RESULT = cnt;

}

My observations are as follows.

- new push of value to PROP2 get reflected to excel A2 when button click event is over, not at the time of while loop process execution.

- Also I observed PROP1 value never changes and it is the value which is recorded just before button click event of component at run time and remain the same till button click event is over.

- Further to this I notice as click event is over, new value of PROP2 reflected in cell A2 and change value (if it passes IF formula of excel ) in A1 as well. Next button click action takes these new A1 and A2 values in consideration and process the function "test002".

I would like to understand possibility of custom component event execution process, which can able to pass as well as read value from component parameters to underlying excel with changes due to excel formula existance. If it is not limitations (If so) what is the set of commands which can be helpful to achieve this. I appreciate if anybody explain typical event execution required in such scenarios with some actionscript code directions.

Thanks

Sandesh

Edited by: Sandesh Darne on Feb 8, 2010 11:48 AM

Edited by: Sandesh Darne on Feb 10, 2010 5:23 AM

[Former Member · Accepted solution]

Hi Sandesh,

Can you write this again without the code and with some spacing as it is very difficult to read...

Regards

Matt

[Former Member · Accepted solution]

Hi.. Matt Lloyd,

Here is situation. I am working on custom component development which has specific task to perform in dashboard. Let me explain scenario here.

Custom component has three properties PROP1, PROP2 & RESULT . These parameters are bound to excel sheet cells as follows..

cell A1 connected to PROP1

cell A2 connected to PROP2 and

cell A3 connected to RESULT.

In excel A1 cell has excel formula set as u201C=IF(A2<5,10,0)u201D. Custom component would be simple button display at runtime and on click of the same, I would like to perform following execution steps in sequence for 100 times. These actions should happen in one life cycle of click event.

1. First push any random number to PROP2. (which is simple task, I believe)

2. Pick new value of PROP1 which is changed due to existence of excel formula

3. check new PROP1 value equal to 10. If it is 10 then count it, otherwise goto step 1

After 100 iterations push number of count to RESULT parameter.

All the parameters are u201Cbindableu201D (getter/setter with [[Bindable]]) in Flex component application. And in flex propertysheet application, I used standard binding techniques-- that is binding ID to bind with cell address and with binding direction BOTH and SINGLETON.

My observations are as follows.

new push of random value to PROP2 get reflected to excel A2 after button click event is over, not at the time of WHILE loop process execution. Further to this, I observed as click event start PROP1 value is picked from excel A1 and never changes in WHILE loop. It remains the same till button click event is over.

Further to this, I notice as click event is over, new value of PROP2 reflected in cell A2 and change value (due to existence of excel formula u201C IFu201D ) in A1 as well. Next button click event action takes these new A1 and A2 values in consideration and process the function "test002".

Here is flex function code which triggered at component button click event.

refreshProperty() raise dispatch event on PropertyChangeEvent of PROP2 in below code.

public function test002():void
{
var cnt:int; var N:int = 0
while ( N < 100){
this.PROP2 = Math.round(Math.random()*1000);
refreshProperty();
if (this.PROP1 == 10) 
{
cnt++;
}
N++
}
this.RESULT = cnt;
}

I am currently looking for Xcelsius SDK coding directions for reading and writing values from excel cell to component parameters u2014 repeatedly u2014 in one life cycle of user interface event.

[Former Member · Accepted solution]

Hi Sandesh,

First off, if you are ONLY writing OR reading data to/from a binded cell, the direction shouldn't be BOTH. This applies to PROP1, PROP2, RESULT.

To achieve this while loop with the bindings and Excel included, i would suggest to "send the process around manually" instead of using while. Please see the below code. It is just to demonstrate what i mean "send the process around manually", haven't tested it syntactically.

(N should be global)

public function helper()
{
   N++;
   if (N < 100)
   {
      this.PROP2 = Math.round(Math.random()*1000);
     refreshProperty();
   }
  else if (N = 100)  
  {
      this.RESULT = cnt;
   }
}

public function get PROP1(value:int):void
{
    if (N <= 100)
    {
         if (value < 5)
         {
              cnt++;
         }
         helper();
    }
}

I know your question is just an example, but could you please explain why are you using this over-complicated method with the Excel calculation? Instead of:

public function test002():void
{
	var cnt:int; var N:int = 0
	while ( N < 100)
	{
		if (Math.round(Math.random()*1000) < 5) 
		{
			cnt++;
		}
		N++
	}
	this.RESULT = cnt;
}

Regards, Marton

[Former Member · Accepted solution]

Matt,

Appreciate your quick response. Let me share my thoughts here about this typical custom component which I am trying to develop. Component will do predictive analysis using Monte Carlo method. Wherein random numbers are used to evaluate predefined formulas for predications. User will have liberty of business formula setting in excel at design time of dashboard. However, per Monte Carlo method these formulas are evaluated repeatedly with random numbers as one of the element of formulau2014for example, Market demand of product. Result of each evaluation will be average out to show optimal or best range of values. Idea is end user will perform such simulation run on the dashboard to get feel of forecast for business values for further decision making process.

Let me simplify further here with simple business formula example.

profit = revenue - prodution cost.

revenue = sales qty * price

production cost = produced qty * production cost

Evaluate the "profit" formula number of times ( may in thousand times ) with random sales qty. Each evaluation of formula result --profit -- will be average-out for better predictability.

To achieve above, business formula should be with excel component only so that user can apply any formula. Custom component need to only identify which formula element (technically which cell of excel) is variable and use further for simulation. While doing simulation, I am expecting custom component needs to keep feeding random number to variable ( nothing but identified cell) and excel should execute formula and return result back to custom component. custom component will read result and store in internal array for average calculations.

Code suggested by you is more on hardcoding formula inside the custom component. I am refering getter function of PROP2 here. I think, by doing hardcoding inside custom component, we make component situation specific and can not be used as generic one.

Hope the above thoughts clarifies my need. If you have any further queries or need clarification, I am happy to provide. I once again thanks for your suggestion and looking further to make this component successful.

Sandesh

[Former Member · Accepted solution]

Hi Sandesh,

I am not sure I would recommend changing the spreadsheet data that often as you may experience performance issues...

But if you really want to do it (again not recommended) and I have not tried this but you may need to look at callLater to give the spreadsheet time to detect the changes in between each of your loops.

Test it in Flex first of all by displaying the in a Label to see it changing...

http://blog.martinlegris.com/2009/05/27/tutorial-implementing-your-own-calllater-function-is-as3-fla...

Regards

Matt

[Former Member · Accepted solution]

Thank you for the explanation, it makes sense to keep the flexibility for the user.

For easier understanding, i will rename the properties in your example as:

PROP1: CalculationResult

PROP2: RandomNumber

My solution would be:

 
// onclick event calls randomGenerator

public function randomGenerator()
{
  N++;
  if (N < 100)
  {
     // set random in Excel (RandomNumber is a bound property)
     this.RandomNumber = Math.round(Math.random()*1000);
  }
  else if (N = 100)  // return the result
  {
      this.RESULT = sum;
  }
}

// In excel the formula calculates a new result, and will trigger 
// the set function for the CalculationResult property.
// In my previous post, i called this get by mistake.
 
public function set CalculationResult(value:int):void
{
   sum = sum + value; // this can be enhanced by allowing average calculation
   calculationResultChanged = true;
   invalidateProperties();
}

override protected function commitProperties():void
{
   super.commitProperties();

   if (calculationResultChanged == true)
   {
        // start the cycle again by generating a new random number, 
        // or write out the result
        randomGenerator();
   } 
}

Hope it helps, good luck with the component!

Regards, Marton

[Former Member · Accepted solution]

Marton,

I have tried your solution. It works well immediately after xcelsius dashboard get displayed or preview. It does not wait click event to happen. So I modified further by adding one switch ( "switch001" in code piece shown below) to control iteration by click event. That stops running of iteration immediate after dashboard display.

In order to see iteration I have to click twice on button. First click does send "frm" parameter value to excel and excel formula operates. The excel formula result send back to component via SETTER of "frmResult" . But it stops over there. If I click second time the iteration starts for 100 times.

Also I try to export this to PDF. PDF file get generated. If I try to open PDF file it raise error.

My code is attached here.

"frm" is the parameter that sends random number to excel.

"frmResult" parameter receives excel formula output and send it back to component.

"test()" function operate on button click event and set switch001 and then calls "Calc" function for iterations.

MXML code..........

<mx:VBox>

<mx:TextInput id="frmResultLabel" width="100" />

<mx:Button label="test..." click="test()"/>

</mx:VBox>

Actionscript code

public var cnt:int;

public var N:int = 0;

public var sum:int = 0;

public var switch001:Boolean = false;

public function test():void {

N = 0;

sum = 0;

switch001 = true;

}

public function Calc():void {

if (switch001 == true)

{

if (N < 100)

{

this.frm = Math.round(Math.random() * 1000);

}

else if (N == 100) // return the result

{

this.frmResult = sum;

// Label component use to display frm and frmResult values at runtime

frmResultLabel.text = sum.toString() + ">" + this.frm + ">" + this.frmResult;

}

N++;

}

}

[Bindable]

private var _frm:int;

private var _frmChanged:Boolean = true;

[Bindable]

public function get frm():int

{

return _frm;

}

public function set frm(value:int):void

{

//if (value == null) value = 0;

if (_frm != value)

{

_frm = value;

_frmChanged = true;

invalidateProperties();

}

}

override protected function commitProperties():void

{

super.commitProperties();

if (_frmResultChanged == true)

{

// start the cycle again by generating a new random number,

// or write out the result

Calc();

}

}

[Former Member · Accepted solution]

Marton,

Here is rest part of code.

[Bindable]

private var _frmResult:int;

private var _frmResultChanged:Boolean = true;

[Bindable]

public function get frmResult():int

{

return _frmResult;

}

public function set frmResult(value:int):void

{

sum = sum + value;

_frmResultChanged = true;

invalidateProperties();

}

[Former Member · Accepted solution]

Hi,

I have tried your solution. It works well immediately after xcelsius dashboard get displayed or preview. It does not wait click event to happen.

...
public var N:int = 0;
...

I didn't include the variable definition part, and in your implementation this was the bug. This is the reason why your loop started right at startup. At the set frm(); at startup it got into the loop and run until N reaches 100. To resolve it you should initialize N with a value over 100.

Another comment: you need a helper an initiator function like test(), but the switch001 varable is not necessary in my opinion. Just set N and sum to 0 and call the random generator (Calc()).

Regards, Marton

[Former Member · Accepted solution]

Dear Marton,

Sorry for delay in response. I was in deep of making final design of component. But I must appreciate your interest and timely solutions provided to shape my component. I will be in touch with you once again to share final component.

Thanks once again.

With this I am closing this thread here.