How to delete reference of formula column in JSL

[#1]

Hello expert,

I want to delete reference of formula column in JSL.

Manually, after I selected the columns to be deleted, I can click "Remove Formula" after JMP Alert prompted.

But, when I copied the workflow script & put it in my script, the reference of formula column still not deleted.

Any suggestion of how to move around? I'm using JMP 17

Thank you in advance

[#2]

I'm assuming that you are using the workflow builder. Have you tried recording yourself deleting the formula in the Workflow Builder? This should remove any dependencies. You could also do it via JSL using the example below.

[#3]

Yes. I used workflow builder. 

1)However, the workflow builder did not record "Delete Formula". Instead, it just recorded "Delete Columns" although I've selected "Remove formula" when the JMP Alert prompted.

2) I tried "Delete Formula" as well in my script but it didn't work.

Below is my script;

//Add new column formula to change Wirebond, 2nd Wirebond, Diebond and 2nd Diebond to correct date format
Names Default To Here( 1 );
dt4 = Data Table( "DB.WB(Datasheet)" );
dt4 << New Column("Wirebond.End Date", Numeric, Formula(
			(As Date( In Days( :Wirebond End Date) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("Diebond.End Date", Numeric, Formula(
			(As Date( In Days( :Diebond End Date) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("2nd Wirebond.End Date", Numeric, Formula(
			(As Date( In Days( :"2nd Wirebond End Date"n) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
dt4 << New Column("2nd Diebond.End Date", Numeric, Formula(
			(As Date( In Days( :"2nd Diebond End Date"n) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 ))));
//Change column info: Wirebond.End Date
Data Table( "DB.WB(Datasheet)" ):Wirebond.End Date <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: Diebond.End Date
Data Table( "DB.WB(Datasheet)" ):Diebond.End Date <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: 2nd Wirebond.End Date
Data Table( "DB.WB(Datasheet)" ):"2nd Wirebond.End Date"n <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Change column info: 2nd Diebond.End Date
Data Table( "DB.WB(Datasheet)" ):"2nd Diebond.End Date"n <<
Input Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 0 )
 << Format( "Format Pattern", "<D></><MM></><YYYY><hh><:><mm><:><ss><ampm>", 20, 0 );
//Remove data with End lot time < Today - 30 days (not required for analysis)
Current Data Table() <<  Select where( :End Lot Time < (today () - in days(30))  ) << Delete Rows;
//set Label for Machine No, Lot No, Base Part (Device), Recipe, Diebond & Wirebond MC
dt4 << set label columns( :Machine No, :Lot No, :Device, :Recipe, :Diebond MC ID, :Diebond End Date, :Wirebond MC ID, :Wirebond End Date, :Package, :Package Platform );
//Delete columnsCurrent Data Table() << Data Table( "DB.WB(Datasheet)" )<< Delete Formula (:Diebond End Date, :"2nd Diebond End Date"n, :Wirebond End Date,:"2nd Wirebond End Date"n);

The log is as below

[#4]

You might be looking for Delete Formula.

From the scripting index:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Bank Loan.jmp" );
:Time << Delete Formula;

[#5]

The script for "Delete Formula" didn't work.

Strange. It states the column has no formula when running script.

But, when I select the column manually to delete, it states "cannot delete column while it is referenced by the formula of other column"

[#6 · Accepted solution]

"Diebond.End Date"

is the column with the formula that refers to 

In Days( :Diebond End Date)

Try deleting the formula from the column with the period in the name.

@Jed_Campbell  - that very first dialog, "cannot delete the selected columns while..." should add something like

 Diebond End Date can't be deleted because it is in use by the formula for Diebond.End Date, ... etc

[#7]

Thanks @Craige_Hales .

It works. I'll put a wishlist for the workflow on this matter.

//Delete columns
dt4 << Data Table( "DB.WB(Datasheet)" );	
Wait(0);
dt4:Diebond.End Date << Delete Formula;
dt4:"2nd Diebond.End Date"n << Delete Formula;
dt4:Wirebond.End Date << Delete Formula;
dt4:"2nd Wirebond.End Date"n << Delete Formula;
Wait(0);
dt4 << Delete Columns (:Diebond End Date, :"2nd Diebond End Date"n, :Wirebond End Date, :"2nd Wirebond End Date"n);