Subelement Not Found

[forskare]

Re: Subelement Not Found

Kenn,

I wonder if the error message you were seeing happened because you might have been trying to assign a value (i.e. null_value() ) to fields defined as "calculated fields" in your table field rules? You can't do that of course. Assignments to calc fields are forbidden.

Hi Tom,
I sorta wondered the same but none of my calculated numerical fields are in the list, so that should not have been a problem. To get around the issue, I used the same fields in my grid and mad the calculated fields there. That is working A-OK. Now, I need to get the code to null the fields in all 5k records rather than just one.

[forskare]

Re: Subelement Not Found

Hi Stan,

Why is this a loop? I works fine but only on one record.

[Tom Cone Jr]

Re: Subelement Not Found

Kenn,

I've tweaked Stan's code slightly, and reformatted it for the msg board.

dim t as p
t = table.open("summarytest")
[COLOR="#FF0000"]t.fetch_first()[/COLOR]
[COLOR="#FF0000"]while .not. t.fetch_eof()[/COLOR]
    t.change_begin()
    t.contract = NULL_VALUE()
    t.applydate = NULL_VALUE()
    t.techinitials = NULL_VALUE()
    t.datepaid = NULL_VALUE()
    t.sale = NULL_VALUE()
    t.chk = NULL_VALUE()
    t.paid = NULL_VALUE()
    t.combinedrate = NULL_VALUE()
    t.ppd = NULL_VALUE()
    t.year_chg = NULL_VALUE()
    t.year = NULL_VALUE()
    t.discount = NULL_VALUE()
    t.change_end(.t.)
   [COLOR="#FF0000"] t.fetch_next()[/COLOR]
[COLOR="#FF0000"]end while[/COLOR]
t.close()

Your code runs against whichever record is currently selected in the form, when your button is pushed. By adding the "red" lines, Stan has turned your script into a loop that jumps to the first record of the table, processes it, then fetches the next, processes it, then fetches the next, and so on. The code continues to "Loop" through the While ... End WHILE code block until the WHILE "condition" is true. In contrast your code ran against a single record and then stopped.

[forskare]

Re: Subelement Not Found

Ahhh, now I understand. The way the documentation is written, there is no explanation that it is for only one record nor does it explain or show how to code for the entire table, which is probably more than likely the case.

Thank you for the explanation and thank you Stan for your valuable contribution. I and not doubt others who follow are and will be very appreciative.

[Stan Mathews]

Re: Subelement Not Found

If you want to work on more than one record at a time I believe most would just use an operation. For all records the filter is just .T.

In your case an update operation would seem appropriate.

[forskare]

Re: Subelement Not Found

I tried that but it only handles character fields using the General tab. There is no way to null date or numeric fields. At least, that I can see. I did not try using terms like > Null, etc. Not sure if the search feature recognizes it as a filter vs actual text.

[Stan Mathews]

Re: Subelement Not Found

There is no way to null date or numeric fields.

Assign a constant value to a field, expression null_value().

You might also wander past the genie and see what's there.

[DaveM]

Re: Subelement Not Found

If you try the expression builder in the operation for a date field, it will not accept null_value(), but if you just place it in the expression on the line, it will work.

xbasic results shows it fine and it will work in the final. I fought that one for 3 hours amongst others.

[forskare]

Re: Subelement Not Found

Gentlemen, the saga continues.

THE UPDATE OPERATION-

The Update operation WILL null a character field. Numeric fields must be set to a constant value of 0. The Update operation will NOT null a date field. While a constant value can be entered, it must be a valid date value.

These do not work on numeric fields:

t.Tax = NUL_VALUE() or t.Tax = 0

Documentation for NULL_VALUE() says:

Note : Numeric variables are a special case. You cannot use NULL_VALUE() with a numeric variable. However, if you have a numeric field, you can set the field value to NULL_VALUE(). Hmmm, that seems to contradict the Update operation? Maybe not. Where is the field value set? In the field rules? If so, that's not the same as it will set the field value to Null for new records only. So, what does the Documentation mean? NP though because the Update operation allows for setting a constant value of 0.

THE NULL_VALUE() CODE -

Running Stan's suggested code produces this error message:

t.change_begin()
Change already began

Thus, I am forced to close and reopen the app in order to clear all instances of "anything” which Alpha leaves open when a from or code is run. Click the button to run the script and I am greeted with this error:

t.fetch_next()
Command does not work in change or enter mode.

Now what? Can the code simply be rearranged so that t.fetch_next is BEFORE the t.change_begin() command? No, that will produce another error message: Change already began.

Obviously, there is something wrong with Alpha. It is not a user fault but a program fault. Both Stan and Tom have been around much too long to make elementary mistakes in basic code. Besides, the code was working flawlessly yesterday on the same table putting ALL listed fields at a null value, character, numerical or date, no difference, but only for one record. When the looping code is added, Alpha suddenly has the flu.

SUMMARY:

So far, there is no complete way to accomplish setting the necessary character, date or numeric fields to a null value in one setting, either a batch operation or a coded event. Granted, I am beginning to see too many trees in the forest so it's time for a short break.

TYVM, BIASW

[Tom Cone Jr]

Re: Subelement Not Found

If the Null_value() function is giving you headaches, stop using it. Change your script to Assign zero to numeric fields, and a blank space to the character fields.

[WokingJon]

Re: Subelement Not Found

If you want to delete all data for selected fields in all records of a table why not delete the fields then re-add them to the table?

e.g. table.delete_fields("<table>", "<field>") etc.
then a5_add_fields_to_table(a5.Get_Master_Path()+"\<table>.dbf","<field>,L,1")

I'm not sure what this would do with field rules, but don't think they would be affected.

[DaveM]

Re: Subelement Not Found

Being used every day

a_tbl = table.open("inspector")

	DIM a5_operation_order as C
	a5_operation_order = "recno()"
	query.order = a5_operation_order
	DIM a5_operation_filter as C
	a5_operation_filter = "Daterev <= date() .and. isnotblank(\"Daterev\")"
	query.filter = a5_operation_filter
	query.options = "I"
	query.description = "Temporary Query"
	i_indx = a_tbl.query_create("N")
	update.fields = 4
	update.field1 = "ALTERNATE"
	update.expr1 = "if(Daterev <= date()+1 .and. isnotblank(\"Daterev\"),\"\",ALTERNATE)"
	update.field2 = "DONOTUSE"
	update.expr2 = "if(Daterev <= date() .and. isnotblank(\"Daterev\"),\"\",DONOTUSE)"
	update.field3 = "REASON"
	update.expr3 = "if(Daterev <= date() .and. isnotblank(\"Daterev\"),\"\",REASON)"
	update.field4 = "DATEREV"  [COLOR="#000080"][SIZE=5][B] ' date field[/B][/SIZE][/COLOR]	
        update.expr4 = "if(Alternate=\"\",[COLOR="#800000"][SIZE=6]null_value(),[/SIZE][/COLOR]DATEREV)"
	a_tbl.update()
	a_tbl.close()

This was done in operations/update

No credit to me.

[MoGrace]

Re: Subelement Not Found

If someone else has already made this point, please ignore me.

Since the tbl.change_end() can take a flag, its a good place to trap an error that may occur before it, since without an error trap, you will never get to the change_end(.t.) as the script will just end at the error.

dim commit_flag as L =.f.

Commit_flag = .t.
On error goto ERRMSG
Tbl.change_begin()
'Change something...
tbl.change_end(commit_flag)
End
ERRMSG:
'Error trap script here
On error goto 0
Commit_flag = .f.
RESUME NEXT

[Stan Mathews]

Re: Subelement Not Found

Obviously, there is something wrong with Alpha.

Just how you're trying to use it.

I assumed you had enough background to realize you don't do mass updates via operation or script with the table being updated open. This means you don't run them from forms based on the table.

This limitation, if you will, can usually be avoided by running an operation from the control panel or from a button on a form not built on the table in question with all other forms closed. It is possible to include a script command to save the current record changes or entry, if any, before executing any code which loops through the records of the table of a form. Simple include a first line of

parentform.commit()

followed by your script. This avoids any change or enter already begun errors and any question of what mode the table is in.

Numeric fields must be set to a constant value of 0. The Update operation will NOT null a date field.

You really need to investigate creating operations with the Create button as opposed to the Create with Genie. See the attached screenshot where an update operation was used to blank a date field and a numeric field using null_value().

[forskare]

Re: Subelement Not Found

If you try the expression builder in the operation for a date field, it will not accept null_value(), but if you just place it in the expression on the line, it will work.

xbasic results shows it fine and it will work in the final. I fought that one for 3 hours amongst others.

Hi Dave,

Not sure what you mean. (in dark red)

UGH! your other post didn't show right away so now I have yours and Stan's to study. Thanks