Worksheets Control with Python

[sully8391]

I  have two worksheet questions.

1.) I'd like to link rows of a worksheet to instances of a PIO to store some parameters and format settings. Right now I'm creating a UUID, storing that in a hidden parameter and in the last column of the worksheet, and then I match the UUIDs to push/pull data. This is not only a bit ugly, but a user can easily change the cell, breaking the link. On the other hand, if I store the initial row number in a hidden parameter, the user could rearrange the rows and break the link that way. Is there an elegant way to link rows with Python/Vectorscript? Or maybe some methods I've overlooked that might help?

 

2.) The PIOs are event-enabled, so when a parameter is updated, it automatically updates the worksheet. I'd like this to work the other way around. In the SDK I found this property: kObjXPropGetWorksheetEvents = 57. To me that looks like an event-enabled PIO is aware of changes in worksheets, but I haven't been able to find any other information on what event IDs to use, or how to point a PIO at a specific worksheet. Is is possible for edits in a worksheet to trigger a parametric recalc in a specific PIO?

 

Thanks, All!

 

 

[Pat Stanford]

If you can, it would be better to create a Worksheet Database rather than try to manage the link between the objects and the worksheet with a script.

 

 Using a database (or Report for a slightly different interface but mostly the same functionality) you set the Criteria that define the objects that will be shown in the database subrows. You enter the formulas into the cells in the database header row. The formulas can be basically the same as the ones you use in worksheet cells, but any that take criteria are entered without the criteria and pick up the object(s) set by the database criteria.

 

Any cells that are Record.Field, including PIO Parameter Record fields are automatically two-way objects. If you change them in the Worksheet they update the object. If you update the object in the OIP, the worksheet will be updated the next time it is recalculated.

 

Ask again if you need more help.

[JBenghiat]

And if Pat’s suggestion doesn’t provide

your solution, you may want to rethink your overall design. Things like preferences that are global for the file are generally stored in a hidden record as the default data. You would write a custom dialog to change the preferences. 
 

Any data specific to a PIO should store in a PIO parameter, in which case using database rows of the worksheet will work for you. 
 

kObjXPropGetWorksheetEvents will trigger the PIO when the worksheet recalcs. I’m not sure if this event is accessible outside of the SDK — there is a separate c++ interface for dealing with worksheet events. 

[sully8391]

I had given a Database Worksheet some thought, but I wasn't able to find any methods that allow me to create a database with Python, and that's a requirement. I also don't think storing these as parameters will work, because the number of components being formatted is configured by a user, and a simple PIO could easily see 256 parameters. If it's possible to create a Database with Python, this could work for the other PIO's I'm working on, because the worksheets are just schedules and can link to actual parameters in the OIP.

 

As for a hidden record, are you referring to a record that's automatically created like the one for parameters, or one that I generate? There are a number of times I've thought about using a record for data storage, but there are just so many things I want hidden from users. There are also general preferences I'd like to set for the plugin-package, but I don't see how that's possible without instantiating persistent objects.

 

I'm seeing some success using a dialog to act as an interface between the user and the worksheet, in which case I can execute scripts that update either the PIO or the worksheet as part of the dialog, but what I really (really, really, really) want is to treat the plug-in object like, well, and object, in the OOP sense. Then I could just create a dictionary and use a dialog to edit it; piece of cake. Actually, I can't count the number of times I've wished to interact with a PIO like an OOP object. Is this something that's possible in the SDK? I have to imagine that each instances of a plugin object is in fact an OOP object, if I could extend that object then all my Vectordreams would come true.

Thanks for your knowledge!

[Pat Stanford]

You certainly can generate database rows using a script. Take a look at this post for one I did in Vectorscript. Look for the lines that start with Formula:= and then the line after that is the Vectorscript command.

 

The basic idea is you set the formula of column zero to a formula of =Database('Criteria Sttring Goes Here')

 

Ask again if you need more help.

[MullinRJ]

Hidden records, or visible ones for that matter, don't need to be attached to anything. You can add fields dynamically, store your data, and retrieve it at any time, even the next time the file opens (assuming you saved it first.) As to the number of fields a record can handle, I never looked for the limit. I have successfully stored 10K unique fields in one record as a test. I assume the upper limit is 32K fields, if I were to guess. Maybe someday I'll ask myself that question again and try to answer it.

 

Records are excellent for storing data that persists in your file, but not ideal for data that needs to be accessed by other files, though I've done that with much effort. If you want to read and write from a central source so multiple files have access to it, I suggest you explore using XML. Whether you use records or worksheets to store your local data is a personal choice.

 

Good luck, and have fun doing it,

Raymond

[JBenghiat]

1 hour ago, sully8391 said:

I also don't think storing these as parameters will work, because the number of components being formatted is configured by a user, and a simple PIO could easily see 256 parameters

You can always hide and show parameters. There is a command to control parameter visibility, as well as prefixing the name with a single underscore for a read-only parameter and a double underscore for a hidden parameter. 

if you’re saying that you are needing to store a variable amount of data, you can store the data in a hidden text field as a JSON string (or delimited data if it’s simple), and use a custom dialog to interact with the data. 
 

1 hour ago, sully8391 said:

 

As for a hidden record, are you referring to a record that's automatically created like the one for parameters, or one that I generate? There are a number of times I've thought about using a record for data storage, but there are just so many things I want hidden from users.

Not exactly. You create a record format object — check if it exists (by name), and create if it doesn’t. Each field is a different setting. Setting the object variable 900 of the record format to false will

hide it from the user. You can get a handle to the format and set its defaults with GetObject(recordName). Use the defaults as your data storage — that’s your “persistent object.”

 

1 hour ago, sully8391 said:

Actually, I can't count the number of times I've wished to interact with a PIO like an OOP object. Is this something that's possible in the SDK?

I don’t think in the way you’re envisioning. You use Get/SetRField to access parameters. For a complex PIO, you certainly can create your own python or c++ object, where you load the data on initialization and then write the data at the end of your code. You will likely have a number of calculations and error checks that would really prohibit automatic object creation anyway.