Set the value of a variable when selecting a value in a Listbox

[salto]

Hello,

I'd like to set the value of the Currency named variable to €, when the value in a ListBox changes to 'Consolidation'. Is this possible to perform with a Macro? I have tried with Sheet Object Event Trigger "Set Variable", but it changes the variable to € whatever value I select in the List Box.

Any idea will be appreciated.

[jonathandienst]

Hi

Rather than using a macro or trigger, I suggest that you use an expression in the variable. Something like:

=If(only(field1) = 'Consolidation,'€', '$')

Change field1 to match your model.

This should work as long as only one value for field1 is selected. If multiple values are allowed, you could use GetFieldSelections instead.

Jonathan

[salto]

Thanks Jonathan, sorry for the delay. Where should I use the expression for the variable?

I have tried by writing the expression you provided in the variable definition for the document (Document Properties -> Variables). But no luck, the behaviour is not the expected one, because the Currency variable does not change when I change the selection in the ListBox (from Consolidation to No Consolidation and viceversa).

I'll keep on trying...

[jonathandienst]

Hi

I suggest that you first test the expression in a text box. Assuming that the expression variable has a leading '=' sign, as per my post, then put =vCurrencySymbol into the text box Then check whether the expression is working correctly.

(If you define the variable without a leading '=', then you can define the text box =$(vCurrencySymbol))

Once the expression works correctly, you can use it wherever you need the currency symbol.

Regards

Jonathan

PS - if you cannot get the expression to work, I suggest that you post your model so that we can look at this more closely.

[salto]

Hello Jonathan,

the expression works perfectly in the textbox: it shows $ or €. But the variable in the inputbox does not change. Should I set up the variable in the load script?

Thanks.

[jonathandienst]

Hi

I am a little confused. The expression calculates a value when you change the selection in a list box. Where does the input box come into this?

Jonathan

[salto]

Sorry, it has been my fault: the variable is used in an input box to select the currency in which the sales amounts have to be shown.

The goal is to make the currency value to be € (and never $) when the "Consolidation" value is selected in the listbox.

I apologize for not explaining myself clearly.

[jonathandienst · Accepted solution]

OK that explains it.

Yes, you were right about needinga trigger - you will need to an OnSelect field event trigger. Use a Set Variable action and put the variable that should contain the currency symbol in the Variable box and enter the expression =If(only(field1) = 'Consolidation,'€', '$') in the Value box.

(Obviously with the correct field name in place of field1).

Remember that this expression only works if only one value is selected in field1.

Jonathan

[salto]

That did the trick. Many thanks!