Can you search column formulas?

[#1]

I have a data table with a couple hundred columns. I would like to be able to search through all the column formulas to see if a particular column is used in any of the column formulas. Is this possible?

[#2 · Accepted solution]

Yes......

Names Default To Here( 1 );

dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/JMP/JMPPRO/19/Samples/Data/Big Class.jmp" );

dt << new column("Ratio", formula(:height/:weight));

theFormula = Char(dt:Ratio << get formula);

If( contains(theFormula, "height"), show("found it"));

[#3]

Jim,

Thanks for this. Since I am really poor at scripting at this point, could you show me how I can have your algorithm loop through all the columns in a data table and output a list of columns that contain the target value?

Jarmo,

Unfortunately, I don't have JMP Pro. For some reason, the error message I get when I try to delete the column does not list the column containing the formula. I don't know why. I also thought that the data dictionary might be able to help but I couldn't see how once I looked at it. Thanks for the assistance though.

[#4]

I also shared one more option which isn't dependent on JMP Pro

You can select the formula columns, select copy columns, open new script (ctrl + t) and paste there. Then search in script editor.

[#5 · Accepted solution]

Ohhhh... That's cool. I didn't know you could paste a column into the script editor.

I think that will work.

Thanks!

[#6]

If you have access to JMP Pro you could do this with Formula Depot.

You can select the formula columns, select copy columns, open new script (ctrl + t) and paste there. Then search in script editor.

You could also try removing that column and if it is being used, JMP will display warning like

Edit:

There might be also some way in JMP18 utilizing Columns Manager but I could figure one out. Data Dictionary almost can do it but it stores the formulas as images so they cannot be searched easily

And of course there is plenty of pure scripting options

[#7 · Accepted solution]

Similar to @jthi's suggestion to copy the script of each column, you can copy the script of the entire table without the data using the Red Triangle (at the top left of the table) > Copy Table Script (no data).

The script will contain the jsl to recreate your table, which will include all your column formulas. Paste that script into a script window, and search from there. 

I hope this helps!

@julian 

[#8]

@julian maybe this type of searches could be implemented to Column Manager in some capability? So we could also search for the values within Column Properties instead of just filtering down to those which have specific properties. At least Formula, Notes and Short/Long column names are few column properties which could possibly benefit if we could search from.

[#9]

I like it—Columns Manager feels like a natural place for that. I was also thinking of how it might work within the ordinary Find dialog if there were another checkbox to offer “Find in Column Properties.”