PDA

View Full Version : Subelement Not Found


ABC123

forskare
01-24-2015, 06:57 PM
I put the following into a script. All I changed is the table name and the field name. Thus "orders" was changed to "summarytest" and t.date was changed to t.contract.

dim t as P
t = table.open("orders") Now t = table.open("summarytest")
t.change_begin()
t.date = NULL_VALUE() Now t.contract = NULVALUE_VALUE()
t.change_end(.T.)
t.close()

When I run the script, I get the following error message:

t.change_begin()
Change already began

What is happening to cause this error message? What change began before the script got to the t.change_begin line?

CraigSchumacker
01-24-2015, 07:06 PM
I am assuming this is occuring after closing A5 completely? Then re-opening it?

I am guessing if you restart A5, it doesn'y happen on the first run, but it does on the second run?

If so, you are 'Stuck' in change mode, because something is (or has) interupted the script before the change_end(.t.) executed on the first run.

Hope that helps...

DaveM
01-24-2015, 07:08 PM
Means that t. was already in change mode



vmode = t.mode_get()
select
case vmode = 0
t.change_begin()
case vmode = 2
t.enter_end()
t.change_begin()
end select



May help in place of t.change_begin()

CraigSchumacker
01-24-2015, 07:15 PM
I think Dave's code is missing:

vmode = t.mode_get()
select
case vmode = 0
t.change_begin()

case vmode = 1
t.change_end()
t.change_begin()

case vmode = 2
t.enter_end()
t.change_begin()
end select

DaveM
01-24-2015, 07:20 PM
Craig. I use this so much, I have it saved and just plug it in as needed which is most of the time. LOL
Never failed yet.

CraigSchumacker
01-24-2015, 07:27 PM
So, what does your script do when mode_get() returns a '1' showing the table is in change mode.

You know, 0 = View, 1 = change mode, and 2 = enter mode...

Just trying to learn here, since he is stuck in change mode.

DaveM
01-24-2015, 07:34 PM
Craig,

I have no idea, it has never done that.

forskare
01-24-2015, 07:38 PM
No, I did no0t close A5. but I did run this in the interactive window:

dim t as p
? t.change_begin()
ERROR: Property not found
t.change_begin method not found.


? t.contract
ERROR: Property not found
t.contract subelement not found.

When I read your responses, I closed and reopened the app. Upon clicking the button with the code, I got the error, subelement not found.

This is what I'm trying to do. Once a year, data (partial records) will need to be removed from several, but not all, fields in the table. My plan is to first archive the entire table and then run this script to remove the data. What I don't know, just yet, will it remove the data form those fields in every record. This is the script:


dim t as p
t = table.open("summarytest")
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.)
t.close()

CraigSchumacker
01-24-2015, 07:43 PM
Does a field called 'Contract' exist in the 'Summarytest' table?

You wrote:

"Now t.contract = NULVALUE_VALUE()"

Should that be:

t.contract = NULLVALUE_VALUE()

CraigSchumacker
01-24-2015, 07:47 PM
Wait, what is NULLVALUE_VALUE()? Isn't thatSQL?

Do you mean Alpha's NULL_VALUE()???

I see that was in the first message in the thread, and you either caught or never had the issue here. Please ignore this message...

forskare
01-24-2015, 07:52 PM
Yes. that is a typo

forskare
01-24-2015, 07:54 PM
Wait, what is NULLVALUE_VALUE()? Isn't thatSQL?

Do you mean Alpha's NULL_VALUE()???

I see that was in the first message in the thread, and you either caught or never had the issue here. Please ignore this message...

Yes, I took the code from Alpha's documentation which said nothing about SQL;

NULL_VALUE()

Syntax

NULL_VALUE()

Description

NULL_VALUE() is used in an Update Operation or an Xbasic script to assign NULL to a field or variable.

Supported By

Alpha Five Version 5 and Above

Examples

dim t as P
t = table.open("orders")
t.change_begin()
t.date = NULL_VALUE()
t.change_end(.T.)
t.close()

CraigSchumacker
01-24-2015, 07:58 PM
Yes, that was from the first script you posted.

Is 'Contract' a field in 'Summarytest.dbf'?

If so, look at the field rules for contract. Is it a calculated field, or is there anything there that may inhibit change, like required field, or something?

forskare
01-24-2015, 08:03 PM
Contract is a 1 character field for recording if the customer has a contract. There are no field rules other than to capitalize the letter, from y to Y. That's it. It is not associated with any other field in the table. Comment out the contract line and the error message moves to the next line. Must be something that is not recognized. These are the fields in the table to be purged:

Sale Numeric 10 2
Combinedrate Numeric 8 5
Discount Numeric 10 2
Applydate Date 8 0
Tech Character 18 0
Techlicno Character 8 0
Techinitials Character 4 0
Datepaid Date 8 0
Chk Character 6 0
Paid Numeric 10 2
Paidint Numeric 5 2
Year Date 8 0
Exempt Character 1 0
Contract Character 1 0
Route Character 3 0
Units Numeric 10 0
Ppd Character 1 0
Year_Chg Character 1 0

As I look at this, some are numeric but that shouldn't throw this error, maybe another but not this one.

CraigSchumacker
01-24-2015, 08:05 PM
Try:

t.contract = ""

in your script. Does the same error pop up?

forskare
01-24-2015, 08:10 PM
Try:

t.contract = ""

in your script. Does the same error pop up?

Yes, in the script design window and the interactive window.

That doesn't work either, same error message.

CraigSchumacker
01-24-2015, 08:13 PM
I just reread your post.

Please do not use the interactive window.

? t.change_begin()

would have to be:

t.change_begin()

You need to remove question marks and highlist the whole script to run in the Interactive window.

Have you made a backup? This should only hit the first record, but I don't know if you have this looped.

CraigSchumacker
01-24-2015, 08:15 PM
I think you will need to give us (well me) a copy of the table.

forskare
01-24-2015, 08:19 PM
I copied the entire script to the interactive window and pressed the Enter button after each line. The are the first few:

dim t as p

t = table.open("summarytest")

t.change_begin()

t.contract = ""
ERROR: Property not found
t.contract subelement not found.

t.applydate = NULL_VALUE()
ERROR: Property not found
t.applydate subelement not found.


I attac a file in a few.

CraigSchumacker
01-24-2015, 08:27 PM
Allready made the table. testing now.

Stan Mathews
01-24-2015, 08:28 PM
t = table.open("summarytest")
? t.field_name_get()

Will dump the summarytest field names to the interactive editor.

CraigSchumacker
01-24-2015, 08:32 PM
Your table and script work perfectly, here.

Try your script with the attached table.

Create a new folder, a new database in that folder, and add this table.

Then copy your script and try it.

forskare
01-24-2015, 08:44 PM
Attached is a zip file of the table with 35 records. You may have to copy the script from above.

Thanks much.

CraigSchumacker
01-24-2015, 08:49 PM
Your script works perfectly on your posted table.

I did change the name of the table from:

'Null Test'

to:

'NullTest'

as I didn't like the space in there

CraigSchumacker
01-24-2015, 08:53 PM
Kenn:

Try closing Alpha again.

I get the feeling you might be tainting your results by using the Interactive Window.

Try just the script window.

Craig

forskare
01-24-2015, 09:03 PM
I downloaded and your script works fine, even in my database. Perhaps the table has an issue so I'll work with it a bit and report back. Thank you for your assistance.

CraigSchumacker
01-24-2015, 09:04 PM
Check the field rules and table events in the field rules.

Good luck.

forskare
01-24-2015, 09:33 PM
I removed the field rules one at a time. The error kept popping up until they were all deleted until except for the autoinc field. Did a db compact and the ran the script and it worked. Well sorta. I only worked on the first record. Does the script need to be changed to include all fields or is it supposed to null the fields in all records?

Thanks again,

Stan Mathews
01-24-2015, 10:41 PM
Called a loop, Ken.

dim t as p
t = table.open("summarytest")
t.fetch_first()
while .not. t.fetch_eof()
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.)
t.fetch_next()
end while
t.close()

Tom Cone Jr
01-24-2015, 10:55 PM
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.

forskare
01-25-2015, 12:21 AM
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
01-25-2015, 12:23 AM
Hi Stan,

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

Tom Cone Jr
01-25-2015, 06:58 AM
Kenn,

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


dim t as p
t = table.open("summarytest")
t.fetch_first()
while .not. t.fetch_eof()
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.)
t.fetch_next()
end while
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
01-25-2015, 12:24 PM
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
01-25-2015, 12:36 PM
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
01-25-2015, 12:59 PM
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
01-25-2015, 02:20 PM
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
01-25-2015, 04:02 PM
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
01-25-2015, 04:53 PM
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
01-25-2015, 05:08 PM
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
01-25-2015, 05:26 PM
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
01-25-2015, 05:29 PM
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" ' date field
update.expr4 = "if(Alternate=\"\",null_value(),DATEREV)"
a_tbl.update()
a_tbl.close()


This was done in operations/update

No credit to me.

MoGrace
01-25-2015, 05:44 PM
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
01-25-2015, 07:10 PM
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().

39611

forskare
01-25-2015, 10:58 PM
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

DaveM
01-25-2015, 11:09 PM
the date value could have been
update.expr4 = "null_value(),DATEREV)"
But I had a filter attached

Stans images show the same way I did it in operations

forskare
01-26-2015, 02:14 AM
Hello Stan,

Your assumption is incorrect. The button is on a Menu form based on a dummy Table. The app is set t open to the Menu form. Thus there should not be anything open which contains data. When the button is pressed, it throws the error. Right now, I am on my laptop and using a backup of the app. The app opens, I click the button and this error pops up:

Script:Datapurge1 line:11
t.change_Begin()
Change already began

Well, isn't that interesting, especially since the first line of code in the script is parentform.commit(). Also, line 11 is a commented out line of code referring to a numerical field. That begs the question, What, exactly, does the error message mean and to what is it referring? Does it mean the error is on line 11? Does Alpha count commented out lines as well as empty or blank lines when it throws an error code? After some experimenting, I know that Alpha counts empty lines and commented out lines.

I edited my script so only character fields were evaluated. I closed the app and reopened it. Clicked the button and nothing happened except in the lower left corner of the screen appeared the words, Record 1. I clicked on the Control Panel tab and Alpha locked up. After several attempts of this procedure, each time Alpha locking up, I commented out the 4 lines of code referring to the loop, saved the script, closed and reopened the app. Clicking the button resulted in no error codes and in the lower left corner, the words Record 1 made a very brief appearance. I clicked the CP tab and no lockup. The CP opened.

OK, I opened the script and uncommented the 4 lines of loop code. After saving an exiting the app and reopening it, I clicked the button and the same thing happened, Record 1 appeared and did not disappear. (That's probably when Alpha locked up) I clicked the CP tab and had to close the program as it was frozen solid.

OK, so what's with the looping code? Why is it not throwing an error message? I have no clue but I'll keep working with it.

forskare
01-26-2015, 02:37 AM
I figured out that the Change already began error message occurs no matter what was opened. Experimenting, I copied Stan's code and pasted it into my script, replacing all except the commit line. When I clicked the button, it threw an error because there is a line of code referring to a field that is not in this version of the app. I removed the line from the script and saved it. Clicked the button and the Change already began error appeared. The ONLY form and table that had been opened was the Menu form/Dummy table. The script editor was closed as well.

I ended up commenting out the 2 fields in the script which are not in this version of the app as well as one of 3 date fields along with the 4 lines of looping code. As long as those 4 lines are not in the script, it runs, even with the numerical and other 2 date fields intact. It ignores the numeric and date fields but it runs, nulling the character fields as directed.

SUMMARY

Alpha does not like the lines of looping code and will lock up if included. Why, I have no clue. However, I will try created an update operation without the genie and see what happens.

forskare
01-26-2015, 03:22 AM
I was able to create the update as Stan suggested. I works beautifully from the operation tab. However, action scripting to run the operation from a button does not work. I chose Operation, Run a saved operation. Next, I chose the Summarytest table and that's where the problem lies. It will not allow any table to be accepted because the window stays open showing all the available tables. The script can be completed but the table selection window stays open and the default prompt appears to create an operation.

OK, enough, it's 2:21 and time for some zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

See attached