Referencing Container Nested Relationships

[manifold1978]

I have a master container shape, which when brought onto the page from the stencil, typically contains further instances of the same container, upto a maximum of 4 levels as shown in the attatched.

I then have another shape, which I want to inherit the properties (i.e. the name) of all the containers to which it belongs, and display them in it's own shape data, as highlighted in the attached (with only the last two working).

The problem I think I'm having is that there seems to be no reliable way to reference the nested container relationship in Visio.

CONTAINERSHEETREF() needs either the Z index, or the Z index and a category. This doesn't help here as the Z index isn't the same thing as the nested relationship. If category would work alone without Z index, I feel like it work work, but it NEEDS the Z index.

I've tried to dynamically set (and guard) the DisplayLevel of the master container shape based on it's type, so something like the following in Shape Layout > DisplayLevel field of the Container's shape sheet:

=IF(STRSAME(Prop.Area_Type,"Site / Campus"),GUARD(-25000),IF(STRSAME(Prop.Area_Type,"Building"),GUARD(-26000),IF(STRSAME(Prop.Area_Type,"Floor"),GUARD(-27000),IF(STRSAME(Prop.Area_Type,"Room"),GUARD(-28000),IF(STRSAME(Prop.Area_Type,"Location in Room"),GUARD(-29000),"")))))

I also tried just simply changing the DisplayLevel to =GUARD(-#) in different instances of the container shape.

Doing either of these things, the "send to front" and "send to back" buttons always allow me to click a few levels up or down before it decides it's locked and doesn't let me go any further.

If I keep clicking them, the Z order will eventually "settle", I won't be able to send forward or back, and the nested relationship will correspond to the Z index as I would need it to in order for CONTAINERSHEETREF it and get the result I'm after. To me it should not allow me to do this, and the Z order should always correspond properly as everything should be in a different DisplayLevel band.

I understand that Z index and DisplayLevel aren't the same thing, but surely if I'm setting everything to different DisplayLevel bands, then everything in a lower band must have a lower Z than anything in a higher band?

Can anybody spot what I'm missing, or am I just thinking about the whole thing in the wrong way, and there IS some way to reference a nested relationships?

Thank you!

[wapperdude]

I haven't tried your scenario, and not @ my PC, but in the containershaperef(), I believe the value entered is the equivalent to Z order.  Typically, value = 1, which is the immediate holding container, i.e., parent.  Value = 2 is the next level holding container, i.e., grandparent, value=3 would be great grandparent.  Anyway, that's how I believe it works.

EDIT:  Confirmed. 

[wapperdude]

Considering Z-order as relates to nested Containers... in a word, BEWARE!!!   Messing with Z-order will mess with Containershaperef() results.

For consideration, see the attached file.  It has 4 nested containers plus 1 member rectangle shape.  Each container has an added named User row, User.MyTxt, with appropriate entry.  The containers were placed such that the Great Great Grandparent was 1st.  The 2nd, Great Grandparent was next and assigned to the 1st.  Thus, the z stack matches the nesting stack.  This would be the expected and normal ordering method.  Certainly, I would recommend it as such.  The rectangle is placed above and belongs to the upper most Container.  The rectangle then field inserts the User.MyTxt, beginning with the 1st, Great Great Grandparent, container and progressing thru the Parent container, using the Containershaperef fcn.  As shown, all is as expected.

Now, the fun.  Select the parent container.  Send it backward 1 Z-level.  It will be hidden behind the Grandparent container.  The order of the list in the rectangle changes, even though the field  listing order did not change.  That is, the lowest line shown in the rectangle is Containershaperef(1) and now points to the Grandparent and not the Parent.  This can be observationally verified by looking at the TextFields section of the rectangle's shapesheet.  Thus, Z-order changes can alter Container membership assignments

[wapperdude]

This is not an endorsement of your method, which, is interesting.  This merely addresses the concerns of z-order.  The attached file shows how your approach might be implemented.  Sorry about the "geneology" theme.  I had chosen that as a simple visual aid, and just stuck with that theme. 

One thing to note is that a shape can only reference containers that are in its vertical stack.  That is the only possibility for a shape belonging to multiple containers as far as I know.

[manifold1978]

Thanks again for the replies.
I finally worked out what I feel like is the only way to do this.

One problem was that if I brought a shape outside of a container that it used to be in, any "unused" CONTAINERSHEETREF values stuck.
And the Z order thing was still causing a problem regardless of what I did to DisplayOrder.

So I prevented certain containers from being put inside others using Categories (i.e. don't let a Building go inside a Room, for example).
Then in the shape, check the CONTAINERCOUNT so I could narrow down the only CONTAINERSHEETREFs that mattered in a given situation.
Each of the shapes various fields (i.e. Building, Room etc.) could then be looked for using STRSAME to match the category of the container, which could never be wrong becuase any invalid things were disallowed via the container categories.

So it's a bit long winded, but the formula for the Building field for a shape, looked like this:

IF(CONTAINERCOUNT()=0,"",IF(CONTAINERCOUNT()=1,IF(STRSAME(CONTAINERSHEETREF(1)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(1)!Prop.NAME,""),IF(CONTAINERCOUNT()=2,IF(STRSAME(CONTAINERSHEETREF(1)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(1)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(2)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(2)!Prop.NAME,"")),IF(CONTAINERCOUNT()=3,IF(STRSAME(CONTAINERSHEETREF(1)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(1)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(2)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(2)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(3)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(3)!Prop.NAME,""))),IF(CONTAINERCOUNT()=4,IF(STRSAME(CONTAINERSHEETREF(1)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(1)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(2)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(2)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(3)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(3)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(4)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(4)!Prop.NAME,"")))),IF(CONTAINERCOUNT()=5,IF(STRSAME(CONTAINERSHEETREF(1)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(1)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(2)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(2)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(3)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(3)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(4)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(4)!Prop.NAME,IF(STRSAME(CONTAINERSHEETREF(5)!Prop.AREA_TYPE,"Building"),CONTAINERSHEETREF(5)!Prop.NAME,""))))),""))))))

[wapperdude]

Great!  Good to hear you have working solution.  Filtering possible, undesired combinations is always challenging.  Getting a loooong, cascaded IF formula to work can be very difficult, time consuming.

However, couple points concerning that type of structure...
1) as time goes by, you will forget how / why the formula is constructed; not intuitive
2) such formula are difficult to edit:  adding to or deleting from

The recommended approach is to deconstruct the large formula into multiple, smaller pieces which are intuitive, easier to edit.  Then, combine these smaller pieces into the final large formula.  Most of this effort is done in User defined section.  Note, initial defining the required scheme takes planning, effort.  But the ultimate outcome is more satisfactory in terms of future alterations/debugging/understandablity.

I can only encourage you to do this extra effort.  Nevertheless, again congrats resolving your issues.

[manifold1978]

Thanks, makes sense.
Given Visio itself seems like an inconvenient platform to be able to be able to see how formulae are constructed, what's a good way to do this?

I've literally been using pen and paper with different colours so I can see the start and end points of the various IF statements.

[wapperdude]

This can be developed in the User section.  Off the top of my head (not thoroughly thought out), the scheme might go like this:
User.CountDec =  If  containercount = specific value, then gto User.CountValue.  For example, User.Cnt1 or User.Cnt2, etc

Then, in each User.CountValue = if containersheetref = specific value, then gto User.CSRValue.
Then, in each User.CSRValue do strsame comparison appropriately

Basically, the long horizontal formula is deconstructed into a vertical cascade of smaller formulae.  Several  consequences:  1) User Section may end up with many rows, 2) each row has obvious function and traceable consequence, 3) each row is more readily editable.

To facilitate, navigating the flow, there are a couple of tools available. 
1) With the shapesheet open and window active, the GUI has tab Shapesheet Design.  There within is section Formula Tracing.  Select the Window.  For a given selected cell, the window can be chosen between precedents or dependents of that cell.  That is, who points to it, or to whom it points.
2) Nikolay has an add-in, Shapesheet Watch that is quite useful/powerful to see what's going on.