Conditional Fixed List

[dharris]

I am sure this has been asked before, however I cannot find a result for it.  I am sure it is something you folks have figured out and I am just too much of a newbie to dial it in.

I want a user to select something in a fixed list and a subsequent list to be 'tailored' to that selection without using a macro.

Something like the result of this.
Label - User
Type - Fixed List
Format - Dave;Joe;Sam

in the next field, depending on the selection of Dave, Joe or Sam the user sees what they can do and selects from that list what they want them to do.

Label - Duties
Type - Fixed List
Format - ?


Dave (West Hall; Ground Floor; Beacon Hall)
Joe (Main Hall; Front Porch; Greeter)
Sam (Landscaping; Ceiling Tile; Smoke Alarms)

So if in the the user selects 'Dave' in the user field, the only 'duties' available in the 'Duties Field' are "West Hall; Ground Floor; Beacon Hall" -

Help?

[Surrogate]

May be this article by Croc can helps

Hope this article can help...

[dharris]

This seems like a logical solution - however, I do not 'know' where the 'Scratch section' is, when in developer mode, with the shape sheet exposed, for the shape - do I create a user-defined cell called 'scratch' and place the commands therein?

Scratch.A1=LOOKUP(Prop.Prop1,Prop.Prop1.Format)

with my values in B1?
Scratch.B1="West Hall,Ground Floor,Beacon Hall;Main Hall, Front Porch, Greeter;Landscaping,Ceiling Tile,Smoke Alarms"

In the example - the 2nd window with the scratch data and where/how to place it is not well defined and I cannot find it, I have pasted the picture here.

It looks like it is in the shape data geometry, but I cannot read the top well enough, and I do not know how to 'insert' a scratch section into that.  From the example an X and Y are noted, I only see those in connectors or geometry.

Apologies, it looks like the right answer, but I am not executing properly for sure.

[dharris]

I figured out how to add the shape scratch section, and view it, however, it wont let me paste any of the example into the fields without beeping and giving an error

Just trying to paste this from the example - it will not allow in scratch column A this without error - =LOOKUP(Prop.Prop1,Prop.Prop1.Format)

I cannot recreate the example, if I could, I think I could figure out how to get what I need in.

https://gist-github-com.translate.goog/Surrogate-TM/b8fbe7b72297beb3c1bf111492a6fb37?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hl=ru&_x_tr_pto=wapp

This is the example it is driving me to, which I cannot recreate.
------------------------------------------------------------------------------------------------------

ShapeSheet solution
As mentioned above, the problem also has a solution at the shapelist level. Event processing is no longer required here; it is enough to select the required piece of data using formulas and insert it into the Format cell.

There are a lot of formulas required, but they are all packed into one line of the Scrath section. Let's look at the formulas in more detail.

image image

Scratch.A1=LOOKUP(Prop.Prop1,Prop.Prop1.Format)
The formula in Scratch.A1 calculates the position of the selected value in the first property's select list. In this case it will take the value 0.1 or 2.

Scratch.B1="value1.1,value1.2,value1.3;value2.1,value2.2,value2.3;value3.1,value3.2,value3.3"
Scratch.B1 contains a list for all possible values ​​for the second property. Note that the list uses different delimiters: comma and semicolon. This is important because in each specific case a sublist needs to be selected from this combined list. But Visio requires that the values ​​in the list be separated by semicolons. Therefore, the sampling is carried out in two stages. First, the second level list is selected as a single value (with commas). Then in cell Scratch.C1 the commas turn into semicolons and the list gets the desired format.

Scratch.C1=SUBSTITUTE(INDEX(Scratch.A1,Scratch.B1),",",";")
All that remains is for the SETF function to throw the resulting list into the Prop.Prop2.Format cell.

Scratch.D1=SETF(GetRef(Prop.Prop2.Format),Scratch.C1)
This is all. A solution at the shapelist level looks exactly the same to the user as a software solution. The formulas are a little more complicated, but there are no macros, which means there is no need to sort out the security issues associated with them.

[dharris]

Solved - thanks

[dharris]

The fix was it is not a reference of Prop.Prop1 (my property name) it is, in the shape scratch area, a reference to the Row # of the shape data table -

so, Prop.Row_1 - etc - then it worked fine.

kinda lame

[Surrogate]

My own solution works like