steve@violet.berkeley.edu (Steve Goldfield) (03/11/89)
In article <6056@bsu-cs.UUCP> neubauer@bsu-cs.UUCP (Paul Neubauer) writes:
#*Here is a question for anyone out there more knowledgeable than I about
#*dBASE. I am doing an application involving several tables (.DBF files)
#*where the main key for the principal table is the connection to the
#*subsidiary tables. Specifically, the main table is a table of service
#*requests and there are (several) other tables of, e.g. equipment needed,
#*etc. Each of these tables may contain one or more items related to the
#*requests table. I determined to use the request key as the relating field
#*and I then number the items in a category, e.g., equipment orders, so that
#*each item of equipment will have its own number. I then index on
#* request_no+str(item_no)
#*since the request number is a character string (though all digits) and the
#*item_no is a number (I do minimal arithmetic on it, i.e. increment it from
#*the preceding one). The problem arises when I add new items to the items
#*table. In order to enable default values for several fields, I use
#*APPEND BLANK and then REPLACE the default values into those fields. What
#*happens then is that the new records are not indexed properly. REINDEXing
#*solves that, but I certainly do not want to REINDEX every time I add a
#*record. Experimentation has revealed that if I change the item_no field to
#*a character field instead of a numeric field the index is maintained
#*properly and automatically, but when the index is on a character field
#*concatenated with a STR'ed numeric field the index breaks down. I have also
#*determined that if I use APPEND instead of APPEND BLANK the indexing works
#*properly whichever field types I have.
#*
#* Has anyone else ever experienced a similar problem? What have you
#*done about it? I assume that I can just turn the item_no field into a
#*character field and convert it back and forth, but that seems silly. I
#*welcome any better suggestions. Thanks.
#*
#*--
#*Paul Neubauer neubauer@bsu-cs.bsu.edu neubauer@bsu-cs.UUCP
#* <backbones>!{iuvax,pur-ee}!bsu-cs!neubauer
Check your manual on this, but I suspect you may be having a
problem with STR defaults because you don't specify the length
or number of decimal points of your output string, such
as STR(item_no,5,0). So if you expect to get all digits, it's
possible that there are spaces and periods embedded which
interfere with indexing as you intend.
Steve Goldfieldrl1b+@andrew.cmu.edu (Robert A. Locke) (03/13/89)
Paul,
You were correct in your assessment from the second message. It does have to do
with your using STR without specifying the length. The function creates a
varying length key which dBASE cannot handle. Since you were doing an APPEND
BLANK, the length of the field was set to zero. When you do an APPEND, it sets
it to a more understandable length (and I believe, takes on the length specified
in the field definition). This is why the first one corrupts your index and the
second one does not.
Now, as to the way you should probably solve your problem:
When you specify the numeric field in your database there will be two numbers
associated with it (length & decimal places). Simply use these numbers in the
STR(field,length,decimal) in your index key and everything will work fine.
--Rob Locke
-------
Arpanet: rl1b+@andrew.cmu.edu
Bitnet : rl1b@ANDREW
Fidonet: Rob Locke (1:129/15)
USNail : 5419 Normlee Place
Pittsburgh, PA 15217
USAdanielg@earl.med.unc.edu (Daniel Gene Sinclair) (03/17/89)
>#*Here is a question for anyone out there more knowledgeable than I about >#*dBASE. I am doing an application involving several tables (.DBF files) >#*dBase... >#*I then index on >#* request_no+str(item_no) etc... etc... [and the idexes are not updated properly when I append blank] Then another guy writes... >Check your manual on this, but I suspect you may be having a >problem with STR defaults because you don't specify the length >or number of decimal points of your output string, such >as STR(item_no,5,0). So if you expect to get all digits, it's >possible that there are spaces and periods embedded which >interfere with indexing as you intend. > >Steve Goldfield I am only a novice user but here's some suggestions. I think the Steve is right. With numeric feilds, if you don't specify the string length, dbase will give it a default length of 13 (no matter what the size of the feild). You can try the above suggestion, or maybe index on request_no+LTRIM(str(item_no3,1)) or the likes. Later! Disclaimer: My opinion means | 'If you only knew how much I was holding nothing, but His means | back, you would commend me' everything. | - Charles Spurgeon, 19th century danielg@uncmed.med.unc.edu | evangelist (on humor in preaching)
neubauer@bsu-cs.UUCP (Paul Neubauer) (03/18/89)
In article <407@uncmed.med.unc.edu> danielg@uncmed.med.unc.edu (Daniel Gene Sinclair) writes: >>#*Here is a question for anyone out there more knowledgeable than I about >>#*dBASE. I am doing an application involving several tables (.DBF files) >>#*dBase... >>#*I then index on >>#* request_no+str(item_no) > >etc... etc... > > [and the idexes are not updated properly when I append blank] > >Then another guy writes... >>Check your manual on this, but I suspect you may be having a >>problem with STR defaults because you don't specify the length >>or number of decimal points of your output string, such >>as STR(item_no,5,0). So if you expect to get all digits, it's >>possible that there are spaces and periods embedded which >>interfere with indexing as you intend. >> >>Steve Goldfield >I am only a novice user but here's some suggestions. I think the Steve is >right. With numeric feilds, if you don't specify the string length, > index on request_no+LTRIM(str(item_no3,1)) I was the person who posted the original question. I have already thanked several people who have sent email and I would like to thank the answering posters here. The answer, as I have managed to (mis?)understand it, does appear to have something to do with variable (or at least unspecified) length indexing expressions. I am not sure I understand it much better than that, but since I am not trying to write a bug-for-bug compatible compiler or the like, I guess I don't really have to. I would like to mention, for Daniel and Steve, though, that the problem really is with whether dBASE III+ _maintains_ the indexing properly during the append and edit operation, not with whether the expression sorts correctly. Note that the space character (leading blanks in the string) precedes all of the digits (and incidentally all other printables) in the ASCII collating sequence. Thus, the string "anything 9" correctly precedes and sorts ahead of "anything10" as long as the strings 'anything' are identical (including internal spaces). Therefore, strings with some sequence of code characters followed by STR'ed representations of numbers (even in their default lengths with lots of leading blanks) sort correctly by the numerical values, and REINDEX produces a correct index. The problem is that somewhere in the APPEND BLANK and EDIT process dBASE forgets to keep track of the fact that the index needs to be updated when the index is on request_no+LTRIM(str(item_no)) but not when it is on request_no+LTRIM(str(item_no,3)) I can, and by now have, work around this, but I can hardly see how to consider it anything other than a bug. Thanks again all. -- Paul Neubauer neubauer@bsu-cs.bsu.edu neubauer@bsu-cs.UUCP <backbones>!{iuvax,pur-ee}!bsu-cs!neubauer