Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable

mvanderaa1 — Fri, 09 Jun 2023 16:03:02 GMT

I have a script that loads data from excel and then generates a new formula column. The column appears empty however, even though my formula preview shows values. Once I copy/paste the column into the same datatable suddenly the values appear. What's going on here?

Easiest demo is through the gif below. I also added the script and excel sheet.

Names Default To Here( 1 );

//Prompt to select and open an MS Excel file
path = Pick File( "Select Excel File", "$DOCUMENTS", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 );
fname = Word( 1, Word( -1, path, "/" ), "." );

//Let the user choose the name of the datatable
w = New Window( "Choose datatable name", // opens a window with a title and this content...
	<<Modal,
	H List Box( Text Box( "Name: " ), tbox = Text Edit Box( fname ) ), // data entry          
	// optionally, close the dialog. Or, you might want to run it again...
	H List Box(
		Button Box( "OK",
			fname = tbox << get text(); // data entry                            
			Button Box( "Cancel" );
		)
	)
);
dt = Open( path );

//Extract the die strength measurement conditions from the datatable and move to table variables
dt << Set Name( fname );
tableVariables = {"Thickness (um)", "Width (mm)", "L (mm)"};
For Each( {var, index}, tableVariables,
	dt << Set Table Variable( var, As Column( var )[1] );
	dt << delete column( Column( var ) );
);

col_name_list = dt << get column names( string );

// Check if the die strength column exists, otherwise calculate from the force and measurement conditions
If( !Contains( col_name_list, "Die strength (MPa)" ),
dt << New Column( "Die strength", Numeric,
		"Continuous",
		Formula( 3 * :Force * :"L (mm)"n / ((:"Thickness (um)"n * 0.001) ^ 2 * 2 * :"Width (mm)"n) )
		 ),
dt:"Die strength (MPa)"n << Set Name( "Die strength" );
);

//Rename the Die strength and force columns and set their respective units
dt:"Die strength"n << Set Property( "Units", "MPa" );
dt:"Force (N)"n << Set Property( "Units", "N" );
dt:"Force (N)"n << Set Name( "Force" );

Re: Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable

jthi — Thu, 24 Nov 2022 12:26:52 GMT

It seems like your force column's name is wrong in the formula until you rename it.


Column Die strength Formula Interrupted
Name Unresolved: Force at row 1 in access or evaluation of 'Force' , :Force/*###*/
Formula evaluation errors have been ignored

Re: Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable

mvanderaa1 — Thu, 24 Nov 2022 15:10:56 GMT

Edit: Ok I see now, I'm changing the variable name later in the script. So the Force column name reference is not correct at the time of creating the column.

topic Re: Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable in Discussions

Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable

Re: Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable

Re: Strange behaviour of script generated formula column; no data until I copy+paste the column in the same datatable