Setting variable in Script not evaluating as expected

[mathewwp]

Hi Everyone,

I'm looking to see if anyone could fix my problem.

I can create a variable on the front end of my app, I call it ThreeYears and in the expression editor it is defined as so:

='>=$(=monthstart(addmonths(max(MonthYearComplete),-35)))<=$(=addmonths(monthend(max(MonthYearComplete)),0))' 

MonthYearComplete is a field whose values are in the form MMM-YYYY.

The above returns >=01/02/2018<=31/01/2021 which is the result I want

Instead I would like to define this in script but when I do the following:

LET ThreeYears = '>=$(=monthstart(addmonths(max(MonthYearComplete),-35)))<=$(=addmonths(monthend(max(MonthYearComplete)),0))' 

it returns >=<=

How do I change the Script so that it returns >=01/02/2018<=31/01/2021 ?

[agigliotti]

Hi @mathewwp ,

You should replace $ symbol with chr(36).

ex. LET ThreeYears = '>=' & chr(36) & '(=monthstart(addmonths(max(MonthYearComplete),-35)))<= ...

Best Regards

[marcus_sommer · Accepted solution]

Your issue is caused from the $-sign expansion within your variable value - it's evaluated on the run-time and becomes therefore NULL. This couldn't be directly avoided else you need any workaround. This could be to double replace it with another char and back again, like:

let varTemp = '... #(= ....) ....';
let varFinal = replace('$(varTemp)', '#', '$');

Another way would be to load the variable-content as field-value from an external source and then assigning those field-value to the variable with functions like peek() or fieldvalue().

The first workaround is possible but rather ugly and not really better as assigning the needed variables within the UI. The second workaround needs a bit more efforts but by many variables it's a quite practically approach.

- Marcus 

[mathewwp]

Thanks for you answers Marcus and Agigliotti.

When I went for your answer Marcus, it evaluated correctly at the front end and it's a very practical approach.