deleting column with refs

[#1]

Hi

I wrote a script to delete a column.  However, I am getting error that " Cannot delete this column while it is referenced by the formula to another column. "

There are no formula, but if use "Remove Refs" option manually, I can delete column manually. 

I need to learn how to use this function in my script to use "Remove Refs"

I am using JMP 16.1.0  

[#2]

can you supply a sample data table?

[#3]

I wonder if there is a hidden formula column? If there is really no formula column, it sounds like a bug, and sending the table to tech support could help get it fixed.

[#4]

You might want to step back and consider the approach that you are using. Instead of fixing this problem, it might be avoided in the first place. For example, why are you using a formula in the other data column? Why do you want to delete a column?

You might be able to use other ways to populate a data column that do not involve formulas at all. The only reason to use a formula when scripting JMP is if you need the data column to update when the data table changes. There are otherwise other ways.

Are you able to post your script? Or describe the steps your script takes?

[#5]

Is the issue related to Summary table?

I did just today notice some weird behaviour with summary tables when I tried to remove formulas. I'm currently testing the issue out and will send ticket to JMP support after I a proper example. I can also post the example here after it is ready.

Edit:

Here is quick example about the issue I came across today (I fairly rarely use formulas...):

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

//create some formula colums
dt << New Column("deduction", Numeric, "Continuous", Format("Best", 12), Formula(:height - :weight));
dt << New Column("concat_col", Character, "Nominal", Formula(:name||"_"||Char(:name)));

dt_summary = dt << Summary(
	Group(:name,:sex, :concat_col),
	Mean(:height),
	Mean(:deduction),
	Freq("None"),
	Weight("None"),
	Link to original datatable(0)
);

Try(
	dt_summary << Delete Column("name");
,
	show(exception_msg);
);

Try(
	f = Column(dt_summary, "concat_col") << Get Formula;
	show(f);
	Column(dt_summary, "concat_col") << Delete Formula;
,
	show(exception_msg);
);
/*
Cannot delete column "name" while it is referenced by the formula of column "concat_col".
Removing a formula leaves the data unchanged. Removing references replaces each reference with an empty value. These effects are permanent and cannot be undone.

f = :name || "_" || Char(:name);
exception_msg = {"Column concat_col has no formula"(1, 2, "Delete Formula", Delete Formula /*###*/)};
*/

Formula not visible in column listing on the left and formula not visible in Formula panel box either.

The formula is there if I click Edit Formula. And if I change "_" in concatenate to " "  the formula disappears, column gets locked and values do get changed in datatable:

Simple fix in this case could be to first delete all formulas from original data table before creating the summary table, but I would think that formulas shouldn't be copied over (just values) to summary table, as it might have some very weird behaviour depending in the formulas.

Edit2:

I have now sent ticket about this issue/feature I described to JMP Support.

[#6]

The problem I reported to JMP Support has now been disclosed as a bug and has been reported to R&D.

Because it is possible to remove formulas interactively, I got an idea that you could use << Delete property("Formula") instead of << Delete Formula and it seems to work, but I would be very careful with formulas when creating Summary tables as they might have some very weird interactions

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

//create some formula colums
dt << New Column("deduction", Numeric, "Continuous", Format("Best", 12), Formula(:height - :weight));
dt << New Column("concat_col", Character, "Nominal", Formula(:name||"_"||Char(:name)));

dt_summary = dt << Summary(
	Group(:name,:sex, :concat_col),
	Mean(:height),
	Mean(:deduction),
	Freq("None"),
	Weight("None"),
	Link to original datatable(0)
);

Try(
	f1 = Column(dt_summary, "concat_col") << Get Formula;
	show(f1);
	Column(dt_summary, "concat_col") << Delete Formula;
	f2 = Column(dt_summary, "concat_col") << Get Formula;
	show(f2);
,
	show(exception_msg);
);

Try(
	f3 = Column(dt_summary, "concat_col") << Get Formula;
	show(f3);
	Column(dt_summary, "concat_col") << Delete Property("Formula");
	f4 = Column(dt_summary, "concat_col") << Get Formula;
	show(f4);
	dt_summary << Delete Column("name");
,
	show(exception_msg);
);

/*
Close(dt_summary, no save);
Close(dt, no save);
*/