Controlling multiple variables in shape data

[Michelle]

I have some shapes where I would like to be able to control several different variables within the shape data.  For example, a shape may have choices in the areas of part number, description & cost.  Here is a super simplified example:

Part #1 ....red..... $0.00
Part#2......blue....$5.00
Part#3......black...$10.00

These do not change, as in the red part #1 is always free, and the $10.00 part is always #3, and black.  Etc.  The problem comes in that I would like to try to set up shape data as a way for the user to choose the part he wants by choosing any of these variables.  Some users may know part numbers and choose #2.  I would like the other shape data areas to change according to what they should be for part #2.  But another user may remember that the one he wants is black.  I would like for him to be able to choose that and have the other fields change.

How can I do this?  I have tried setting up (for example) an if statement that makes the color change when the part number is changed.  And then of course makes the price change according to the part number as well.  The problem comes in when I then try to make the part number change based on color or price... I get an error that I have a circular reference. 
Help! 

Thanks so much in advance!

[Michelle]

I don't know if I'm on to something or not... I am trying this:

=(OR(IF(STRSAME(Prop.Description.Format,"small"),(STRSAME(Prop.PartNumber.Format,"1")),(STRSAME(Prop.Cost.Format,"10.00"))),"10.00",IF(STRSAME(Prop.Description.Format,"med"),(STRSAME(Prop.PartNumber.Format,"2")),(STRSAME(Prop.Cost.Format,"20.00"))),"20.00","30.00"))

I have the cost working for $10.00, as in it changes to $10.00 if either"small" or part number "1" is chosen.  I am not having any success with the cost changing to $20 when either "med" or part number "2" are chosen, nor do I have any luck with the cost changing to $30 when any other change is made.  I am hoping my mistake is somewhere in my parentheses, or maybe my use of IF and OR?  Am I on the right track?

Thanks again,
Michelle

[Michelle]

Another try:

=((OR(IF(STRSAME(Prop.Description.Format,"small"),"10.00",(STRSAME(Prop.PartNumber.Format,"1"))),"10.00"))+OR(IF(STRSAME(Prop.Description.Format,"med"),"20.00",(STRSAME(Prop.PartNumber.Format,"2"))),"20.00"+(OR(IF(STRSAME(Prop.Description.Format,"large"),(STRSAME(Prop.PartNumber.Format,"3")),(STRSAME(Prop.Cost.Format,"30.00")))))))

Pretty much everything I do just places the cost at 10.00.  Ugh!

[vojo]

this looks pretty complicated....I would start simple and go complex

1. simple if then else.....make sure that works
2. add the or case....make sure that works
etc

Another option useful is to put various pieces in a bunch of user cells

- Small case in say user.small cell....maybe even a user.smflag to signal small case in play
- mid case in say user.mid cell.....maybe even....
- large case in say user.large cell....etc
- a general test using the user cells or the flag cells to pick final value

[Michelle]

OK, Vojo... Thanks for taking time!  I'm trying, but not succeeding.

I tried this: (see attachment)

Then in the Value area for cost in the Shape Data I put:
=GUARD(IF(User.Cost10,"10.00",IF(User.cost20,"20.00","30.00")))

Aaaaaand it's not working.  Can you help?

Also I'm not familiar with user.smflag.  What is this flag you refer to?
Thanks for your help, sorry for what are probably stupid questions.  I guess I am VERY rusty.
Michelle

[vojo]

cell user.cost10marker = if(inputcost = 10, true, false)
cell user.cost20marker  = if(inputcost =20, true, false)

cell user.cost = guard(if(user.cost10marker,"$10.00",if(user.cost20marker,"$20.00,"err")))

Once you get this working   then

cell user.cost10marker = if(AND(inputcost <20, inputcost>10), true, false)
cell user.cost20marker = if(AND(inputcost<30, inputcost>20),true, false)
and add say
cell user.costothermarker = if(AND(user.cost10marker = false, user.cost20marker = false),if(inputcost= 59.23, true,false),if(inputcost = 99.99, true,false)

cell user.cost = if(user.cost10marker, "betwee 10 and 20", if(user.cost20marker, "between 20 and 30", if(user.costothermarker, "either 59.23 or 99.99", "not one of the cool values")))

and so on

When you look at this in spreadsheet.....go up to view and select value instead of forumla....you can see what each cell evaluates to

Note once you get the hang of this, given cell can something like 127 If statements nested  (I personally have done something like 8 in a cell)

[Jumpy]

I only skimmed the thread so far, but it sounds like a case for the SETATREF-ShapeSheet-Function-Family.
That makes it possible to alter a value from different places. But it's not easy to learn, how to wield these functions:

http://msdn.microsoft.com/en-us/library/office/ms425563%28v=office.12%29.aspx
http://msdn.microsoft.com/en-us/library/office/ms425564%28v=office.12%29.aspx
http://msdn.microsoft.com/en-us/library/office/ms425565%28v=office.12%29.aspx

[vojo]

agreed.....until she can get familiar with If-then-else, there is no point even talking about SETATREF or SETF

[Michelle]

Sorry, As I said I am rusty.  I am familiar with If-Then-Else. 

Sorry for more questions as well.  Vojo, you explained the marker a little more, but I guess I need a little more hand holding 

"cell user.cost10marker = if(inputcost = 10, true, false)
cell user.cost20marker  = if(inputcost =20, true, false)

cell user.cost = guard(if(user.cost10marker,"$10.00",if(user.cost20marker,"$20.00,"err")))"

^^^are these done in user defined cells? And is "err" one of the values?  Our costs are set, so I would have no need for an "err", the user needs to make choices from a set list, there will be no "errors".  (Unless they choose the wrong one, in which case the cost will still be correct for the one they chose...)

cell user.cost10marker = if(AND(inputcost <20, inputcost>10), true, false)
cell user.cost20marker = if(AND(inputcost<30, inputcost>20),true, false)
and add say
cell user.costothermarker = if(AND(user.cost10marker = false, user.cost20marker = false),if(inputcost= 59.23, true,false),if(inputcost = 99.99, true,false)

^^^Just to be sure, are you showing values of 59.23 & 99.99 assuming my end user will be typing in values?  They will be choosing from fixed lists.  Part A is blue and costs $10.00, etc.  I just need to have the option for them to choose the part by any one of these variables. (and have all the other variables change appropriately)  They will not be typing in any info.

Thanks for any help!  True, I am far far from being as knowledgeable as most of you here, but I am not a total hack (I hope  )  and you guys have helped me a few times before when things seemed hopeless!  I'm just hoping y'all can help me again!

Michelle

[Michelle]

Oh, and I'm jumping in to the SETATREF function to see if I can make it work.  Wow, you aren't kidding, it's not easy... my brain hurts!  I *need* to figure it out, though. 

Does anyone have any examples of this at work you'd be willing to attach here or point me to so I can keep chugging along in trying to create a solution to my issue?

Thanks!
Michelle

[wapperdude]

To put a slightly different spin on the approach, how about a look up table(s).  Certainly a single table with various search criteria in Excel would do the trick.  Then, need to import the data.  But, using, perhaps 3 tables (don't know if it could be done with a single table) in Visio and using the index function, that might be a way to go.  The idea would be to use the user input as the search criteria for the appropriate table, and then use the table entries to populate your variables.

Anyway, just a thought.

[Michelle]

You have seriously piqued my interest, wapperdude!!!  The data I am working with comes to me in excel files... I am not familiar with lookup tables, however.  Where can I learn more?  This might be just what I need.

Thank you so much for any help!
Michelle

[wapperdude]

One place to start is here:  http://msdn.microsoft.com/en-us/library/ms406635(v=office.12).aspx

Check out the functions Index and Lookup.

There probably examples here in the Forum:  search for index and / or lookup.  VisioGuy's website has some examples too, I believe.
In fact, from VG's website, http://www.visguy.com/2009/05/06/top-twelve-text-tips/ and scroll down to tip number 10.

HTH
Wapperdude

[Jumpy]

Index and Lookup ShapeSheet-functions are the key to avoid nested "If-Statements". Simple example: If you only could choose the color and depending on that the other two values are set you could use sth. like:

cell user.cost =Lookup(Index(Prop.Cpolor,Prop.Color.Format),User.Cost.Prompt)

where Prop.Color.Format holds the fixed list of colors and User.Cost.Prompt holds a similiar list of costs.

------------

But that is only the simple case. You want to make it possible, that it doesn't matter which value is changed (color, cost, ...) the other values are changed accordingly. My guess is, that won't work without SETATREF and family. Maybe you could work with SETF and DEPENDSON and hope that Visio resolves the circular expression somehow (Visio is rather intelligent in that regard). Graham Wideman has good examples of that in his book if I remember it correctly.

Otherwise search the blogs of the Visio MVPs for examples that use SETATREF.

[wapperdude]

I agree that SETATREF is useful in resolving circular arguements and is difficult to use / understand.

My question would be, can the circular arguement be avoided?  For example, the user chooses his "identifier", e.g., a color.  That could set a user.identifier variable.  This variable could then be used with Lookup and Index functions to determine the entries for all of the variables for that particular entry row.  That is, each variable that Michelle identified in her example is driven by the derived index value.  I believe this all flows down and does not invoke any circular arguements.  This may be an over-simplification, and I may be missing some detail, but, I give it as a possible approach to consider.

Wapperdude