cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
ComplexNerd
Level III

Set Editiable rows with formula column

Hello guys,

 

 i am trying to create a datatble with user restricted  rows and columns. I have a default value column ( its default value for all column values in that row) i have coded this as formula in other columns ... but i am not able to have user input if i have formula ... i need flexibility for user to enter data 

n = 10;
rownList = {};
For(i=1, i<=n, i++,
	Insert Into(rownList, "Row " || Char(i));
);
dt =  New Table("Restricted Row Value Table",
	Add Rows(n),
	New Column( "Row Number",Character,"Nominal",Set Values(rownList),Color Cells( {77, {0}} ))
);
dt << New Column("Default Row State","Nominal",Color Cells( {-14803425, {0}} ));
For(i=1, i<=n, i++,
	dt << New Column("Column " || Char(i),"Nominal",
		Formula( If( Is Empty( :Default Row State ), Empty(), :Default Row State ) )
	);
);
dt << Set Edit Lock( "Add Rows", "Add Columns", "Delete Columns" );

how do i rewite formula such that it allows user input but takes default value if specified ... kindly guide... Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Set Editiable rows with formula column

This method for you might work.  Use a hidden column that has a formula that sets the value in a different column.  The column that has the value set remains editable, since it does not contain a column formula.

txnelson_0-1719785956521.png

New Table( "Examplel",
	Add Rows( 10 ),
	New Column( "Default Row State",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., 3, ., ., 44, ., 8, ., ., .] )
	),
	New Column( "formula column",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Is Empty( :Default Row State ) & :Default Row State != :Formula Column,
				:Output Column[Row()] = .,
				:Output Column[Row()] = :Default Row State
			);
			:Formula Column = :Default Row State;
		),
		Hide
	),
	New Column( "Output Column",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., 3, ., ., 888, ., 8, ., ., .] )
	)
)

 

Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Set Editiable rows with formula column

Change

Formula( If( Is Empty( :Default Row State ), Empty(), :Default Row State ) )

from a Formula() to Set Each Value()

Set Each Value( If( Is Empty( :Default Row State ), Empty(), :Default Row State ) )

and the column will have real values rather than a formula, thus allowing for editing

Jim
ComplexNerd
Level III

Re: Set Editiable rows with formula column

@txnelson Thanks for reply ... But set each value() is not dynamic... 

Formula( If( Is Empty( :Default Row State ), Empty(), :Default Row State ) )

when user enters data in "default row value" it applies to all others  but doesnt allow any user input to that column,

 

Set Each Value( If( Is Empty( :Default Row State ), Empty(), :Default Row State ) )

is applied only during the first execution ... 

 

is there any way formula can accomodate user input?

txnelson
Super User

Re: Set Editiable rows with formula column

This method for you might work.  Use a hidden column that has a formula that sets the value in a different column.  The column that has the value set remains editable, since it does not contain a column formula.

txnelson_0-1719785956521.png

New Table( "Examplel",
	Add Rows( 10 ),
	New Column( "Default Row State",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., 3, ., ., 44, ., 8, ., ., .] )
	),
	New Column( "formula column",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Is Empty( :Default Row State ) & :Default Row State != :Formula Column,
				:Output Column[Row()] = .,
				:Output Column[Row()] = :Default Row State
			);
			:Formula Column = :Default Row State;
		),
		Hide
	),
	New Column( "Output Column",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., 3, ., ., 888, ., 8, ., ., .] )
	)
)

 

Jim