Default a date field to NULL or EMPTY...

[davej]

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

Gabe,

If you calculate an average, the values get summed and counted then the sum is divided by the count. If you use a zero, the sum doesn't change, but the count does, therefore the average is incorrect. A null is not counted nor is it included in the sum, so the average is correct.

Dave

[Hansolo]

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

g gabriel

As I understand it, it won't matter which you use. It is not evaluating a blank in a numeric field from a table in an Alpha5 database even though the basic syntax makes is sound like it is. It is evaluating a 0 -zero- which is indistinguishable from a 0 that for instance I meant to put there or that was derived from an expression.

field1 = 0, field2 = blank (no data) = 0 in a numeric field in an Alpha5 table.

isblank(field1)= True
isblank(field2) = True
not.isblank(field1) = False
not.isblank(fiels2) = False

Something to do with the DBF backend used in Alpha5.

Chuck

[Hansolo]

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

No Null value can be stored in an Alpha5 db table numeric field. You can assign a variable a NULL value but you cannot store the Null value of the variable in a numeric field in an Alpha5 db. It will evaluate to 0 when stored.

See this post

http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=15&id=12114&loc=0&offset=0&sortby=lastreply&direction=desc&thread=12114

Given that, the point is how to distinguish that from an intended value of 0?

Chuck

[G Gabriel]

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

I understand. In this case if you want to make the field blank, you need to blank it out with a script as you would for the date field. Use the same script:
topparent:field_name.text = ""
You might want to present the user with a small dialog box onArrive or onActivate asking if the value is zero or blank. If blank, run the script.
Gabe

[G Gabriel]

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

Good example. In this case create a calc field:
If (isblank(field_name),0,1)
Instead of dbcount() use dbsum() of calc fields
Base your average on the sum of the calc fields instead of the count of the fields.
Gabe

[Hansolo]

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

I think we are talking about different things. The text or label controls used to represent fields for display in grids or dialogs are not the same as the fields representing data structures in the Alpha5 database tables. Receiving the field value from the table you can run whatever script you want to blank the resulting display in a text or label control. However, your script cannot distinguish the data it receives from field value from the table as 0 -zero- by intent or 0 -zero- by no data having been entered.

Similarly, you can trap a blank or NULL control field in a script prior to sending its contents to the data table because the control data type is always text (especially web controls). But even if you do, there is no way you are going to get Alpha5 to store the value as anything other than 0 -zero- in a field defined as numeric in the database table.

You can use a script to write a value to a hidden field in the database table that will trap whether the person left the field blank or entered a 0. You can then use this hidden field to distinguish an intended NULL value from a numeric 0 value.

Chuck

[G Gabriel]

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

You might be correct. I never tried it and as such I cannot comment on it. In this case you would have to enter the values as character then val() to numeric to do the calculations you want to do until alpha introduces null values.
Gabe

[martinwcole]

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

Chuck, the default value for any field is "nothing," unless you specifiy a default value

dates will be blank, dates will be blank, and logicals will be blank

however - if you are viewing the data on a form, where the format is set, for example, to "$000,000.00" it will appear as $0.00, but the stored value is nothing

here's an example of a filter expression, that deals with blank logicals

"" .t. 'this catches both .f. and nothing

[martinwcole]

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

forgot to add this - here is a way to blank any field:

t=table.open("vendoraudits")
t.fetch_first()
t.change_begin()
fld=t.field_get("auditdate")
fld.blank_put()
t.change_end(.t.)
t.close()
end

[Hansolo]

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

Yes, we are now talking the same problem and you are correct you would have to forgo the safety of defining a numeric field for numeric data in the table and use a text field. And you could then val() to numeric to do some math.

That presents potentially a bigger problem than the NULL issue in that you lose all the backend validation rules inherent to numeric data fields to protect against unwanted or improperly formatted data being entered. This is a major issue for most database developers. If you seriously wanted to use the data collected in any calculations you would have to define in code all these rules either at the front-end or during the calculation. Leaves a lot of room for error and won't change the fact that if something other than numeric data is entered, because after all it is a text field, you will have a lot of editing to do without in many cases knowing what should have been the value.

Chuck

[Stan Mathews]

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

Yes, but try this in the interactive, one statement at a time of course....

t=table.open("vendoraudits")
t.fetch_first()
t.change_begin()
fld=t.field_get("auditdate")
fld.blank_put()
t.change_end(.t.)

? t.auditdate
--should see = { / / }
t.close()

[martinwcole]

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

well, phooie!!
see what you mean

although it still says its blank.
if Alpha reads it as having no value, then I don't see that it makes any practical difference.

t=table.open("vendoraudits")
t.fetch_first()
t.change_begin()
fld=t.field_get("auditdate")
fld.blank_put()
t.change_end(.t.)
ui_msg_box("","" + t.auditdate + if(t.auditdate.is_blank(),"blank","notblank"))
t.close()
end

[G Gabriel]

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

Exactly, that's why you have to design some work around so as not to take into account those records that have a "blank" value. Since alpha does not store values as "blank" or "null", you have to do that ahead of time, hence I suggested the dialog box to ask the user as a work around.
Gabe

[martinwcole]

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

gabe, what's a practical example of a field with a "blank" value being a problem, since all filters, queries, expressions, etc, will see it as blank?

[Stan Mathews]

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

An on screen true false test graded as 1 point for true, -1 for false, -.25 points for not answered.

I believe something similar is/was used for the SAT/ACT.