Author Topic: Adding a dynamic reference string in one shape that references another shape  (Read 345 times)

0 Members and 1 Guest are viewing this topic.

Quixote

  • Newbie
  • *
  • Posts: 2
Good morning:

Without going into unnecessary detail, I'm designing a collection of shapes that propagate electrical load within a visio model. In order for the system to work, each shape must read key fields from the shapes that are connected to it.  Thus far I have done this by automatically updating the shape sheet equations each time two objects are connected or disconnected.  Unfortunately, in many cases this requires that each object have an in depth knowledge of the object next to it...  I'd like to avoid this.

Is there are way to place the name of adjacent shape in a ShapeSheet property (say parent) and then simply reference that property from all other fields... for example, these fields create a property called Parent with the name of the object connected to this one, a property called Load that contains the amperage of this device, and a property called Overdrawn that compares this object's Load with the parent's Capacity using the reference:

Prop.Parent="Sheet.1"
Prop.Load="400"
Prop.Overdrawn = Prop.Parent!Capacity<Prop.Load

Any help would be appreciated.

Q

Yacine

  • Hero Member
  • *****
  • Posts: 2368
=SetF(getref(prop.overdrawn),"="&prop.parent&"!capacity<prop.load")
Yacine

Quixote

  • Newbie
  • *
  • Posts: 2
Interesting.  So to use this I would make a property called, for instance, update and place this formula there...  Then whenever the object is updated it would propagate to the effected fields?

vojo

  • Hero Member
  • *****
  • Posts: 1074
keep in mind, both shapes have to be already dropped on the sheet to make the reference work
Otherwise, the cell will have a "REF" value since shape not on page when formula created.

You could make some sort of "broker" shape
- add some sort of sheet wide repository of properties
    - legend shape or page properties
    - could be a background page (sort of use background page as "global database" of values for all shapes.
- each shape goes to the "broker" to pick up latest values.

Could look at other way around
- shape handle says what shape it is...somehow register in "global database"
- "global database" shape or page pushes the right values to the shape


Probably need some tricks to make it work and wont kid you, this will be tedious.

Obviously, if you use VBA this could be done much simpler but have VBA skill and security (enable macros) settings.

Yacine

  • Hero Member
  • *****
  • Posts: 2368
keep in mind, both shapes have to be already dropped on the sheet to make the reference work
Otherwise, the cell will have a "REF" value since shape not on page when formula created.

since we're handling the formula as string, it doesn't matter whether the other shapes exist or not. The formula gets repaired as soon as the reference is corrected. Some sort of "self healing" ;).
Yacine

vojo

  • Hero Member
  • *****
  • Posts: 1074
how do you know other shapes name?
what about 2 of them on a sheet?

Yacine

  • Hero Member
  • *****
  • Posts: 2368
how do you know other shapes name?
what about 2 of them on a sheet?

The shape's name is in a manually (or automatically) filled prop field:
Prop.Parent="Sheet.1"
Prop.Load="400"
Prop.Overdrawn = Prop.Parent!Capacity<Prop.Load

In case of 2 or more shapes, you can either provide additional fields or work with a list and identify the individual names by their index in the list.

Cheers,
Y.
« Last Edit: February 06, 2018, 11:08:28 AM by Yacine »
Yacine

vojo

  • Hero Member
  • *****
  • Posts: 1074
you miss my point
Visio shapes are unaware of other shapes....so for 1 shape to always reference another shape, it has to be
   A) manually entered by the user
   B) some sort of VBA approach
   C) some very complicated spreadsheet approach looking at some sort of store and forward method.

Shapes will not automagically find each other on drop (REFs do not get corrected)

Or is this some sort of group of shapes concept so that within a group, static relationships are well defined?

vojo

  • Hero Member
  • *****
  • Posts: 1074
BTW...your suggest of using "  "  forgets the advice from MS

you should not use a " " approach because the visio internal mapping database is dynamic...no guarantee the
"sheet.1" will actually map to the sheet.1 as shapes added.

This was from visio 2003 or earlier.

Yacine

  • Hero Member
  • *****
  • Posts: 2368
Hi Vojo,
yes I did indead miss your point - actually wie talked past each other.

You're absolutely right about needing VBA to get relations, but Quixote showed formulas where he had already the name of the referenced shape in a prop field.
When you do have this value, you can write the formula as requested.

I've posted many years ago an example showing exactly this solution: http://visguy.com/vgforum/index.php?topic=1714.0

So basically you work with such as system like in Excel, where you put explicitely the address of the referenced cell (eg A1=3xB1) or in excel terms

prop.myRef = "sheet.1"
user.update = SETF(Getref(prop.myResult),"=" & prop.myRef & "!width*3"
prop.myResult = sheet.1!width*3
Yacine

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3053
  • Ideas Visio-lized into solutions
Seems like there are two issues...

The first is the original syntax...Prop.Overdrawn = Prop.Parent!Capacity<Prop.Load.  This should read Prop.Parent & "!" & Prop.Capacity.

The 2nd, more critical issue is overloading due to multiple loads on a single source.  The proposed approach seems to account for each loading device as singular loads and does not account for accumulated loading from multiple loads connected to the parent.

Seems like VBA is need to check connectivity and do the loading calcs.

Wapperdude
« Last Edit: February 09, 2018, 01:38:24 PM by wapperdude »
Visio 2007 Std

vojo

  • Hero Member
  • *****
  • Posts: 1074
but no guarantee that sheet.1 actually points to sheet.1 shape.
MS says you need to use its base handle to that.  It looks sort of like UUID field.

Specifically
- first time around, I drop sheet.1, sheet.2, sheet.3
- sheet.3 is of interest. so user updates his props field (because the third shape is what he wanted)

- second time around I drop sheet.1, sheet.2, sheet.3
- but because I dropped interesting shape first, user needs to update props field (because the first shape is what he wanted)

In other words, the user either must
A) specifically order the shapes dropped to know the nth shape is of interest
B) look in shapesheet of shape of interest to know its sheet.n

especially if dropping shape of interest twice (sheet.1==>sheet.2  and sheet.3==>sheet.4....need do the match ups
or (sheet.1, sheet.2, sheet.3, sheet.4, sheet.5, sheet.6....1:2  6:3  5:4)

http://www.visguy.com/2009/07/15/whats-my-shapes-id/