I got some advice from a fellow Works Member. He suggested to stay away from keys that are numeric. Whoa!!! That is a big concern for me! I mean geessss… almost every invoice is a number. And if I use the invoice number as the key (record id) in a table (file), will there be problems???

So, I have figured a work around. Just prefix the number with an "X" or something like that. And before I go and do a major MAJOR change to my code… I would like to know if anyone can comment on the usage of numbers as keys (record ids), and indexing effects.

Here is one example of my software where I use numeric invoices…

Expense payables. The key (record id) is "CUSTOMER*INVOICE" (i.e. 35000*123456), where 35000 is the "CM" table (file) key (record id), and the 123456 is the invoice of the customer. Now I have indexes for "CUSTOMER" and "INVOICE", so that tracing can be done by customer resulting in a list of invoices, and by invoice resulting in a list of customers. The indexes are crossref type. And I am about to do a check on this myself. If anyone already knows of flaws in using this method, I would definately appreciate the input.

By the way… This worked just fine in Arev.

Richard,

I think I said keys that you think of as strings but look like numbers. INTEGER keys are ok. Multi-field keys are OK.

- Oystein -

I have never had problems with numeric keys and indexes…though I have always used Btree indexes instead of Cross-Reference ones. I did run into the 25E600 problem, however, where this is interpreted as a number in scientific notation rather than an alphanumeric id (that took a while to figure out!). The only issue that I can see might be a problem with numeric keys would be where 12345 and 012345 refer to different records, but I've never had to deal with that scenario, so I'm not sure. There are countless systems out there that use a numeric invoice number and I know that many of them index on it without problems. I wouldn't start changing all your code to prefix invoice numbers with a letter just yet…unless some of the technical gurus have a valid reason to do so.

Thanks Oystein and Gray. I get what you both mean.

I have done some tests, and I find no problems so far. The only one problem I have found so far was the problem I had earlier in the week with my "FB" table and keys like "02.12345" "02.000123". And after help from you, Oystein, I changed the "." to "_" and it solved the problem.

One of the problems of using numeric keys (particularly sequential numbers) is the way in which integer keys can get distrubuted within a LH table. If they are integers, B-Tree indexes will work fine. The problem with LH tables and sequential keys is one of "clustering". In order for LH to work efficiently, the placement of keys within the table should be essentially random (or as much so as possible). AREV's (and OI, for that matter) LH algorithm favors string variables for keys. That's why I sometimes use an alpha character in front of a key to write and use an ICONV/OCONV technique to deal with it in screens, reports, popups, etc.

The use of numerics in multi-part keys is ok if you use the standard asterisk delimiter. However, when I originally converted my old Pick System to REV F, I had used an asterisk as a separator and a period to indicate a sub-account such that my chart of accounts file looked like TYPE*ACCOUNT.SUB-ACCOUNT where Type was Asset, etc. The period between account and sub-account caused no trouble in REVF or REVG but made havoc of the file when it was converted to AREV.

Anyway, integers are ok if they don't accidentally get converted to scientific notation.

HTH

Don Miller

Don,

What do you think of this Convert_To_Unscientific_Notation() function I just came up with?:

function Convert_To_Unscientific_Notation( Number )

begin case

case Number=1 return "One"

case Number=2 return "Two"

case Number ] 2 return "Many"

case true$ return "Huh?"

end case

- Oystein -

well that'll do it. In fact yesterday, I had a similar problem where a number got into scientific notation as a result of a division:

20/50 as in

Num=time/60 ; where time is a number of seconds

Anyway what I needed to do is to express time in decimal rounded to 15-minute increments so:

HR=FIELD(NUM,'.',1)

MI=FIELD(NUM,'.',2)

BEGIN CASE

CASE MI=0 ; FRAC=00

CASE MI <= 25 ; FRAC=25'

CASE MI <= 50 ; FRAC=50'

CASE MI <= 75 ; FRAC=75'

CASE 1

  HR+=1

  FRAC=00'

END CASE

TI=HR:'.':FRAC

The problem is that MI came through in scientific notation due to the repeating decimal so:

Before doing the above

NUM=ICONV(NUM,'MD2')

NUM=OCONV(NUM,'MD2')

This removes the scientific notation. Nonetheless a genuine PITA

Don

Richard,

Either you misunderstood what was being said or (hopefully they are a friend) someone is yanking your chain

The only thing I can think of is the possiblity of some clumping(?)

We have used integers for 99.9% of all keys since 1984 can't think of a time that they have been a problem.

There are problems, as is being discussed on the works side, with ids which can/are converted to scientific notation. Also if you have a key with leading zeros it will probably get stripped down to just the integer portion.

But other than these you should be okay. Don't change a thing unless some one comes here and states specifically what the problem is ..

dsig@sigafoos.org.com onmouseover=window.status=the new revelation technology .. a refreshing change;return(true)"

David Tod Sigafoos ~ SigSolutions

Phone: 971-570-2005

OS: Win2k sp2 (5.00.2195)

OI: 4.1.1

PII 300 laptop

Don,

Now you're talking about a different problem, right? So far we've talked about alphanumeric data - codes and ids - that are mistaken for numeric data. But you're talking about numeric data - data really meant to be numbers - real real numbers if you like - sometimes getting converted from decimal notation to a different syntax - scientific notation - and thereby making problems for string-based operations assuming decimal notation. Correct?

I wasn't even aware of this "new" problem.

- Oystein -

Well, I suppose it's somewhat different except if you use a small enough value and use it as a key to a record, you'll see the same results. It will probably be calculated correctly but it sure will mess up a report or trying to get it back. This is particularly true if you compute the value for a record key and don't use a form to create it. Anyway, it's not a new problem.

Don

Don,

New to me I meant. Thanks for alerting me.

- Oystein -

For 3.12 we were all set to add in a system flag so that scientific notation required a + or -, so 123E45 would be a string. You would have to reference 123E+45 in order for the math libs to take control. Unfortunately, it was vetod.

Still think it would be a good change for future releases though.

View this thread on the forum...