topic Re: Why is there such unexpected re-calculation of columns without formulas? in Discussions

Why is there such unexpected re-calculation of columns without formulas?

dyakunin1 — Fri, 10 Oct 2025 02:43:35 GMT

I often use JMP to prototype data pipelines, calculating column values in one table, then querying data into another table based on calculation results. I noticed a behaviour that looks to me very strange and counterintuitive. Say, I have a table with three columns:

The second and the third columns are calculated as Lag ( :Column 1 ) and Col Sum ( :Column 1 ).

Now I query the rows into a new table where :Column 1 > 2. The result is:

":Lag of Column 1" and ":Column 1 Sum" get re-calculated, since the table inherited the formulas. This is understandable.

But what if I delete the formulas and update the table? Then, since I do not have formulas and I do not re-calculate values in the destination table, I will see the data as it is in the the source table, right? Guess what, wrong! Nothing changes. I still see null, 3 and 7, 7 not 2, 3 and 10, 10.

Is this a defect? Or is this as expected? How can this be explained?

Thanks!

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Fri, 10 Oct 2025 04:33:55 GMT

Ouch, a surprisingly toxic mixture: Query Builder and Column formulas!

Some settings like in Subset

would remove lot of headache.

Besides that: a nice magnifying glass for the nuts and bolts of JMP : )
Which version of JMP? Can you share the steps from the log?

I tested it with JMP 18.2.2 and 19.0, where Lag() gets executed after executing the query, Col Sum() doesn't.

After removing the formulas, everything is fine.
[maybe: did you reference an old data table "untitled" - with formulas?]

names default to here(1);

dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "Column 1",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4] )
	),
	New Column( "Sum[Column 1]",
		Formula( Col Sum( :Column 1 ) )
	),
	New Column( "Lag[Column 1]",
		Formula( Lag( :Column 1, 1) )
	)
);

dt << run formulas();
wait(0);

queryDT= Expr(New SQL Query(
	Connection( "JMP" ),
	Select(
		Column( "Column 1", "t1" ),
		Column( "Sum[Column 1]", "t1" ),
		Column( "Lag[Column 1]", "t1" )
	),
	From( Table( "Untitled", Alias( "t1" ) ) ),
	Where(
		GT(
			Column( "Column 1", "t1" ),
			2,
			UI( Comparison( Base( "Continuous" ) ) )
		)
	)
) << Run Foreground);

queryDT;

Column (dt, 2) << delete formula();
Column (dt, 3) << delete formula();

wait(0);
queryDT;
wait(0);
dt << set name("input");

Re: Why is there such unexpected re-calculation of columns without formulas?

jthi — Fri, 10 Oct 2025 04:21:28 GMT

Are you making the query to JMP data table? Are you removing formulas from which table? Can you share example datasets to replicate the issue?

Re: Why is there such unexpected re-calculation of columns without formulas?

dyakunin1 — Fri, 10 Oct 2025 04:56:02 GMT

Yes, the second table in the second screenshot is a result of the querying first JMP data table in the first screenshot. Tables are attached - place them in the same folder and execute "Update From Database" script in the second one. The second table does not have formulas; yet, the results are as if the formulas existed and got evaluated.

Re: Why is there such unexpected re-calculation of columns without formulas?

dyakunin1 — Fri, 10 Oct 2025 04:56:58 GMT

JMP Pro V.17

Tables are attached.

Re: Why is there such unexpected re-calculation of columns without formulas?

jthi — Fri, 10 Oct 2025 05:34:24 GMT

Not a nice workaround, but you can suppress formula eval temporarily from the original table, run the query and re-enable formula evaluation (this should in my opinion already happen by Query Builder).

Also, I would contact JMP support about this as there is some weirdness going on, JMP Support

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Fri, 10 Oct 2025 07:29:24 GMT

suggestion for JMP 19.1:

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Fri, 10 Oct 2025 15:44:02 GMT

Ah, I misinterpreted the

But what if I delete the formulas and update the table?

OK, still the same in JMP19.

So, JMP runs the query (including the formulas) - and fills the columns in the target table with the new results?

This is actually what I expected with 'Update'. But why can't JMP do such a 'keep the table, just update the data' update if a new column has been added in the meantime? Concerning Column4, it looks more like it's deleting everything that was there and replacing it with the new query results.

Maybe: it takes the query result - but copies and applies the old column settings?
... thereby removing the column formula - and hiding the issue?

(view in My Videos)

Re: Why is there such unexpected re-calculation of columns without formulas?

dyakunin1 — Fri, 10 Oct 2025 15:17:23 GMT

Yes, this workaround works. Another clumsy workaround I use is to query all rows into an intermediate table without formulas and then query this intermediate table.

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Fri, 10 Oct 2025 15:43:20 GMT

What was the response from JMP Support?

Re: Why is there such unexpected re-calculation of columns without formulas?

dyakunin1 — Fri, 10 Oct 2025 15:46:40 GMT

I did not contact them. I'd rather count on this community and myself :)

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Fri, 10 Oct 2025 16:10:43 GMT

Hm, I prefer an official fix vs. "workarounds".

get it fixed - and forget about it.

find a workaround and don't forget!!
- and don't forget to tell every colleague!

Re: Why is there such unexpected re-calculation of columns without formulas?

hogi — Sat, 11 Oct 2025 07:00:40 GMT

Concerning "community and myself" - I added the topic to

On the other hand, I am quite confident that this issue can be resolved in the GUI.
Options similar to those in Subset could help the customer to understand the issue, and the default option should be to remove or disable the formula column.

Let's see if this behavior is considered a bug: TS-00244891.

If not, it will take longer, as we first need to collect enough Kudos on the wish list: