Seeking Set Linking Advice

[Peter.Greulich]

I use tables and sets based upon project numbers. Project numbers may be 3.00, 13.26, 470.01, 3895.12, etc. Would experienced Alpha users/developers recommend I use numeric values to link the set(s) or convert to character fields to link? What are the upsides/downsides of either?
Peter

[Stan Mathews]

RE: Seeking Set Linking Advice

Peter,

You need to tell us more about why a project number contains a decimal. What do the digits to the right of the decimal signify? If you need to be able to "strip off" or check the last two digits for sorting, etc, then character is the way to go.

Tom Cone has posted several excellent suggestions dealing with character vs numeric fields, the short of it being that if you don't need to perform a calculation on the field, choose character. (I hope I'm not misquoting Tom too badly hers)

[Peter.Greulich]

RE: Seeking Set Linking Advice

A new project gets a number - 3800.00
The next phase of the same project gets a sub-number - 3800.01 and so on. Our sub-office uses numbers in the three digit range - 500.00, etc. Keep in mind that this system is pre-personal computers, and one way or the other we have to live with it.

[eeetee]

RE: Seeking Set Linking Advice

hi:

i wrote a fairly large app useing numeric assignments for client numbers, project numbers, inventory etc. it did work, however after writing a second app using character expressions i found that the code was easier to write, and lookups etc ran faster. you also run the risk of numbers getting truncated if they have leading zeros. alpha 5 also does the queries based on character fields (if memory serves me corrctly). i have a fixed rule .. if it doesn't get calculated don't make it numeric.

regards,

ed

[Bill Warner]

RE: Seeking Set Linking Advice

I agree with the others about using a character field. I had an occassion to convert a customer's app from A4 to A5, and she used numeric fields for job number, cust number, etc., which were linking fields. It was more of a hassle to deal with numbers than with characters. Also, keep in mind that if, for some reason, you do have to calculate one of these fields, you can always use the VAL function to do so.

On the other side of the argument, you mentioned different values that you're using, ranging from 1 to 4 places before the decimal. If you change these to character and do NOT pad them with zeros (i.e., 3.00 would become 0003.00), then your sorting gets messy. For example, you would run into the records sorting like this:

1001.00
2.00
300.00
40.00

If padding isn't an option, I'd stick with numbers, even with their disadvantages.

[Daniel Weiss]

RE: Seeking Set Linking Advice

Peter
I would add a field to parent table with a calculated field rule; LTRIM(STR(your numeric link field,16,2)) and use that as the linking field. in the child table you only need the character field. its simple and safe
Daniel weiss

[shaun]

RE: Seeking Set Linking Advice

Hi Peter,

I am a new Alpha user, but you might consider having a project table with a project name field, and a unique character project number. Link this one to many to a child table on the project number field. The child table can have the sub-number and other fields relating to the sub-project.

This will relate all the sub-projects to the main project, and you can easily make a concatenated project reference using the main project id + the subproject id.

Hope this helps.

[Robert Adler]

RE: Seeking Set Linking Advice

Peter:

If this is a brand new system you might want to consider createing to fileds. Project_Number, Version_Number. Keep both as characters. build the index Project_Number+Version_number.

The index will help to keep the information in the correct location. It is usually good practice not to combine unique information. Just as you keep first name and last name in separate fields you should isolate project number and version number.

I hope this helps.

bob adler

[Peter.Greulich]

RE: Seeking Set Linking Advice

Thank you all for your many good suggestions. I will ponder your advice and make some kind of determination.
Peter