Replace the n-th element of a semi-colon separated list.

[Yacine]

The task is to replace a certain item in a semi-colon separated list.
We know already from Chris how to count the items, but replacing an item is yet not solved - and MS would rather give as a tint and shade, instead of useful function , like a (see the title of the post)

As there are no FOR-NEXTS in the shapesheet functions, the idea is to let Visio iterate by iteself through an own FOR loop.
Here the formulas:

Code Select Expand

User.Counter = 0
User.Start = False ... use this field to start your computations
User.GetString = IF(User.Start,SETF(GetRef(User.String), Prop.Categories) +SETF(GetRef(User.Counter),User.Position)  +SETF(GetRef(User.Start),FALSE),FALSE)
User.String ... gets set automatically
User.Next = FIND(";",User.String)
User.Truncated = RIGHT(User.String,LEN(User.String)-User.Next)
User.Truncate = IF(User.Counter>0,SETF(GetRef(User.String),User.Truncated) +SETF(GetRef(User.Counter),User.Counter-1),FALSE) ,TRUE)
User.Position = 4 ... the position you are trying to get -1...
Prop.Categories = "A;BB;CCCCCCCC;D;EEE;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM"

This should give you a String
User.Truncated = "EEE;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM"

Now you could look for the next list separator and extract "EEE", get the right part.
And reassemble LeftPArt & "Replacement text" & Right part. done.

Now the sad news... Visio resigns in going through the loop, interpreting it as infinite and gives something out lile "M"!

Doing it by adding a step property, so V. does only one replacement at a time worked fine. it's the loop that is misliked.

I simulated other loops and got a rate of appr. 42 iterations before V. resigns. My method looks shorter, but still gets stopped by the "infinity switch".

Any advice?

[Nikolay]

The task is to replace a certain item in a semi-colon separated list.
Any advice?

Hello Yacine,
Provided all items are different, this sems to be pretty stringhforward:

Code Select Expand



User.R1=SUBSTITUTE(";"&Prop.Categorie&";", ";"&INDEX(User.Posistion, Prop.Categorie, ";")&";", ";"&User.NewValue&";")
User.R2=MID(User.R1,2, LEN(User.R1)-2)



Explanation

Assuming:
Prop.Categorie = ";A;BB;CCCCCCCC;D;EEE;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM;"
User.Position=4
User.NewValue="ZZZ"

";"&Prop.Categorie&";" => (add semicolons at head and tail)
";A;BB;CCCCCCCC;D;EEE;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM;"

INDEX(User.Posistion, Prop.Categorie, ";") => (find item text to replace)
"EEE"

";"&INDEX(User.Posistion, Prop.Categorie, ";")&";" => (add semicolons at head and tail of item text)
";EEE;"

SUBSTITUTE(";"&Prop.Categorie&";", ";"&INDEX(User.Posistion, Prop.Categorie, ";")&";", ";"&User.NewValue&";") => (replace as text)
";A;BB;CCCCCCCC;D;ZZZ;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM;"

MID(User.R1,2, LEN(User.R1)-2) => (remove extra semicolons at head and at tail)
"A;BB;CCCCCCCC;D;ZZZ;F;GGGGGGGGGG;HH;I;JJJJJJJJJJJJ;KKK;LLLLLLLLLLLLL;MMMMMMMMMMMMMMM"


Am I missing something?

[Yacine]

Hi Nikolay,
thanks, your solution does exactly what I wrote   , but not what I meant  
My example string is misleading  
It's the n-th element I want to replace, not all the elements, that are the same as element n.

A;BB;CCCCCCCC;D;E;F;GGGGGGGGGG;HH;I;E;KKK;E;MM
Ask to replace the 5th element
you get:
A;BB;CCCCCCCC;D;ZZZ;F;GGGGGGGGGG;HH;I;ZZZ;KKK;ZZZ;MM
Expected
A;BB;CCCCCCCC;D;ZZZ;F;GGGGGGGGGG;HH;I;E;KKK;E;MM

[Nikolay]

Hi Nikolay,
thanks, your solution does exactly what I wrote   , but not what I meant  
My example string is misleading  
It's the n-th element I want to replace, not all the elements, that are the same as elment n.

Yep, this won't work for the case when the list contains duplicate items - that's why I wrote "provided that all items are different".
But what is the big idea of keeping duplicate "Kategorie" items in the list anyways? 

[Yacine]

The idea came from Tulm's question and Jumpy's answer:

One idea to assign IDs could be to store the current "maximumID" in a user defined cell of the page. Now if a new shape comes into play and is worthy of a new ID your code can lookup the maxID in the page, increase it by 1 or a random number, save it again in the page and then assign it to the shape.

.
Now I don't want to have only 1 ID, but several one's as in Visio's P&ID solution, where valves, instruments and pipes get their own numbering.
To keep the solution flexible, I don't want to hard code the categories, nor their quantity, but put them in a semi-colon separated list.
If I want to assign an ID to a shape, I would look for it's category, get the according category number, the max ID, write the next higher value in the shape and write on page level that incremented list member back.

There should be many other applications as well...

[Nikolay]

I think in context of that topic VBA seems to be okay..?
Means, it seems taht with VBA this task can be done in a few lines of code - it does have cycles anyways
Also, I'm just willing to notice that besides "ID" Visio shape has "UniqueID" (GUID) to uniquely identify it...

Though it seems that I've got your basic idea here (?),
I don't know how to properly implement recursive formulas that would "emulate" cycles in shapesheet (and if it is possible at all).
Means - screw the badge, if I were him I would just do that with VBA

===

- Georgia, you said you wanted to do this.
- I did.
- You signed up to sell a hundred boxes of cookies. You do it. We are going outside to sell these cookies.
- It's too hot.
- Now.
- It's too stressful.
- We need to move these.
- I just wanted the stupid badge!
- You can't get the badge until you sell the cookies.
- Screw the badge.

(c) DLM

[Yacine]

I agree with you, it's not reasonable to do it in the shapesheet. But it's the challenge.