Script Corrupts Product Table

[[email protected]]

Not sure why, but when I run this script, it totally corrupts my product file. It is identical to another script I run and yet, it does not corrupt. I know that the table is closed from a previous script, but jus do not see why this one keeps corrupting this file.....

Here's the script:

'Date Created: 13-Mar-2009 02:26:53 AM
'Last Updated: 26-Jun-2009 08:35:25 PM
'Created By : Ryland
'Updated By : Ryland
DIM global_var_A as c
DIM GLOBAL user as C
DIM GLOBAL pword as C
DIM GLOBAL level as C
DIM GLOBAL varC_result as C
dim global try as n
dim global way as c
dim ans as n
dim time_in as c
dim tbl as p
dim tbl_1 as p
dim qry as p
dim qry_1 as p
dim recs as n
dim recs_1 as n
dim global user_level as c
DIM global v_personnel as c
dim XDialogStyle as p

tbl_1=table.open("Product")
query.filter="Next_calibration_date <= date() + 15"
query.order=""
query.options="T"
qry_1=tbl_1.query_create()
recs_1=qry_1.records_get()

if recs_1 > 0 then
'msgbox(tbl_1.product_id)
BUTTON18.fill.style = "solid"
BUTTON18.fill.color = "Red"
end if


if recs_1 = 0 then

BUTTON18.fill.style = "solid"
BUTTON18.fill.color = "Green"
end if
tbl_1.close()
end

Thanks for the eyes, Ryland

[Stan Mathews]

Re: Script Corrupts Product Table

You don't have

tbl_1.close()

within

if recs_1 > 0 then 
'msgbox(tbl_1.product_id)
BUTTON18.fill.style = "solid"
BUTTON18.fill.color = "Red" 
end if

but that probably isn't the issue.

[[email protected]]

Re: Script Corrupts Product Table

Let me try that. I was hoping my end script at the bottom would take care of it, but maybe it's leaving the table open.

Will try now.

Thanks Stan,

Ryland

[[email protected]]

Re: Script Corrupts Product Table

Stan,

As usual you are correct. I placed the close tables above, but I soon as I touch that button (with the tbl.close moved into each conditional). it corrupts the product table immediately.....really werid.

I'll keep digging, but it does not appear to be in script....

Ryland

[[email protected]]

Re: Script Corrupts Product Table

Yeah, the product file is toast. Packing and rebuliding the indexes, even though appeared to fix it would not export, since it stops on record 100 ot so.

I'm going to purge the file and rebuild... Ryland

[[email protected]]

Re: Script Corrupts Product Table

Nah, no luck.Thought the table was corrupt, but for some reason when I run the above routine, it just kills that table. Does not make sense unless I have a bad key structure...Going to completely delete the table and redefine the 100 fields, and try again.......

Nope, table is correct. It is almost like the file pointer is getting lost. If I exit out of Alpha and come back in then all of the Product records appear fine. So it must have something with a "Pointer" getting lost. I've repeated this several times, so definitely there's something flaky going on.

Ryland

[Tom Cone Jr]

Re: Script Corrupts Product Table

Ryland,

I have several ideas for you.

1. The most important is that you don't have to run a query to determine if a single record exists in your table with a "next_calibration_date" field value meeting your criteria. The approach you are taking will result in a count of all matching records. You don't need to know the count, you just need to know if there's one, right? Define an ascending index on the "next_calibration_date" field. Then in your script open an instance of the table, set the index primary, fetch the first record and check the field value. Make sense?

2. If you want to run a query against a date field your query filter syntax needs to be tested and corrected. Here are some examples I've prepared for you using the Invoice_header table from AlphaSports. Notice how the syntax changes as you move from a literal date, to a variable containing a date.

t = table.open("invoice_header")
query.filter = "date < ( [COLOR="Red"]{[/COLOR]01/11/2007[COLOR="red"]}[/COLOR] + 5 )"  [COLOR="darkorange"]'literal date value. Do you see the braces?[/COLOR]
qry = t.query_create()
?qry.records_get()
= 11
t.close()

dim mydate as D = {01/11/2007}
t = table.open("invoice_header")
query.filter = "date < ( {" + dtoc(mydate) + "} + 5 )" [COLOR="darkorange"] 'old fashioned syntax
                                     'Notice the quote marks and the string concatenation[/COLOR]
qry = t.query_create()
?qry.records_get()
= 11
t.close()


dim mydate as D = {01/11/2007}
t = table.open("invoice_header")
query.filter = "date < " + s_quote(mydate + 5) [COLOR="DarkOrange"]'same thing, using newer s_quote() fn[/COLOR]
qry = t.query_create()
?qry.records_get()
= 11
t.close()

[[email protected]]

Re: Script Corrupts Product Table

Thanks Tom,

I will give this a try. Certainly makes sense the way you explained it, and will let ya'll know. Ryland

[[email protected]]

Re: Script Corrupts Product Table

Hi Tom,

Also, I think I see the problem when I run the Query. When I execute the Query, I am setting on a Dumy Form. What I noticed is that I saw Alpha change to openning the product table, but while I am still setting on the Dummy form (which is a menu). Then when I click on the "Products" buttom to bring the form up, the records display, etc on that Form is all screwed up.

So it looks like the culprit is that I'm assuming that you cannot run a standalone Query on a dummy form without messing up the primary form itself. One would think that by closing the table that would take care of it, but I guess not....so I'm stumped now on how to handle since I need my menu's on the Dummy Menu to be touch sensitive (no mouse) for selections.

[[email protected]]

Re: Script Corrupts Product Table

Tom, and Gang

I found the problem, just do not know how to fix it. What is happening on the Menu as referenced above, is that after I find that first record, if it is available, I automatically run a Query on Product. That is what is locking up my Product screen when I depress the Menu button for it. The Product form is set to Query Mode...duhh for me...That is why when I leave Alpha and come back in, it is reset.

I'm trying to figure out the Query.Drop, in order to place it into the script to release the form but just cannot get the syntax right.

Well at least I know now to pay attention to the right hand bottm of the screen and look at the status. Just need now to know how to get out of it..

Hope this helps someone as well. as by adding qry.drop() did the trick in the script

Thanks,

Ryland

[[email protected]]

Re: Script Corrupts Product Table

Final Note:

As Tom stated earlier the Fetch first is more efficient, and do not have to worry about dropping the qry.drop() in the script:

'Date Created: 13-Mar-2009 02:26:53 AM
'Last Updated: 27-Jun-2009 07:36:49 PM
'Created By : Ryland
'Updated By : Ryland
DIM global_var_A as c
DIM GLOBAL user as C
DIM GLOBAL pword as C
DIM GLOBAL level as C
DIM GLOBAL varC_result as C
dim tbl as P
dim indx as P
dim count as N
'__________________________________________________

'a5.command("VIEW_TRACE")
tbl = table.open("Product")
query.filter = "Next_calibration_date <= date() + 15"
indx = tbl.query_create()
count = indx.records_get()
tbl.fetch_first()
'____________________________________________________

'while .NOT. tbl.fetch_eof()
' trace.writeln(tbl.Next_calibration_date)
' tbl.fetch_next()
'end while
'____________________________________________________

if count > 0 then
'msgbox(tbl_1.product_id)
BUTTON18.fill.style = "solid"
BUTTON18.fill.color = "Red"
end if


if count = 0 then
BUTTON18.fill.style = "solid"
BUTTON18.fill.color = "Green"
end if
indx.close()
tbl.close()
ui_msg_box("Record Count", "" + count)
end