Accessing cell dependencies

4.72K viewsScripting
0

Some while ago this question arose for QAPI: [url:dxe15gzr]http://www.quantrix.com/forums/showthread.php?t=492[/url:dxe15gzr]

[INDENT][quote="J Scott":dxe15gzr]I am working on a plugin that will perform “breakback” of a value across its preceding cells. For example, if a cell calculates the sum of 10 numbers, I will provide a way for the user to enter a new sum and have the sum be proportionally spread across the 10 preceding numbers that feed the sum.

What I’m stuck on is how to get the precedent cells from a given cell range. You can do this manually using Dependency Inspector, but how do you do it in QAPI?[/quote:dxe15gzr]

[quote="Blue":dxe15gzr]Sorry, QAPI does not provide a way to do this. It can be done with the Extended API which requires an additional license. It has a class ExtendedMatrixView with a method getFormulaRanges() that returns the ranges referred to by a specified formula in the matrix.[/quote:dxe15gzr][/INDENT]

Does the 4.0 scripting engine provide access to the dependency information for a cell (with or without an additional license)? There is no available method on a cell, but maybe via some other route??

Thanks

Simon

0

Understood re dependency inspection, but calc vs input cell flag would be highly desirable.

That would of course require the cell object to be exposed in the scripting engine – per my other outstanding comment :)

Thanks

Simon

0

Hi All,

We feel that for now dependency inspection is beyond the scope of scripting. However, Dominik’s point about APIs for determining calculated vs. input cells makes sense, and we will look at incorporating that functionality into a future release.

Regards,
Ben

0

Simon
@Quantrix Developers

Looking into the QAPI Docs reveals that there are two boolean functions which fits to Simon’s request for testing whether a cell is an input cell or calculated by a formula. I suggest to add these two functions to the scripting engine because it is important to prevent a script to overwrite a cell which is caluclated by a formula. Or are they available already? Please let me know, thanks.

These are the mentioned functions (Interface: QCell):
[list:38e7v5ao]
[*:38e7v5ao] canSetValue() -> returns true if the cell is an input cell and is not locked[/*:m:38e7v5ao]
[*:38e7v5ao] getIsCalculatedValue() -> returns true if the cell is being calculated by a formula[/*:m:38e7v5ao][/list:u:38e7v5ao]

Dominik

0

Given no response so far on the question of access to the Dependency Inspector functionality I’m thinking that means it’s a “no” for now – which is could certainly understand.

However could I request that we get access to the flag that indicates whether a cell is calculated or an “input cell”. That would be very helpful in figuring out how to process the content of specific cells within a script.

Thanks

Simon

0

Simon,

Thank you for your reply. Yes, I realized while developing this model that my solution will not answer your question.

I am looking forward to see your generalization efforts!

Dominik

0

Thanks Dominik. That’s an interesting illustration of how to solve the original problem of doing a “breakback” calculation. But my question wasn’t about that particular issue, it was whether V4 scripting supported access to the functionality of the Dependency Editor, so one could trace back from a cell to its dependents.

Hopefully the Quantrix guys can answer that.

Nevertheless I may play with your suggestion in a different context – and if I create any useful generalization will certainly share back here.

Thanks

Simon

0

Simon,

I have created a sample model where a user can enter ten values into an input matrix (Input Numbers).

The output matrix (Matrix1) has a linked category named “Numbers” to the matrix “Input Numbers”.

If the user changes the numbers in “Input Numbers” he or she can then click on the button “Update numbers” on the Canvas1 on the right. Quantrix adds several formulas to the matrix “Matrix1”. Two formulas are commented out and are added just to provide the whole picture.

Now you want to distribute back the the average of a sum evenly to each number. Delete fall formulas in the “Matrix1” first (I haven’t found a possibility to delete formulas with a script so far). Enter the new sum in the matrix “Input Sum” and then click on the second button on the Canvas1 called “Update Sum and distribute numbers”.

This isn’t what you are looking for but maybe you can use this as a starting point. You could update the formula

Number = Sum of Number / 10 Skip Sum of Number

based on your need.

Take the attached model an play with it.

Other ideas and any improvements are very much appreciated.

Dominik