Variables which reference other variables in the script

[sam_grounds]

Hi all,

I've had a pesky issue in the past with the dollar expansion. A great feature, but can cause problems if you don't know how to avoid it... I often want to write a large number of variables, which I paste into my script, sometimes using a program like excel to use a formulaic pattern to writing them.

An example would be this...

Variable name: vSales

Desired value: num(sum({$<[Transaction Date]='$(vDate)'}>}Sales),'£#,##0')

There a couple of problems with this approach, the first of all is the use of single quotes. If you want to write a single quote into a variable using the script, use the chr(39) function instead.

Simply replace all single quotes with this

'&chr(39)&'

(including the single quotes)

Secondly, the dollar expansion will evaluate the variable there and then and write it in, in place of the variable name, rather than allowing it to reference the variable directly.

In order to stop the dollar expansion evaluating the string contained in the variable, simply break the dollar and opening bracket up. Like so...

$'&'(

Simple, yet effective.

So the best way I have found to write this into the script would be...

LET vSales_Yesterday='num(sum({$<[Transaction Date]={'&chr(39)&'$'&'(vDate)'&chr(39)&'}>}Sales),'&chr(39)&'£#,##0'&chr(39)&')';

This will create a variable...

vSales

which contains...

num(sum({$<[Transaction Date]={'$(vDate)'}>}Sales),'£#,##0')

This approach basically tweaks what is being evaluated the first time (by the script) to produce a string which later on when it is evaluated it can repeatedly use the other variable in the front end expressions.

Hope this helps someone else who encounters the same issue.

Sam

[millerhm]

@sam_grounds  - four years later, you've saved me from tearing all of my hair out or banging my head on my desk one too many times - many, many thanks!

[jda_bryan]

@sam_grounds  Same here.   Nice tip.   Thanks!