Inserting a new record from a browse/form can initially save a specific field sometimes with null and sometimes with "empty". The field is not touched in the form. I get the idea this has to do with the last saved record beforehand. Any ideas?
I think the buffer can explain the differences i noticed. In the dct i can set an initinal value ie
0;setnonull(Fil:Fieldname) but hoped there was a more generic way to clear the buffer in all forms
My experience is that clearing the buffer, which happens before you enter a form to insert, like Richard says, will set the numeric fields the zero and the string fields to an empty string. And without any input by the user that's how it will (try to) save in the database. If that's what you want, just leave it like that. In general my main intervention there would be to use setnull on the numeric fields in the "field priming on insert". For example, if your database is set up to check a field has a value between 1 and 5, it will accept a Null, but not a Zero. Similarly, if it has to accept a number that is found in the avaiable_colours table, a null is fine, but a zero will cause your insert to fail.
I used Oracle where an empty string is, by definition, null, but that is not the case in other databases. With that sort of backend I would probably be using setnull on the string columns too.
In case you're not aware, there is a lot of updating of issues being done when reported over at https://forums.softvelocity.com/
I don't know if this issue falls into the category of "things that are getting fixed" but Bob Z is actually in the building, so it might be worth a try?
I did have a vague remembrance of thread, but I'm getting old, so it was very vague, despite only being a year and a half ago. As per Bruce's suggestion that clear should set the null indicators either one way or the other, I'd prefer them to be set to null, like they would be in a database insert referencing a subset of columns, but I suspect I would be in the minority.
E.g insert into my table (somecolumn) values (42); will insert a row with all columns except for somecolumn having null values.
For the new drivers (build 1.50 and later) I've implemented a driver switch;
/CLEARNULLS=null
/CLEARNULLS=notnull
/CLEARNULLS=ignore
The first sets all fields to NULL when CLEAR(file) is called. (I suspect you will be in the minority Jon )
The second clears all the NULL status's when doing a CLEAR. This is likely what most people expect should happen (but doesn't by default).
The third is redundant but explicitly means that no effect on the NULL status of a field is applied when CLEAR(file) is called. This is the default Clarion behavior at the moment. Should the Clarion default change, and you want to preserve the old behavior then use this.
If a field is part of a Unique key i need to set the initial value in the dct to 0;setnull(Fil:Filedname) otherwise it will crash in the form when inserting a new record with an update error
And if a field needs to be nonull (ie used in a foreign key i have 0 in the lookup table to show it in a sort header enabled browse as empty and will not be filtered out) i place 0;setnonull(Fil:Filedname)