index_create

[Schulzie]

Hi Phorum,

I guess you folks are tired of seeing this one and I'm
tired of trying to make it work, but here goes. The early part of this script creates a table whose name is 1 year less than the current year and adds it to the DB. All is fine there, now I need an index of lastname+firs_nam+midinit. Nothing is really simple here
this will have to work once a year every year hereafter, hence; the table to be opened for creating the index must
be a variable, like new_name or similar. The Professional Edition Reference book that I have is not very clear on
this.

What I need to know is what changes do I need in the commented out lines of the following script:

a_tbl = table.open("1999_1.dbf")
new_name = alltrim(str(year(date()-365)))
query.order = "lastname+firs_nam+midinit"
query.filter = "substr(datepaid,9,2)=substr(new_name,3,2)"
a_tbl.query_create()
copy.set = ""
copy.db = "c:program filesa5v4union"+chr(92)+new_name+".dbf"
copy.dd = .f.
copy.delete_o_dd = .f.
copy.records = .t.
copy.fields = 10
copy.field1 = "lastname"
copy.field2 = "datepaid"
copy.field3 = "duespaid"
copy.field4 = "assesspaid"
copy.field5 = "percent"
copy.field6 = "trade"
copy.field7 = "dues"
copy.field8 = "firs_nam" '20
copy.field9 = "midinit"
copy.field10 = "dept_num"
a_tbl.copy()
file_add_to_db("c:program filesa5v4union"+chr(92)+new_name+".dbf")
a_tbl.close()
'a_tbl.open("new_name.dbf")
'table.index_create_begin("lastname", "lastname")
'index_add("firs_nam", "firs_nam")
'index_add("midinit", "midinit")
'index1 = tbl.index_create_end()
'a_tbl.close()
a_tbl.open("1999_1")
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")

a_tbl.close()

Where I open the table (presumingly 1999.dbf in this case)
I have tried "1999.dbf", "new_name", and "alltrim(str(year(date()-365)))"

It always says it can't find the table.
Thank you all for reading,
ED

[Stan Mathews]

RE: index_create

Ed,

I'm looking into how to do this. At first glance I would say it is "alltrim(new_name)+'.dbf'".

I think your efforts are directed at automating your whole process.

If I am right you are headed for more problems when you get to trying to create named browses through xbasic, and named operations. You have a button that calls a named operation :Mark_run(del_lst_yr) that you haven't created yet.


Are you trying to automate this to turn over to someone else?

Stan

[Stan Mathews]

RE: index_create

Ed,

I think you're doomed here. Remember that Alpha doesn't like table names that begin with numbers. The following is copied directly from the interactive tab:

new_name = "1999"
a_tbl=table.open(alltrim(new_name)+".dbf")
note the difference between the above line and your "a_tbl.open........
x = a_tbl.name_get()
? x
= "_999"

If this holds true in the rest of my investigations, you cn never get to a numbered

[Stan Mathews]

RE: index_create

Ed,

Here goes. This seems to run ok.

dim new_name as c
' debug(1)
on error goto error_handler
a_tbl = table.open("1999_1.dbf")
new_name = alltrim(str(year(date()-365)))
query.order = "lastname+firs_nam+midinit"
query.filter = "substr(datepaid,9,2)=substr(new_name,3,2)"
a_tbl.query_create()
copy.set = ""
copy.db="d:program filesa5v4union"+chr(92)+new_name+".dbf"
when you past the above two lines into your script, make sure they paste as one line
copy.dd = .f.
copy.delete_o_dd = .f.
copy.records = .t.
copy.fields = 10
copy.field1 = "lastname"
copy.field2 = "datepaid"
copy.field3 = "duespaid"
copy.field4 = "assesspaid"
copy.field5 = "percent"
copy.field6 = "trade"
copy.field7 = "dues"
copy.field8 = "firs_nam" '20
copy.field9 = "midinit"
copy.field10 = "dept_num"
a_tbl.copy()
file_add_to_db("d:program filesa5v4union"+chr(92)+new_name+".dbf")
query.order = "lastname+firs_nam+midinit"
a_tbl.close()
a_tbl = table.open("1999_1")
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")
a_tbl.close()
a_tbl=table.open(alltrim(new_name)+".dbf")
table.index_create_begin("lastname", "lastname")
index_add("firs_nam", "firs_nam")
index_add("midinit", "midinit")
index1 = a_tbl.index_create_end()
a_tbl.close()
a_tbl=table.open("1999_1")
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")
a_tbl.close()

end
error_handler:
msg= error_text_get(error_code_get())+chr(10)+chr(13)+"Error occured at line"+str(error_line_number_get(),4,0)
when you past the above two lines into your script, make sure they paste as one line
ui_msg_box("Error",msg)
end

[Stan Mathews]

RE: index_create

Ed,

Here goes. This seems to run ok.

dim new_name as c
' debug(1)
on error goto error_handler
a_tbl = table.open("1999_1.dbf")
new_name = alltrim(str(year(date()-365)))
query.order = "lastname+firs_nam+midinit"
query.filter = "substr(datepaid,9,2)=substr(new_name,3,2)"
a_tbl.query_create()
copy.set = ""
copy.db="d:program filesa5v4union"+chr(92)+new_name+".dbf"
copy.dd = .f.
copy.delete_o_dd = .f.
copy.records = .t.
copy.fields = 10
copy.field1 = "lastname"
copy.field2 = "datepaid"
copy.field3 = "duespaid"
copy.field4 = "assesspaid"
copy.field5 = "percent"
copy.field6 = "trade"
copy.field7 = "dues"
copy.field8 = "firs_nam" '20
copy.field9 = "midinit"
copy.field10 = "dept_num"
a_tbl.copy()
file_add_to_db("d:program filesa5v4union"+chr(92)+new_name+".dbf")
query.order = "lastname+firs_nam+midinit"
a_tbl.close()
a_tbl = table.open("1999_1")
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")
a_tbl.close()
a_tbl=table.open(alltrim(new_name)+".dbf")
table.index_create_begin("lastname", "lastname")
index_add("firs_nam", "firs_nam")
index_add("midinit", "midinit")
index1 = a_tbl.index_create_end()
a_tbl.close()
a_tbl=table.open("1999_1")g
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")
a_tbl.close()

end
error_handler:
msg= error_text_get(error_code_get())+chr(10)+chr(13)+"Error occured at line"+str(error_line_number_get(),4,0)
when you past the above two lines into your script, make sure they paste as one line
ui_msg_box("Error",msg)
end

[Stan Mathews]

RE: index_create

Hope this isn't too jumbled. The "send message" window is too small to get a feel for how the reply will look, sometimes. I tried to add the color for emphasis. Ignore the "g" after ("1999_1"). I'm also a lousy typist.

[Schulzie]

RE: index_create

Hi Stan,

I feel as though I am monopolizing your time. I did take note somewhere that if the name of a table begins with a number that it doesn't make A5 very happy. Although I found out in many cases that it does accept it, so I have proceded on that premise. There was a time that I used y1999 in lieu of 1999 but it didn't seem to make any difference. Looking at the present task, maybe I should go back to adding the Y in front of the year if only to keep A5 from throwing a fit.

Looking at page 165 of Professional Edition Reference, the example at the bottom of the page, I am confused about "Tag Name" and "Index Expr". I was assuming that "tag name" referred to the index name, and that index referred to the field to be keyed on.

You are right, this must be entirely automated to run based on the system clock without the users interactive input. I may go one step further and have it run on March 1 each year because the user likes it to show about 2 months from the year preceding the year of note.

After I finish this, I'll go try your approach, it looks viable to me, and who am I to think it won't perform admirably.

Tunzakudosifitworks,
ED

[Stan Mathews]

RE: index_create

Ed,

I strongly suggest prepending some letter or letters to the table names, whether it be y or yr or whatever. I would hate for you to develop something this complex only to find a point at which number named files can't be used, and then have to adjust your naming scheme backwards to the first.

Don't worry about my time. I'm doing this because I enjoy and learn from it.

Stan

[Schulzie]

RE: index_create

Hi Stan,

Many, many thanks. I believe prepending the history dates
with a letter is the only way to go. In the meantime, I put your modification to the script in and did not get any errors, however; it did not create the index. Back to plugging.

Ifyourlearningfromitthinkhowmuchyouhavetaughtme,
ED

[Schulzie]

RE: index_create

Hi Stan, (me again)

It looks like it is working now, however; it makes 3 separate indeces,lastname,firs_nam, and midinit. I'll give it a shot now to chaning the indec_create line to read
("lastname","firs_nam","midinit", "lastname","firs_nam","midinit")

Kudos galore,
ED

[Schulzie]

RE: index_create

Hi Stan,

I am living proof that a blind squirrel can create an
index. I used the line:
("lastname+firs_nam+midinit","lastname+firs_nam+midinit")
and it worked just fine.

I guess I'll go onto working to name the saved history files with a "Y" as the first character.

Thank you ever so much,
Ed

[Stan Mathews]

RE: index_create

It's blind hog, as in

"even a blind hog will find an acorn every once in a while".

Congrats!!

[Schulzie]

RE: index_create

Hi Stan,

Thank you. AHA "utopia" welllllll A L M O S T! The following script does the job and names the table with a leading Y ie; Y1999 as opposed to 1999, and adds the index
lastname+firs_nam+midinit. One last problem (always has to
be one) it gives an error message that it can't find the index "lastname+firs_nam+midinit" although it actually does find it and makes it the primary index, as tested. I have a feeling that the index put function comes so fast that it hasn't really completed it's job before the index put functon executes. Do you suppose that a small delay after
index_create_end and before index_put would be proper?

a_tbl = table.open("1999_1.dbf")
new_name = "y"+alltrim(str(year(date()-365)))
query.order = "lastname+firs_nam+midinit"
query.filter = "substr(datepaid,9,2)=substr(new_name,3,2)"
a_tbl.query_create()
copy.set = ""
copy.db = "c:program filesa5v4union"+chr(92)+new_name+".dbf"
copy.dd = .f.
copy.delete_o_dd = .f.
copy.records = .t.
copy.fields = 10
copy.field1 = "lastname"
copy.field2 = "datepaid"
copy.field3 = "duespaid"
copy.field4 = "assesspaid"
copy.field5 = "percent"
copy.field6 = "trade"
copy.field7 = "dues"
copy.field8 = "firs_nam" '20
copy.field9 = "midinit"
copy.field10 = "dept_num"
a_tbl.copy()
file_add_to_db("c:program filesa5v4union"+chr(92)+new_name+".dbf")
query.order = "lastname+firs_nam+midinit"
a_tbl.close()
a_tbl.open("1999_1")
a_tbl.delete_range("substr(dtoc(date()-365),7,4)=substr(duespaid,7,4)")
a_tbl.close()
a_tbl=table.open(alltrim(new_name)+".dbf")
table.index_create_begin("lastname+firs_nam+midinit","lastname+firs_nam+midinit")
index1=a_tbl.index_create_end()
tbl.index_primary_put("lastname+firs_nam+midinit")
a_tbl.close()
end

I gotta tell you this, I have learned a lot about A5 from this one function, all due to you.

Thanksamillion,
Ed

[Stan Mathews]

RE: index_create

Worth a try. You could display a ui_msg_box() to tell that the index had been created, then when the user clicked ok enough time should have elapsed to solve the problem.

[Schulzie]

RE: index_create

Great idea Stan, Thanks.

Ed