The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Boolean Field from NULL to F
Posted: 06 Dec 06 10:57 AM
|
fiogf49gjkf0d I thought I had seen this discussed here before but can't find it. I have a dataform with multiple checkboxes. If the user doesn't select it, I want an "F" in the field. This is an issue for one of my high priority (maintenance?) report writers who refuses to modify her report to display these NULL Records as False. Did I read that ADO won't allow you to change a boolean field from NULL to "F"?
Thanks for any hints! 
Michael |
|
|
|
Re: Boolean Field from NULL to F
Posted: 06 Dec 06 12:27 PM
|
fiogf49gjkf0d You could add a constraint on the table so the default value is 'F'. Here's something that Timmus Agersea posted in the business partner newsgroups recently:
The following TSQL will generate the alter table commands to set a default value of 'F' for the columns with a data_length = 1. I would only apply the alter table statements for tables that have "customer data". The main goal of this is to help users with query building. No longer will they need to worry about nulls.
SELECT
'ALTER TABLE ' + T.Table_Schema + '.' + T.Table_Name + ' ADD CONSTRAINT DF_'
+ T.Table_Name+ '_' + C.Column_Name + ' DEFAULT ''F'' FOR ' + C.Column_Name
FROM
Information_Schema.Columns C
INNER JOIN Information_Schema.Tables T
ON T.Table_Name = C.Table_Name
AND T.Table_Type = 'Base Table'
WHERE
C.Character_Maximum_Length = 1
AND C.Data_Type = 'char'
ORDER BY
C.Table_Name, C.Column_Name
Sample output
ALTER TABLE sysdba.ACCOUNT ADD CONSTRAINT DF_ACCOUNT_DONOTSOLICIT DEFAULT
'F' FOR DONOTSOLICIT
As always, use at your own risk and this was built and tested on SQL Server.
|
|
|
|
|
|
Re: Boolean Field from NULL to F
Posted: 06 Dec 06 12:35 PM
|
fiogf49gjkf0d SLX boolean is a char(1) with "T", "F", or NULL. A SQL boolean is either true/false I believe. So technically ADO doesn't allow you to change a boolean field but that doesn't apply since SLX booleans are just 1 character string fields.
I thought SLX defaulted checkboxes to "F" because a lot (if not all) of my custom tables have no NULL values without any special help from me. This may have been a change introduced in 6.1 or involves something special for it to work. Some system tables like Account or Contact tend to default to NULL most likely because they're created from SQL instead of using the data manager.
A sure way to fix the problem is to loop through Form.Controls setting checkbox.Text = "F" or set each one manually in the Form's OnChange event. Any checked controls will show as "T" and those left should be created as "F" by the data manager. |
|
|
|
Re: Boolean Field from NULL to F
Posted: 06 Dec 06 2:42 PM
|
fiogf49gjkf0d Unless you databind a SLX boolean column in the add form the record will be inserted with a null value. It is a sound approach to use hidden controls to ensure that all the boolean fields are set to 'F' but this requires customization and it requires customization on EVERY form that inserts data into a table with SLX Booleans.
Timmus |
|
|
|
Re: Boolean Field from NULL to F
Posted: 11 Dec 06 10:39 AM
|
fiogf49gjkf0d Ryan,
Thank you: that worked great! We had previously used Timmus' aproach and didn't want to do that on every form as he mentioned 
Michael |
|
|
|
Re: Boolean Field from NULL to F
Posted: 11 Dec 06 12:30 PM
|
fiogf49gjkf0d Actually, my approach is the one using the SQL Default Constraints. The databinding approach is a pain.
Timmus |
|
|
|
Re: Boolean Field from NULL to F
Posted: 27 Jan 09 4:06 PM
|
The script failed when I tried to run it in SLX Admin. Is it intended to be run in SQL?
If it runs in SQL, how do you get it to the remote users?
This is the statement I tried to run:
ALTER TABLE SYSDBA.LCMS_C_AM_MAIN ADD CONSTRAINT DF_LCMS_C_AM_MAIN_ALUMNI DEFAULT 'F' FOR ALUMNI
our table name is LCMS_C_AM_MAIN, the field name is ALUMNI. |
|
|
| |
| |
|
Re: Boolean Field from NULL to F
Posted: 27 Jan 09 4:59 PM
|
I thought it would, Dave, but I was wrong. I added a default value of 'F' for Account.NationalAccount and cut a remote database and the remote does not have the same constraint. I am testing with 7.5 so your mileage may vary but I suspect it will not.
Sorry for the bad news...
Timmus |
|
|
|
Re: Boolean Field from NULL to F
Posted: 29 Jan 09 5:16 PM
|
My approach would be when the form is opened in insert mode, I would set all checkboxes to Checked=False. If the user doesn't check it, it writes a F to the database, does it not? I know it does write an F if a previously checked box is unchecked.
You can then clean up existing records in a sync-friendly fashion by running this in the SQL Admin tool
update table set field = 'F' where field is null
|
|
|
|
Re: Boolean Field from NULL to F
Posted: 15 May 09 3:29 PM
|
Dan, I don't know if you ever got an answer to this. In my pre-v7.2.1 experience, I was never able to get SLX to write a "F" to a SLX boolean field as the first value. I had to write a "T" first, then update it to "F". I don't know if it still behaves this way as I got tired of beating my head against the issue.
Michael |
|
|
|