nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Exclude data when loading data with a script

bjbreitling

Hi community,

When I open a table with a database with the following script saved to the datatable it works. When I save the script to a menu item in an addin it just pulls the data and doesn't exclude. Whats up?

Open Database(

"DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",

"SELECT * FROM dbo.NSI_Discovered_Factors_Lab_Data",

"NSI_Discovered_Factors_Lab_Data",

"NSI Database"

);

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

Accepted answers

Jeff_Perkinson

Thanks for the clue about it working when run from a script editor but not as an add-in.

That usually indicates a scoping problem for variable references in your JSL. By default, add-ins run with the "Names Default to Here(1)" option, so that un-scoped references go to the "Here" namespace.

Your Select Where() has an un-scoped variable in it in the form of Exclude.

If you include a : before it to scope it to a data table column it works in both cases (script editor and add-in).


dt<<Select Where(:Exclude=="Exclude")<<hide<<Exclude;

-Jeff

All comments

Byron_JMP

Its possible that the Open isn't complete when the Select is executed?

I haven't seen this exact problem, but something like it when I'm working with tables that have lots of rows.

Two suggestions:

First, assign the data table to a variable when its opened. This way dt=current data table(); doesn't accidentally end up pointing at the current table, while the database table is opening.

dt=open database("DNS....

Leave out the dt=current data table();

Second, force dt to be evaluated before selecting rows. It can't be evaluated before it finishes opening

dt<<Run Formulas; //include this first

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

bjbreitling

Hi Byron,

That didn't fix it. I also made a separate addin that should work given what you said is true which I execute after the loading of the dataset which is just the following.

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

This oddly doesn't work either.

pmroz

Try using wait(0) instead of run formulas. I agree with Byron - remove the dt = current data table and set dt = to the results of open database. Like this:

dt = Open Database(

"DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",

"SELECT * FROM dbo.NSI_Discovered_Factors_Lab_Data",

"NSI_Discovered_Factors_Lab_Data",

"NSI Database");

wait(0);

dt << Select Where(Exclude=="Exclude") << hide << Exclude;

bjbreitling

Same issue PMroz. Oddly it still doesn't work.

bjbreitling

Hi Byron,

When I copy and paste the query into a script which is exactly the following:

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

It works. But when I save the script to a jsl addin, it oddly doesn't. The column name and case and spaces match. So does it for the rows. Its very odd that it works in non-addin jsl but not in addin. I didn't generate this jsl form the data filter.

Jeff_Perkinson

Thanks for the clue about it working when run from a script editor but not as an add-in.

Your Select Where() has an un-scoped variable in it in the form of Exclude.

If you include a : before it to scope it to a data table column it works in both cases (script editor and add-in).


dt<<Select Where(:Exclude=="Exclude")<<hide<<Exclude;

-Jeff