Calculated expression problem

[George Corder]

If I have a numeric field and also a "Phone" character type field, I get a "0" for my records that have a phone number entered. I am using the following calculation expression: if(Branch="Fax Numbers",Phone,ltrim(str(ext)))

I would like the output of this list to be:

321
125
158
555-555-1234
320
etc....

Instead I am getting
321
125
158
0
320

Thanks

[Stephen Andrews]

RE: Calculated expression problem

George,

Where does the numeric field come into play? From what you said it seems that both 'Phone' and 'ext' are character type fields.

Steve

[Stan Mathews]

RE: Calculated expression problem

You seem to be saying that because you have a phone number entered, you get a zero from the expression

if(Branch="Fax Numbers",Phone,ltrim(str(ext)))

but that's not the way that expression works.

As written it says:

if the "branch" field is equal to "Fax Numbers", return the character expression in "Phone", otherwise return the character expression created by converting the numeric expression "ext" to characters.

It would seem to me that the "ext" in the record returning the zero is likely blank and "branch" is something other than "Fax Numbers".

You may be looking for an expression like

if(Branch="Fax Numbers",if(alltrim(Phone)>"",Phone,ltrim(str(ext))))

or maybe not.

[George Corder]

RE: Calculated expression problem

Sorry, I should have included this.

The field definitions are

Branch C 30
Ext N 4
Phone C 14

However, I have the report working simply by placing one field 'ext' over the other 'phone'. :)

This is simply a directory listing for an intranet so it will probably be fine with this solution.

I do appreciate both of your input.

[Stan Mathews]

RE: Calculated expression problem

Which will work fine unless there happen to be entries in both fields, which you believe won't be the case. If I won't bore you two much with two points to consider....

1. Ext N 4 - Unless you are going to use a field in a mathematical expression, avoid making it numeric. You've already experienced having to "ltrim(str(ext))" your ext field without it really being necessary (in terms of what the field structure could be).

2. I'm not sure what "Branch" and "Fax Numbers" have to do with your calculation. If you want to put the phone number (if it exists) on a report, and if it doesn't exist, put the extension, then

if(alltrim(Phone)>"",Phone,ltrim(str(ext)))

should do the trick. If you only want this to happen

if "Branch" = "Fax Numbers", then

if(Branch="Fax Numbers",if(alltrim(Phone)>"",Phone,ltrim(str(ext))),"")

should be the answer.

Since you have a solution it is probably not worth your time to work with it any more, but the principle is fairly significant.

Good luck,

Stan

[Stan Mathews]

RE: Calculated expression problem

"two much"! Sorry, senior moment.

[George Corder]

RE: Calculated expression problem

Thanks for the direction, I will try the if() statement tomorrow. I have one report that sorts based on name and then another report that sorts by ext. I have seen character type fields containing digits not be sorted properly in other applications and I was trying to avoid this. For example:

11
112
2
21
22

and I wanted to ensure sorting of

2
11
21
22
112

Perhaps changing my ext field to a character type field and a val(ext) sort expression would be the better design.?

Thanks

[Stan Mathews]

RE: Calculated expression problem

You can either make the character "ext" field fill left with zeroes

011
112
002
021
022

and it will sort as if it were numeric

or you could sort on val() as you have considered.

I prefer the first method for automatic alignment purposes.

Expression for auto fill with zeroes is:

padl(ext,3,"0")

Expression to display only the significant digits, when desired, is ltrim(ext,"0")

[Blake]

RE: Calculated expression problem

Just out of curiosity: What do you do when the extension =isn't= a numeral? Like "Extension J"?