Displaying negative numbers under certain conditions

[Former Member]

I have a Crystal Report which I am developing and need to display positive numbers as negative, but only under certain conditions.  I did not see anything on this type of questions so I decided to join and post it up myself.  This is a transfer report so if the location number entered in the parameter list is the SHIPPING location, I need to display retail, cost, and totals as negative.  Here is the formula which I came up with for ext cost, but it is returning all zeros.  I cannot seperate the to and from locations in the parameters because this report needs to display both in and out of that location.

if totext({?loc}) = totext({Command.SHIP_FROM}) then ({Command.EXT_COST}*-1)

Any suggestions?  Appreciate the help!

[abhilash_kumar]

Hi Jon,

If it returns zeroes, then it means that this part of the code fails:

totext({?loc}) = totext({Command.SHIP_FROM})

Do you have records where the value entered in the 'loc' prompt matches with records in the SHIP_FROM column on the report?

See if this helps:

if totext({?loc},0,"") = totext({Command.SHIP_FROM},0,"") then ({Command.EXT_COST}*-1)

-Abhilash

[Former Member]

Abhilash,

Thank you for the quick reply.  Yes, I am sure there are records that should be displayed with the parameter loc as the SHIP_FROM location.  When I entered the line suggested in the formula I receive and error saying "Too many arguments have been given to this function" and it highlihghtes the quoted section as underlined below:

if totext({?loc},0,"") = totext({Command.SHIP_FROM},0,"") then ({Command.EXT_COST}*-1)

[abhilash_kumar]

That can happen only if the datatype of the {?loc} prompt is 'string'.

Is there are reason why you're trying to convert both the database field and the prompt to string, if they're already of type string?

See if this works:

If Trim({?loc}) = Trim({Command.SHIP_FROM}) then ({Command.EXT_COST}*-1)

-Abhilash

[Former Member]

I am converting because I was prompted that "A string is required here" for Command.SHIP_FROM.  The data type is originally number I believe.

I received the same prompt when trying the code above, "A string is required here" and the "Trim({Command.SHIP_FROM})" section is highlighted.

[abhilash_kumar]

What is the datatype of the {?loc} prompt? If this is string, then use this code:

If Trim({?loc}) = Trim(totext({Command.SHIP_FROM},0,"")) then ({Command.EXT_COST}*-1)

-Abhilash

[Former Member]

Yes it is.  It worked!! Awesome thank you very much Abhilash!  I just had to add the else part and now we are showing negatives for anything shipped out and positive for anything shipped in!

If Trim({?loc}) = Trim(totext({Command.SHIP_FROM},0,"")) then ({Command.EXT_COST}*-1)

else {Command.EXT_COST}

[abhilash_kumar]

Glad it works, Jon.

Please don't forget to close the thread.

-Abhilash