Is this possible with SS formulas: append string every time cell changes

Started by Visisthebest, November 20, 2021, 03:26:58 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Visisthebest

I don't know enough about the shapesheet to know if this is possible:

In a shape data cell, there is a dropdown with several options.

Every time the user changes this cell, I want the string that is selected to be appended to a string in another cell with a semicolon ; before it.

So if a user first selects Coffee and then Bagel from the dropdown, the string in the other cell becomes:
;Coffee;Bagel

(I will remove the first ; in code before using the semicolon-separated string)

Is this possible with some shapesheet magic?
Visio 2021 Professional

Surrogate

 it is possible with SETF function magic

Visisthebest

Visio 2021 Professional

Visisthebest

Visio 2021 Professional

vojo



props.field1 = <coffee or tea or or or>

user cell = setf(getref(user.result), user.result & ":" & props.field1) 

                                       syntax = setf(getref(<cell you want to put value into>), <formula that creates the value>)

1st time

user.result = "coffee"

2nd time

user.result = "coffee:tea"

3rd time

user.result = "coffee:tea:beer"

over iterations, this will blow up to "blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah:blah"

Visisthebest

Thank you Vojo very clear how I can use SETF(GETREF()) to keep appending to a string.

This field is not often changed but it allows me to track the changes the user makes.

What I don't have a full picture on is when SETF(GETREF()) is triggered, from your example any change in a referenced cell (in this case prop.field1) triggers another evaluation of the SETF(GETREF()).

Microsoft writes about GETREF:
"References a cell and doesn't recalculate the formula when the referenced cell changes."

https://docs.microsoft.com/en-us/office/client-developer/visio/getref-function

That threw me off a bit but of course this is about SETF which DOES recalculate when a referenced cell changes (unless embedded in a GETREF)

So I assume that if a user does not use GETREF in a SETF, just a string referencing a cell (which I understand is possible but not recommended), then that reference, if the target cell is changed by something else than this SETF (or you'd get circular triggering), does trigger a recalculation of SETF.

Again thank you this is some great shapesheet magic!  :D
Visio 2021 Professional

vojo

use dependson

user.mycell = dependson(<trigger cell>) + setf(getref(<some other cell>), <calculate value>)