Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

What's wrong with this formula

Status
Not open for further replies.

GRECONCG

Technical User
Oct 8, 2009
31
US
What am I leaving out of this formula

IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}>0 then {INVOICE.POSTADJ} else {INVOICE.REMITTED}
IF {INVOICE.POSTADJ}<0 then "CREDIT ON FILE"


It tells me the remaining text doesn't appear to be part of the formula (its pointing to this line IF {INVOICE.POSTADJ}<0 then "CREDIT ON FILE" )
 
IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}>0 then {INVOICE.POSTADJ}
ELSE IF {INVOICE.POSTADJ}<0 then "CREDIT ON FILE"
else {INVOICE.REMITTED}




_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
Should there be an ...and... or an ...or.. between the two statements?
 
when I try that it says a string is required here

{INVOICE.REMITTED}
 
You cant have mixed data types as an out put in a formula.
is {INVOICE.REMITTED} a date number field?

if so try
//
IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}>0 then {INVOICE.POSTADJ}
ELSE IF {INVOICE.POSTADJ}<0 then "CREDIT ON FILE"
else totext({INVOICE.REMITTED})

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
try the suggestion above...
what about {INVOICE.POSTADJ}? What field type is that?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
{INVOICE.POSTADJ} is also a number but negative

When I try the totext formula I get a number is required (it highlights IF)in front of INVOICE.POSTADJ.

 


Why is you invoice a NUMERIC value, rather than a TEXT value?

Are you going to ever ADD a value to the invoice id?

Are you going to ever SUBTRACT a value from the invoice id?

Are you going to ever MULTIPLY a value with your invoice id?

Are you ever going to DIVIDE a value into your invoice id?

Your invoice id is an IDENTIFIER and not a NUMBER. It may indeed be an IDENTIFER with numeric characters, but it is not an arithmetic value!

Should be stored as TEXT!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
IF {INVOICE.TRANS_TYPE}='R' and totext({INVOICE.POSTADJ})>0 then {INVOICE.POSTADJ}
ELSE IF {INVOICE.POSTADJ}<0 then "CREDIT ON FILE"
else totext({INVOICE.REMITTED})

Skip is correct though. If you are ever going to need to perform arithmetic in the result of this formula you will have to do something different. Let me know if thats the case

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
OK I may be going about this the wrong way I want the Payment applied to just say CREDIT ON FILE if the invoice total is negative So I have the formula for paymetapplied as this:

IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}=0 then 'Payment Credited' else
'Payments Applied'

and that gave me the correct wording Payment applied

and then I did this for Paymentremitted

IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}>0 then {INVOICE.POSTADJ} else {INVOICE.REMITTED}

and that gave me the numeric value, but I want to just have it say CREDIT on FILE if the invoice total is negative so what would I add to the paymentremitted formula to get it to do that?

 
Goes back to Skips question. Will you need the results of the formula to perform any arithmetic later? If not then use the to text function. If so then you cant do it in the same formula.

You could create another formula that returns the text value you need if the invoice is negative, overlay the field on your report with Paymentremitted. Suppress it if it is 0 or greater. Suppress the Paymentremitted formula if the new formula is < 0.




_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
I do not need to do any arithmetic later. I just need the payments applied to say CREDIT is the {INVOICE.INV_TOTAL}is <0 so how would I add that to this formula

IF {INVOICE.TRANS_TYPE}='R' and {INVOICE.POSTADJ}>0 then {INVOICE.POSTADJ} else {INVOICE.REMITTED}

 
OK I made a new formula that gave me the CREDIT ON FILE but I don't know how to supress the Paymentremit
I have
if {INVOICE.INV_TOTAL}<0 then "CREDIT ON FILE"

but need to supress the paymentremit formula How?
 
right click the field
slect format field then the common tab
click the X-2 button and enter
{INVOICE.INV_TOTAL}<0
save

on the other field do the same but enter
{INVOICE.INV_TOTAL}>=0

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
Create another details section i.e., right click details section on the left and select insert section below and insert your formula in that section and then suppress it.
 
It's not surpressing the payments applied formula, how can I supress the whole formula?
 
place the formula field where you want it on the report..

do as I mentioned above..

in the x-2 suppress formula enter the condition that you want the item to be supressed.

{INVOICE.INV_TOTAL}<0

or whatever the case is

abc solution would work nicely as well with the same suppression formulas and might be a little easier to manipulate. same suppression formulas would apply but in the section expert

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
Thank you very much it works and looks perfect.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top