Exclude data when loading data with a script

[#1]

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;

[#2]

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;

[#3]

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.

[#4]

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;

[#5]

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

[#6]

Here's a thought.  Since you're pulling the data with SQL, why don't you exclude the rows in SQL?  Also I think you have an extra parameter to Open Database (NSI Database).

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 WHERE exclude != 'Exclude'",

  "NSI_Discovered_Factors_Lab_Data",

  "NSI Database");

[#7]

That is an option PMroz, but I would prefer pulling the all the data and leave folks the option to un-exclude.

[#8]

There may be a couple of other little things to check, and I apologize in advance for being so basic. 

The imported data table has a column named "Exclude"

-does the actual column name have any leading spaces?

-does the actual column name match? e.g. title case and spelling.

The values to filter on in the column named "Exclude"

-do the values in the column have any leading or trailing spaces?

-is the spelling and title case exact for the level "Exclude"?

The script in the data table works, did it come from the Data Filter?

[#9]

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.

[#10 · Accepted solution]

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