cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bio_grad
Level III

How to update column formulas

Hello,

 

I am working with files with ~300K rows with 85 columns (quite a few formula columns). I am wondering if there is a button or JSL command that can prompt all columns and their dependents to update.

 

I am finding that some columns are not automatically updating.

 

 

Thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: How to update column formulas

This statement will rerun all of the formulas.  You could add it to the tool bar if you want

current data table() << Rerun Formulas.
Jim

View solution in original post

markschahl
Level V

Re: How to update column formulas

Click on the red triangle to the left of the data table name (top left of the script area, just below toolbar).

Bottom of the dropdown that will appear: Rerun Formulas

Thanks to Jerry Cooper for teaching me that one!

View solution in original post

9 REPLIES 9
txnelson
Super User

Re: How to update column formulas

This statement will rerun all of the formulas.  You could add it to the tool bar if you want

current data table() << Rerun Formulas.
Jim
bio_grad
Level III

Re: How to update column formulas

Thank you!

Jeff_Perkinson
Community Manager Community Manager

Re: How to update column formulas

@txnelson gave you a good solution but I'm concerned about the circumstances under which the formulas are not being re-evaluated automatically.

 

@bio_grad, can you send any details about this to support@jmp.com? If there's an issue that needs to be addressed we want to look at it.

 

Thanks.

-Jeff
bio_grad
Level III

Re: How to update column formulas

Hi Jeff, I can't seem to replicate the issue now. What I think might have happened is that I accidentally clicked 'cancel' instead of 'apply' after modifying a formula without realizing.

Re: How to update column formulas

Jeff, I have seen this recently (JMP 14.3.0) on formulas which use the Date Difference function to calculate the time between a fixed date (column in the data table) and the current date (today()). An example file is attached. It was created today (6/14/19) and is fine, but if checked tomorrow or later the formulas will not properly evaluate until refreshed.

Jeff_Perkinson
Community Manager Community Manager

Re: How to update column formulas

@RobinFriedman thanks for the post. Formulas in data tables are not re-evaluated when a data table is opened. The thought is that the formula was evaluated before the data table was saved and nothing changed, so no need to re-evaluate.

 

Also, the data table doesn't treat formulas with Today() in them as special and constantly look at the date and re-evaluate.

 

So, in your case, if you want that column to be reevaluated when the data table is opened to reflect today's date, you'll need force it by adding a script named OnOpen to the data table.  That script will be run whenever the data table is opened.

 

You can use the Eval Formula message to a column to force it to re-evaluate. So the OnOpen script would be:

 

col<<Eval Formula

If you want to evaluate all the formulas in the data table when it is opened you can use the Rerun Formulas message to the data table:

 

dt<<Rerun Formulas

 

-Jeff

Re: How to update column formulas

@ Jeff_Perkinson

 

Belated thanks for your reply. Being aware of the lack of an automatic update of Today() has changed the way I use JMP. 

 

Are there more conditions you can share where formulas will not update? I have recently run into another case where JMP did not automatically update the formulas in an active table. I created a number of formulas like this, where the parameters are referring to table variables:

 

 

If( :Name( "Age" ) <= 2000,
        Parameter( {mu = :WBmu, sigma = :WBsigma}, 1 - Exp( -Exp( (Log( :Name( "Field Life" ) ) - mu) / sigma ) ) ) * :SampleCt
,
        1
)

 

 

The formula results do not initially respond to changes to the table variable values, but once I edit the formula in some way, the calculated update to reflect the influence of the change in the table variables, and then after than the results are immediately responsive to changes to the table variable values.

markschahl
Level V

Re: How to update column formulas

Click on the red triangle to the left of the data table name (top left of the script area, just below toolbar).

Bottom of the dropdown that will appear: Rerun Formulas

Thanks to Jerry Cooper for teaching me that one!

bio_grad
Level III

Re: How to update column formulas

Awesome! I didn't even think to check. Thanks