It should look something like this:
We would like to be able to add a default constraint to trace columns using the SQL functions SUSER_NAME() and GETDATE().
It should look something like this:
It should look something like this:
Page 1 / 1
Right now the SF forces these values to be strings. We tried some SQL injection by escaping the quotes, but unfortunately the SF outsmarted us.
This would be a pretty cool feature indeed.
Default values are not only used for database creation, but also used by the GUI or Indicium to set the default values for the new row.
That's why you can also set them for views, variants etc. Implementing this change is not as simple as changing the code generation, we'd also need to updated the GUI's and Indicium to be able to set expressions as default values.
Sidenote - Be careful using suser_name(), suser_sname(), system_user or other database functions to obtain the user name. These will return the pooling user when you use a Web, Mobile- or Universal GUI or a Windows GUI on Indicium. My advice would be to use dbo.tsf_user() instead.
Default values are not only used for database creation, but also used by the GUI or Indicium to set the default values for the new row.
That's why you can also set them for views, variants etc. Implementing this change is not as simple as changing the code generation, we'd also need to updated the GUI's and Indicium to be able to set expressions as default values.
Sidenote - Be careful using suser_name(), suser_sname(), system_user or other database functions to obtain the user name. These will return the pooling user when you use a Web, Mobile- or Universal GUI or a Windows GUI on Indicium. My advice would be to use dbo.tsf_user() instead.
Big. Fat. Yes. to this one. It's a shame you have to write defaults and such to only fill the current date when adding a row. Being able to set this as default value would be so much easier! AND a great addition to the 'Low - Code' principle.
Another sidenote:
If you want to use this filling the trace columns automatically when inserting a record in the GUI then it is not going to work since the GUI fills in null values for each field which has not been filled by the user. This means that in your example the insert_user and insert_date_time columns will have null values instead of the username and the current date. The default value will only be set when no value has been given. If you set the columns to mandatory (not null) then the GUI will show an error because it passes a null value since the (data) model does not allow nulls.
This could be fixed when the GUI also allows to pass default as a value when a column has not been filled by the user.
For example:
I created a table called test_tab which has a column insert_date_time. On the database I manually set the default value to getdate() (as in your example).
When I insert a record within my Windows GUI the debugger says it executes the following insert statement:
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh',
null
);
When I look into that table there is one record with a null value in the insert_date_time column. When the insert statement would be:
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh',
default
);
or
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh'
);
then you will get the result you are looking for.
So, if this idea/feature will be implemented then it will only be of good use when the above will also be possible/fixed.
If you want to use this filling the trace columns automatically when inserting a record in the GUI then it is not going to work since the GUI fills in null values for each field which has not been filled by the user. This means that in your example the insert_user and insert_date_time columns will have null values instead of the username and the current date. The default value will only be set when no value has been given. If you set the columns to mandatory (not null) then the GUI will show an error because it passes a null value since the (data) model does not allow nulls.
This could be fixed when the GUI also allows to pass default as a value when a column has not been filled by the user.
For example:
I created a table called test_tab which has a column insert_date_time. On the database I manually set the default value to getdate() (as in your example).
When I insert a record within my Windows GUI the debugger says it executes the following insert statement:
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh',
null
);
When I look into that table there is one record with a null value in the insert_date_time column. When the insert statement would be:
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh',
default
);
or
insert into [test_tab]
(
test_val,
insert_date_time
)
values
(
'hh'
);
then you will get the result you are looking for.
So, if this idea/feature will be implemented then it will only be of good use when the above will also be possible/fixed.
Hey Kevin,
You have to realize that database defaults don't work for update statements. It only works for insert statements where the field with the default is not in the column list.
With this knowledge, is it still something you really need?
Updated idea status Working on it! → Next release
Any chance the same trick is possible on the ‘mandatory’ column? Or a new column, ‘conditional visibility’?
Updated idea status Next release → Completed
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.