Referencing a Variables script in my Load Script

[Markbhai]

I have 3 environments Dev, UAT and Production and my objevtive is to store a script file in each of these envirnmenyt to help me to set some envirnmental related variables - the benefit being that these are outside of my load script so that I can promote dashboards through the envirnments with little fuss.

So for example I hope to hold the connection name in the script and refer to it as $(vConnection) etc.

I understand that I need to create a text file for my script which I have done (Environment.txt) and this is stored in the root of the Space I have (Live - PP) my connection name is called 'PP-prod'

In my Load script I have added:

$(Must_Include='lib://Live - PP:PP-prod/Environment.txt'); 

I have also tried:

$(Must_Include='lib://:PP-prod/Environment.txt');

$(Must_Include='lib://Live - PP:/Environment.txt');

$(Must_Include='lib://Live - PP:PP-prod/DataFiles/Environment.txt');

as well as the above with the file named Environment.qvs

I get the error

$(MUST_INCLUDE= *** cannot access the local file system in current script mode. Try including with LIB path.

Can anyone advise what I might be doing wrong?

Thanks

Mark

[TauseefKhan]

Hi @Markbhai,

The Must_Include statement will throw an error if the specified file cannot be found or fetched. This is useful when you want to ensure that the file is essential for the script to run correctly.

Use the Include statement: (Use Include in place of must_include)

$(Include=lib://Live_PP/Environment.txt);


** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

[Lech_Miszkiewicz]

@Markbhai 

Must Include shoudl still work and use of just Include is risky as in case of error it will be just omited.

Try loading your text file as a table (just like you would load data by browsing your connections and navigating to pick "all" file types) so then the proper path gets generated and you can copy/paste it into include statement.

cheers

[Markbhai]

I now know that I have the correct path because I am selecting using the Data Load Editor.

I still get the same error though.   😞

Plus: If I click on the icon next to the statement I can see that the script is loading:

[Lech_Miszkiewicz]

what is the content of your environment file? maybe you have some references in it which are causing the issue

[Markbhai]

Only two lines currently:

Let vENV = 'Prod'; //sets the environment value
Let vConnectionName = 'Live - xxx:xxx_prod' //sets the name of the Database connection

(Obviously xxx is a redaction) 🙂

M

[marcus_sommer]

I think you need to use a relative-path instead of a full-path, like:

$(Must_Include=..\..\..\Include_MainPath.txt);

I don't know if this is possible within the standard-mode but with the legacy-mode it should be.

An alternatively might be to query the calling system and deriving from it the essential information for the next variables, maybe like:

let vEnv = pick(match(computername(), 'x', 'y', 'z'), 'Prod', 'Dev', 'UAT');

[Markbhai]

Thanks Marcus.

I have progressed a little now, thanks to your help.

I now have the file working.  It seems that I can't reference the Space and Database in the script as for some reason the LIB CONNECT TO does not appear to like special characters in the variable (it is rejecting the colon in the string)

I will continue with other aspects but feel trying to get the connection to be set by the script is too challenging.

Thanks for your help.

[marcus_sommer]

Yes, special chars could be challenging. A workaround might be to concat multiple parts, like:

let vFinal = '$(var1)' & 'SpecialChar' & '$(var2)';

Another approach to avoid an unwanted evaluation could be to load the information as a field-value, like:

t: load String from MyExcel;

let vFinal = peek('String', 0, 't');  

[Markbhai · Accepted solution]

OK, I have it working, here is what I ended up doing:

Create an 'environment.txt' file containing your variables, in my case I needed two variables, vConnect and vDBTable, the first held the connection string to the database, the second contained the database name, which I use because in my case the db names differ across environments.

Because the connection string contains special characters I needed to put this in the text file inside single quotes AND double quotes - Let vEnvironment = '"LivePP:PP_prod"';

The other issue I experienced was getting the txt file in the right location.

Within the Live Space click on the Select Script icon:

Click on the 'Drop file here' icon to upload your txt file and then insert your file and 'Insert script'

I can then add

LIB CONNECT TO $(vConnect) to my dashboards, which connect to my DB

I create a 'environment.txt' file for each environment (DEV, UAT and PROD) each containing the correct connection string and DB Name for the environment.

With the variable in the dashboard Load Script instead of hard coded names, I can now create a dashboard and just copy it to the next environment and it will work without any further editing.

Thanks