How to prevent field formulas from reverting to numerical value

[rezingg]

I have a block diagram that has a lot of annotations of frequencies, in may different places. The annotations are based on a common (smaller) set of frequencies. Because things are still changing I want to keep the common set of frequencies in a central location, so that I don't have to update the many annotations all over the place.
In order to do that I added a shape I named "FreqPlan" where I added a bunch of shape data. All the annotations use fields with formulas to calculate the value from the FreqPlan data. This works all just fine.

However, when I delete (or replace) FreqPlan, then Visio is "helpful" and replaces all the formulas in fields that refer to FreqPlan with the result of the evaluated formula before deleting FreqPlan. So when I place a new FreqPlan, then things are broken (the fields have now hard coded values rather than formulas using FreqPlan shape data).
What I am looking for is a way to protect the formulas, so they remain, even if FreqPlan is deleted. This means the formulas would be invalid at this point, but that is ok, as I will place a new "FreqPlan", which makes the formulas valid again.

For example the field value may be "=FreqPlan!Prop.RT_9_IF_from".
When I delete the FreqPlan shape that will automatically become "=12.4". I want to prevent this and keep the formula, even if it become (temporarily) invalid.

I tried to put a GUARD() around the field formulas, but that did not help.

Any other ideas?

[vojo]

problem is the link to freq table is broken

2 ways to fix

1) in each shape, test table is there
 if(stringdsame(freq table <header>.<header>),setf(getref(<this shape field>, <sheet.x of freq table! field>, "freq header table not present"

2) instead of delete the "freg table. set the values to zero.
    actions on the freq table would be very help.....reset all fields to zero
         display user cells
         input using props cells
         action .reset cell = setf(getref(actions.reset.checked), if(actions.reset.checked , 0,1)) 
            // may also want an "action.active cell and use that to pick up props values
         User cell 29 = if (actions.reset.checked, 0,Props.freq29)

[rezingg]

Thanks Vojo,
I'm not sure I understand the two solutions completely. I think the first one might work for me. The second one probably not. The reason is that I have multiple pages where I use the FreqPlan shape. So when I make a change to the frequency plan, I edit the shape data of one FreqPlan shape on one of the pages, then delete the FreqPlan shapes on all other pages and copy the new one onto those pages (I have to delete first and then place a copy so that the shape names will be "FreqPlan" and not get a .## add-on). Or maybe I misunderstand how solution 2 is supposed to work.

For the first solution I think I understand the IF and SETF, but  I don't understand how you check from the ShapeSheet of the shape that contains the field if the FreqPlan shape is present on the page. Can you expand on the explanation of:

stringdsame(freq table <header>.<header>)

in your example?
Greatly appreciated!

[vojo]

Option 1:   This is a formula in some sort of frequency table in each page
 
    So lets say sheet.99 is a group that has the rows of the frequency values
    And lets say sheet.100 is a shape to show a frequency.
   
    so in a user cell of sheet.99, you would have something like this
    Props.100 = "3.4 GHZ"
    User.shape100 = if (stringsame(props.100, "NA"), setf(getref(sheet.99!user.catch_freq), props.100),)

    Essentially, the formula sheet.99!user.shape100i checks if the props.100 cell is a good value value
    If so, write that value into sheet.100!user.catch_freq cell  for use in shape 100's use
    i.e. check and push

Option 2 gives the user some control when to push.  Action cells allow user to right click the shape and trigger an activity

    In this case, you can create several actions
      - 1st to open the props  GUI     Literally   docmd(1312)
           this allows you add values to the table
      - 2nd to push a given value to the shape
           the mapping to say sheet.100 is set when you create the table and shape  (no dynamic discovery of peer shapes)
           here you might use equation in option 1 like this
               User.shape100 = if (stringsame(props.100, "NA"), setf(getref(sheet.99!user.catch_freq), props.100),) + dependson (actions.1st.checked)

           above formula will be evaluted when the actions.1st.checked changes
           to do that,
              actions.1st = setf(getref(actions.1st.checked),if(actions.1st.checked, 0, 1))


One sidebar note, there are couple of interesting cells in each props row.
     - One is order....so that you can add props but display how you want
            Props.3rd.order = "CCC"
            Props.2nd.order ="BBB"
            Props.1st.order = "AAA"
            this would show the value of props.1st....then props.2nd...then props.3rd.
     - Another is hidden....this would gate where a props is showed then doing docmd (1312)  continuing example
            Props.3rd.hidden = <forumula that evaluates to FALSE>
            props.2nd.hidden = <evaluate to FALSE>
            Props.1st.hiddent = FALSE
            Props. 4th.hidden = <forumula evaluates to TRUE>

            then props 1st, then 2nd, then 3rd shown....but 4th is not displayed.

      Although tedious, you can make context menus to show different sets of props fields   
      E,g networking attributes, CPU attributes, etc by having props.context to trigger a user cell to set the hidden cells
      to show a number of props cells

      something like
         right and select actions network in the shape
         this triggers user.network_hide to 0   and then +docmd(1312) + user.network_hide to 1
         this in turn triggers on the network props to be shown (when done, hides those propos).
         (props.somecoolcell.hidden = and(user.network_hide, user.cpu_hide, user.port_hide, etc)  //any one = False, field shown

      Very tedious...is play with a simple square and validate the behavior....then add it to a shape of interest

Attached shows an example of a menu for something like option 2
       
         
 
         
           
     

[Paul Herber]

Option 3: put the data into the page shapedata

[vojo]

if N of a given shape, maybe 3a:  got to document stencil, and manually update the shape there ==>will update all instances of this shape.....maybe a combo of 3a and manually do docmd(1312) from the given shape

[wapperdude]

@OP:  please correct if this is wrong...
In your Visio doc you have multiple pages, each with many shapes.  Of these shapes, there are some that refer to frequency info.  This frequency info in some instances has a core of common frequency info.  For the core info, you have a reference shape that provides the necessary info for those shapes.  The core frequency info may and does change, and this is accomplished by replacing the core shape.  The process of replacing the core shape breaks the links to all of the dependent shapes.

Couple of questions...
1).  Do the core changes merely have value changes?
2).  Do the changes include deletion / addition of frequencies?
3).  How is this core shape created?
4).  Is a code (macro) solution acceptable?

[rezingg]

Hello Wapperdude,
sorry for the lengthy absence. Got many other things on my plate.
Yes, your description is accurate.
Answers to your questions:
1).  Do the core changes merely have value changes?
  --> yes, only values change
2).  Do the changes include deletion / addition of frequencies?
  --> no, number of frequencies is fixed, at least for now
3).  How is this core shape created?
  --> it is simply a rectangle where shape data was added.
4).  Is a code (macro) solution acceptable?
  --> preferably without macro, but if that is the only solution, then it is ok to use macros.

vojo's suggestion is interesting, put the shape that holds the shape data into the document stencil, and place the shape on all pages from the document stencil. Then make any edits to shape data on the master shape in the document stencil. Edits of shape data will propagate to all instances in the document. This looks like a good solution.
One down-side: you have to start out this way. In my existing document I will have to go through the pains one time to edit all fields to point to the new shape, oh well...

[rezingg]

Now that I have played with this for a bit, I suspect the right place to put the frequency data is in the shape data of the document. This should be accessible to all shapes in the whole document. The shapesheet of the document is accessible from Developer-> Show ShapeSheet -> Document. I believe I read somewhere that the document data can only be edited in the ShapeSheet of the Document, there is no GUI access to it. I have not made this step yet, so I am not completely sure this is the right way.

I did write a couple Subs which allow me to search all shapes in a document and replace a string with another string in the text filed formulas. This allows me to change the object that holds the data. In my example I can change "FreqPlan!" to "fPlan!". After running this all fields will now get their data from fPlan, which then allows me to delete FreqPlan without the formulas to get lost and revert to values.
Because this strictly a string search and replace, there is some risk to damage formulas. If the shape name string occurs in other parts of the formulas (which do not refer to the shape), then that will be replaces as well and hence break the formula. So use at your own risk, backup your file before use!

Code

' Update where fields in texts get their data from. Change onld shape name to new shape name in field formula.
' For example, used to point all existing fields that were pulling data from FreqPlan shape to new fPlan shape.
' Needed because deleting FreqPlan (so it can be replaces) will reset all fields to current value, removing the filed formulas pointing to FreqPlan.
' Requires updateAllTextFieldsInShapes Sub
Sub updateAllTextFieldsAllShapesAllPages()
    Dim vsoDoc As Visio.Document
    Dim vsoPage As Visio.Page
    Dim vsoPages As Visio.Pages
    Dim vsoShape As Visio.Shape
    Dim vsoShapes As Visio.Shapes
    Dim oldString As String
    Dim newString As String
    
    ' changes to make ####### ENTER VALUES HERE ##########
    oldString = "FreqPlan!"
    newString = "fPlan!"
    
    ' iterate through all pages
    Set vsoDoc = ActiveDocument
    Set vsoPages = vsoDoc.Pages
    For Each vsoPage In vsoPages
        ' iterate through all shapes on page
        Set vsoShapes = vsoPage.Shapes
        updateAllTextFieldsInShapes vsoShapes, oldString, newString
    Next vsoPage
End Sub

' used in updateAllTextFieldsAllShapesAllPages() Sub
Sub updateAllTextFieldsInShapes(vsoShapes As Visio.Shapes, oldString, newString)
    Dim vsoSubShapes As Visio.Shapes
    Dim rowCount As Integer
    Dim rowIdx As Integer
    Dim valueString As String
    Dim stringPosition As Integer
    
    For Each vsoShape In vsoShapes
        ' iterate through all Text Fields
        If vsoShape.SectionExists(visSectionTextField, False) Then
            rowCount = vsoShape.rowCount(visSectionTextField)
            For rowIdx = 0 To rowCount - 1
                valueString = vsoShape.CellsSRC(visSectionTextField, rowIdx, visFieldCell).FormulaU
                ' check if string is in Formula
                stringPosition = InStr(valueString, oldString)
                If stringPosition > 0 Then
                    valueString = Replace(valueString, oldString, newString)
                    vsoShape.CellsSRC(visSectionTextField, rowIdx, visFieldCell).FormulaForceU = valueString
                End If
            Next rowIdx
        End If
        ' treat any sub-shapes
        Set vsoSubShapes = vsoShape.Shapes
        If Not IsEmpty(vsoSubShapes) Then
            updateAllTextFieldsInShapes vsoSubShapes, oldString, newString
        End If
    Next vsoShape
End Sub

[scott]

I'm glad you discovered the ShapeSheet for the document -- that was going to be my suggestion as soon as I read your initial post. It can be a convenient place to store values that you want to use on multiple pages. You're correct that there's no UI for the document sheet. However, it's possible to fake that by using a set of mirror fields on a page or one special shape and then using formula to push changes from there back to the doc sheet. Let me know if you want an example. However, assuming that changes are infrequent, just editing the doc sheet directly has usually sufficient for me.

[rezingg]

@scott,
thanks for the pointer. By now I have converted all my diagrams to use the document shape data. And yes, I am editing the data in the ShapeSheet. I think this is ok for me. I did see examples of what you mentioned, pushing values from a shape to the document shape data. For now I'm ok without that.