Sheets sharing data and updating across pages

Started by rodney.kimble, August 20, 2024, 10:15:05 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

rodney.kimble

I've searched as much as I can for a solution for a while now but can't find an answer, but I know where there's a will, there's a way.

I want to be able to place a shape on a page, and then copy and paste that shape to another page and being able to update either shape and it will reflect on both. They are essentially the same shape but on different pages. And we load lots of shapes into a single document so often there are fields that are missed across pages that causes problems down the road. Is there some way to link these shapes using a user property or custom property that can be edited by a user?

My biggest issue is that this sheet is not for only me to use. I wouldn't mind doing the extra steps but my team doesn't have that time. They need to be able to load a shape, edit the text, and move to the next one. I've added as much as I can to the shapesheet to help with everything else, this is my final step. If I have to learn VBA I will, I've already been diving in to it a little with excel. I just need to be pointed in the right direction.

Attached a screen shot and project file through box.com. Work computer won't let me use any other file sharer.

box.com download because my work computer blocks me from everything else

Thomas Winkel

#1
You can use SETATREF formula to link fields in two-way:
Prop.Name = SETATREF(Pages[Page-1]!Sheet.1!Prop.Name)
See attached document for an example.

The problem is that there must be a "main shape".
The formula must be inserted to all copies of this a main shape.
These copies can then be copied again without action.
All copies are linked two-way to the main shape.
If you change a field anywhere, all shapes will be updated:
Changed shape -> main shape -> all linked shapes
If you delete the main shape all formulas will break.

I think this is all that can be done without code.

rodney.kimble

This works but I have two problems with the solution:

1. I can't expect a user to do all of this for every (or any) shape pulled from a stencil

2. I would have to load two copies of every shape, one with the original data and one with the setaref formula, which sounds like it would balloon the size of the project file by more than our fairly weak government computers could deal with on our network.

If this sounds is all that can be done without code then it sounds like I'll have to explore coding for this, which I don't mind it'll just be a little learning curve for me.

I was reading the forum and found out about uniqueIDs but found another post stating that uniqueID shapes can not be duplicated across pages, but maybe there's a way around that? Not sure where to start looking from here.

wapperdude

My opinion is this needs code. There are at least two problems for Visio to resolve for the shape dragged and dropped onto a page.  First, the references in the formula are exact, e.g., Page-1, Sheet.3.  If these don't exist, they get removed.  Visio doesn't know how to repair the formula.

> Will page-1 always be the reference page?  That is, could the 1st instance of some reference shape be on another page?  This could be resolved via code.  Not by shapesheet functions.

> How is the reference shape identified?  It will generally not be Sheet.1.  So, code could search for something unique about each shape to identify the desired relationship.  Shape ID is not a good search criteria.  Adding a unique cell, say a User cell (one or more???), that the code can use as identifier.

> once both target page and shape are known, code can be used to construct an appropriate formula and insert it into shapesheet.

> once a shape has been "formulated" it can be copy/pasted.

> some caveats:
    >> Visio will drop the page identifier when shapes reference the page they are on.
    >> the 1st dropped shape will not have a 2nd shape for reference.  Therefore, it cannot have a formula until after a 2nd shape is available.
    >> when to run the code is somewhat problematic. 
          >>> For initial drop, there's no 2nd shape for formula reference
          >>> letting it run automatically requires some serious event monitoring.  Not desirable to trigger every time a some arbitrary shape is dropped.

Bottomline:  this is a tough task.
Visio 2019 Pro

Nikolay

#4
Unfortunately Visio has no built-in solution for synchronizing shapes across pages.

Some specific Visio solutions, such as Org Chart diagram for example, implement this; however, those solutions are not generic.

wapperdude

#5
My 1st solution try might be something like this...  (just thinking out loud, as it were)

1.  The stencil(s) have only the shapes involved.
2.  Perhaps add at least 1 User shapesheet entry in each shape to form unique identifier.  Perhaps two cells:  one that provides unique identity and 2nd to show that it is 1st drop.
3.  Create some sort of list / tally for 1st drops.  This list may reside at document level, background page, 1st page.  It would contain each 1st dropped shape and its location (page)
4.). The code would then handle any newly instantiated shape:
        > if not a "qualified" shape, it would either not start (invalid event) or immediately quit (invalid shape)
        > if a qualified shape, (perhaps need a 3rd User cell???), search the list.
              > if first entry found, build formula in both current shape and 1st shape.  Do this only once for the 1st shape
              > the constructed formula would be SETATREF fcn as suggested by Thomas Winkel.  See:  What's With SETATREF for complete syntax, usage, examples.
              > if no pre-existing 1st entry, then current shape is 1st, and gets added to the list.


Visio does have a shape that does a tally count of shapes, but not sure its useful here.
Visio 2019 Pro

wapperdude

The "Tally" shape is here:  More shapes>Business>Brainstorming>Legend Shapes

See this link:  Counting Shapes

Visio 2019 Pro

rodney.kimble

Ok so taking some ideas and trying to formulate something to add on to what you're suggesting. I'm unfamiliar with the tally but it sounds interesting if it works how I'm interpreting it.


1. > Create a shape with properties and a unique user cell that can reference a data point from one of the user inputted properties.

2. > Load that shape into a stencil for users to pull from.

3. > Upon shape drop, create some kind of vba code with a drop down list that references what's in the tally list, or if it's a new item.

3a. > If user selects new item, code will copy shape as is and ask on drop will let users define field. User field gets added to the tally list (that will probably live at document level)
     
3b. > If user selects an item from the tally list, vba will copy that shape and then set the properties to SETAREF to the original shape.

My concerns with this are that I don't know how to work with a legend list, unless it's just inserting rows into the doc properties, in which case I'm sure I could figure that out. I don't know if it's possible to create a drop down prompt from vba. I've seen them created within the shape data properties and I know it's possible with activex, but I have not worked with them in coding.

And I'm unsure why we wouldn't be able to assign a uniqueID and use that for referencing to bypass having to define the page the original shape is on. I thought uniqueIDs were document level references, but I'm not sure how they work.

I have yet to dive into learning how to code this at all anyway since I just got to work a few hours ago but this will be a project to conquer for the next few weeks it seems.

wapperdude

The idea of my brainstorm was to show a process:  create a singular, easy access reference that can dynamically grow as needed.  This eliminates having to perpetually loop thru every page in the document.  The list needs some key elements to be useful.  And, once info is gathered, there must instructions how to proceed and execute task.

I'm not suggesting the legend shape per se.  It was somewhat related, suggested as a thought activating inspiration.  There are multiple ways to do the "tally".  The main point was it's invisible to the user.  Possibilities include a VBA array, an Excel style worksheet embedded in Visio, or perhaps leveraging container and its members.

Yes.  Lots to learn.

Explore using macro recorder to get bits of code.  See this link for shape and VBA functions:  Lists of Functions

You can Google a function and often get sample code.
Visio 2019 Pro

rodney.kimble

Thank you for pointing me in the right direction and showing me the tools needed to get started. If I ever figure this out I'll be sure to come back and post the solution for future readers

wapperdude

Don't stay away.  Ask questions as you go.  Plus, as other users come onboard, they may have additional comments, ideas, recommendations.

Also, share as you go.  Early critiques can avoid getting painted I to corner.
Visio 2019 Pro

Nikolay

#11
@rodney.kimble, Just interesting, how do you identify your shapes in principle?

I.e. how do you know that ShapeA on PageA is the same shape as ShapeB on PageB? (or, rather, "should be" the same)
Do you have some title or number or?

Here for exameple you have some properties:


Is one of them used to identify the shape (is it "ONE-01")?

The thing is, if it is so, it could be a potential feature VSU for the future.
You just select a property that would "identify" your shape, and when you modify others, they are synced with other "instances".

rodney.kimble

Quote from: Nikolay on August 21, 2024, 10:23:47 PM@rodney.kimble, Just interesting, how do you identify your shapes in principle?

I.e. how do you know that ShapeA on PageA is the same shape as ShapeB on PageB? (or, rather, "should be" the same)
Do you have some title or number or?

Here for exameple you have some properties:


Is one of them used to identify the shape (is it "ONE-01")?

The thing is, if it is so, it could be a potential feature VSU for the future.
You just select a property that would "identify" your shape, and when you modify others, they are synced with other "instances".

Yes, that label property (ONE-01) is how the shapes are differentiated throughout the document. I have never heard of a VSU before, but what you're describing is exactly the direction I'm trying to go. I'm just a long ways away from figuring out how to get from point A (limited vba knowledge) to point B. Right now I have a user cell set up on every shape to reference that label property, I just don't know how to make it do anything yet.

Thomas Winkel

I attached a simplified demo from my Visio ECAD project.
It's a combination of VBA and ShapeSheet.
VBA is simple, but the ShapeSheet formulas are complicated.

You just have to implement the cells "User.SyncId" and "User.SyncShape" to make this work with your own shapes.

A refresh (-> right click on any shape) is necessary if new names are introduced, or "main" shapes are deleted.

The main shape is the first shape found for each SyncId.
SyncId is associated with Prop.Name.

In my ECAD project I have a Refresh button in the Ribbon menu.
I'm thinking about releasing some features like that as an Open Source AddIn.

rodney.kimble

An update on my project;

The code and shapesheet formulas from Thomas worked perfectly. It just took a little bit of finagling to get it to work with the shapes I had already built. I did have to input these in for every shape, but it does what I need it to do and it's easy once you find a flow and a template. I attached samples of what I've had to build into the user.shapesync value cells.

I ended up moving the sync.refresh command to the page level to allow users to be able to click anywhere on the page and refresh. It allowed me to avoid having to add additional items to the menus of every shape.

I do wish there was a way to find how to reduce the amount of code needed for every shape, but it works and is not bogging down my build at all.

I do have a question about the user."rowname".prompt field. I don't understand how or why that works since I thought the prompt field was used to display a message to the user in some way. If other prompt fields work the same way I may be able to reduce the amount of space I'm taking up on my shape data page.

Just glad it works and that it allows me to focus on so much other stuff, like properly learning vba to program menu items, making automated color connectors and such. I'll come back with a full post showing what I'm fully trying to accomplish. Big thanks to Thomas. Yall will see another post from me pretty soon.


Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 408 (show)
Files included: 32 - 1207KB. (show)
Memory used: 1291KB.
Tokens: post-login.
Cache hits: 14: 0.00247s for 26,548 bytes (show)
Cache misses: 4: (show)
Queries used: 16.

[Show Queries]