Default a date field to NULL or EMPTY...

[G Gabriel]

RE: Default a date field to NULL or EMPTY...

Martin:
Evidentily blank does not mean blank as we have come to learn from this thread. Alpha does not store values as blank or null. IOW, if you enter a new record that has a numeric field and you did not enter a vlaue in the numeric field, it will appear blank to you, but in reality alpha stores it as zero.
What is the practical value or difference of that? I asked chuck the same question and the answer he gave, which I thought was a good example, is when you do averages.
Say, you have 10 people each blowing up baloons, if 9 of them blew up baloons and the 10th guy, Joe Blow, did not blow any. If the value entered for Joe Blow is zero, it's misleading, because that means Joe Blow participated but did not blow any. While if the value is blank, that means Joe Blow was there, did not participate and for the sake of average, the number of baloons blown up should be divided by 9 not 10. It's a big difference.
Gabe

[martinwcole]

RE: Default a date field to NULL or EMPTY...

I totally see what you mean.

I have been experimenting and am convinced that the field has a flag of some kind, (that we may not have access to,) that shows whether or not we entered anything in it - otherwise, we couldn't have a "blank" value in a browse, and a 0.00 value (where you have a numeric field, 5,2 and you entered zero and it displays 0,00, but where you didn't enter any thing it shows nothing, if you see what I mean.

For example, there is a hidden field that shows whether a record is deleted or not, that we do have access to, via xbasic. But I haven't been able, via xbasic, to come up with a way to distinguish a numeric field that has not been entered in, and one that has had a zero entered.

The logical filed, however, has an easy workaround:
where a field name is "COD", I created a calculated field:

if(isblank("cod"),.25,if(cod=.t.,1.0,-1.0))

[G Gabriel]

RE: Default a date field to NULL or EMPTY...

Martin:
No need to research it much further, Lenny has already answered that in a different thread by chuck on a related topic:
Answer by Leny Forzatin
Gabe

[Tom Cone Jr]

RE: Default a date field to NULL or EMPTY...

"" For example, there is a hidden field that shows whether a record is deleted or not, that we do have access to, via xbasic. ""

Dr. Wayne calls this the zeroth field and illustrates how to change it's value in his script that undeletes records. You'll find it in the second of his 3 books. (The first one on Xbasic and Alpha Five.)

If this limitation of the DBF format is critical you could add a logical field to your table. Set default value false. Change to True whenever an actual numeric value is entered in the numeric field. In my own work this has more of a theoretical issue than a real one. My apps tend to need the average for non-zero field values. So I use a query to exclude the records that have zero in the numeric field. Doesn't matter if the zero is actual or simply a place holder. However, I can see how in other situations this might not be feasible. In those cases the work around I suggest here would do the trick.

-- tom

[martinwcole]

RE: Default a date field to NULL or EMPTY...

gabe, I still raise this question:

what characteristic has a default browse display a "blank" value when you have entered nothing, and a 0.00 when you enter a zero?

there has to be one, otherwise it would always display 0 or 0.00 - do you see what I mean?

[G Gabriel]

RE: Default a date field to NULL or EMPTY...

Martin:
""what characteristic has a default browse display a "blank" value when you have entered nothing, and a 0.00 when you enter a zero? ""
If I understand your question correctly, you are asking what properties or setting that will make a "default" browse shows blank when nothing is entered and zero when zero is entered??
If that is the question, the answer:
A "default" browse will not do that period. It will show zero if you did not enter a value. You could make it show blank by choosing format from properties where you have these options available to you, but having done that, it's no longer a "defult" browse.
Furthermore, this is only a matter of "display", both fields the blank one and the one that has a zero, have the same internal value of zero when stored by alpha.
Gabe

[Hansolo]

RE: Default a date field to NULL or EMPTY...

The blank in the default browse is only there when using default browse to delete the value. Otherwise default browse shows a 0 for a blank numeric field. This leads me to believe that the areas displaying data in the default browse are in fact not numeric but text fields and that default browse is in fact storing state information about the table elsewhere.

If you empty the table through the Utilities and use anything other than default browse or Default form to populate the table, blanks in numeric fields will be 0 again unless you delete them from within default browse.

Interesting, but I doubt the logic is exposed to us for use through xbasic.

Chuck

[Jim Chapman]

RE: Default a date field to NULL or EMPTY...

Hello Martin,

Just jumping in here with a great deal of ignorance. If I had to guess, I would say that I don't think there is any "unknown flag field". My guess would be that we are seeing the peculiarities of the control on the form bound to an uninitialized table field. Via xbasic if you reference uninitialized numerical field value, you can get some very large nonsensical values. In some of my routines I have to say:

If SomeFieldName " 0 .and. SomeFieldName " 10000000
�do something if the field has valid data
end if

My guess is were are seeing the same thing here, the control, a separate software construct, bound to an uninitialized field is showing �blank�. After initialization, it is showing an appropriate value for the field type. Character can be ��, numeric is 0 in the absence of value, after initialization.

Just my guess here.

Jim

[martinwcole]

RE: Default a date field to NULL or EMPTY...

Jim, here is an image of a test table
1 field
numeric, 5, 2

using the down arrow I created several records, entering a value in one and not the other - and in one of them I entered a zero.

this is a view of the default browse, which is not bound to any object

notice that the ones where I entered nothing, but saved the record are blank, and where I entered the zero display 0.00

so the record was created and saved - and displays nothing

[Jim Chapman]

RE: Default a date field to NULL or EMPTY...

Hello Martin,

""this is a view of the default browse, which is not bound to any object""
IMHO, it is. This is just the default browse object, which Alpha supplies. It is still a ui object with embedded controls bound to the underlying table. We are not looking at the field directly as such, we are looking at the field through the 'window' of the control object.

Jim

[martinwcole]

RE: Default a date field to NULL or EMPTY...

I see what you mean - is still baffling to me that I can enter a record without entering a value and it appears blank.

I gues a workaround would be to put in a calculated field that would evaluate to .t. onchange - but then if they went back and deleted the fields value, the same issue would result

experimented with a_field_value in field rules/field events
a_field_value is always a character value, (as I understand it)

canwrite field event
temp=""
if a_field_value " ""
temp="some value"
else
temp="no value"
end if
ui_msg_box("","canwrite " + temp)

this successfully traps a "blank" or "null" value in the field

although it is obviously a pain, one could update a logical field as true or false, and then include it as a filter in the expression that is totaling/averaging

[Jim Chapman]

RE: Default a date field to NULL or EMPTY...

Hello Martin,

Yes, one could also do a table level calculated field and/or form level variable to display actual field values and based on the events control the actual value written into the table field (ie: such as an underlying logical field -never displayed to the user- to determine whether 0 was the actual value or just the lack of value, and display the correct value in the table calculated field and/or form level variable).

I've been doing this for quite a while and I've never ran into a situation where this was an issue. Maybe I've just not done much statistical work, but it has never come up.

Thanks,
Jim

[G Gabriel]

RE: Default a date field to NULL or EMPTY...

Martin:
This is how the field is "displayed" but not how it is "stored". It is stored as a zero as per Lenny since alpha (dbf)does not store null values.
I am sure there are multitude of work-arounds but at the end of the day, the actual value stored in the field is a zero whether you touched it or not, and, if it matters to you, you have to be mindful of that and design whatever work-around or calc field to account for that.
Gabe

[Hansolo]

RE: Default a date field to NULL or EMPTY...

Agreeded :-)

Chuck

[Selwyn Rabins]

RE: Default a date field to NULL or EMPTY...

this is now fixed. build 1550-2038

you can use null_value() in an update operation against any data type now.