• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6229
  • Last Modified:

formatting within a formula field

I am trying to format a field within a formula field - example - If ({test} > 0, "This is a test", "This is not a test" & {test} &".") - I need the {test} to be red if it shows up.
0
marandrob
Asked:
marandrob
  • 7
  • 7
  • 2
  • +3
3 Solutions
 
MIKESoftware Solutions ConsultantCommented:
You need to format the color using the OBJECT itself. After you build you formula, place it in the report...then ...right-click on it...and choose FORMAT FIELD >>> FONT >>> COLOR... then use a formula to tell Crystal what color to use when a certain TEXT appears.

MikeV
0
 
marandrobAuthor Commented:
IIF ({statementdata.Med Coverage}=0,"You have elected not to participate in the Un-Reimbursed Medical Account (URM)","You habe elected to participate in the Un-Reimbursed Medical Account (URM) in the amount of $" & {statementdata.Med Coverage} & "." )

The actual formula is above and I only nees {statementdata.Med.coverage} to be a different color - I need the rest of the text to be black and that one thing to be Navy Blue.
0
 
MIKESoftware Solutions ConsultantCommented:
Yes...I completely understand the question.

YOU MUST DO THIS AFTER YOU BUILD THE FORMULA.

Read my above recommendation. Place the above formula into the report....THEN... RIGHT-CLICK the formula to activate the options menu...thend do the above...you need to assign a FORMULA...to the COLOR setting...for the OBJECT ITSELF....

Make Sense..?

MikeV
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
peter57rCommented:
You cannot do this in one formula field.

You will have tp split the formula field into bits so that you can format the amount separately rom the rest of the text.
So you need one formula to produce the text and a second formula to produce an amount(converted to text using Cstr() or ToText() ) or blank result.
You can then format the second formula field as red, since an empty value will show no colour.

0
 
marandrobAuthor Commented:
I am in the formula format editor - font color I put the following if {statementdata.HCSA} >0 then crNavy else crBlack - I want {statementdata.HCSA} to be navy is it is > 0 and black if it is 0 - I guess this is t the way to accomplish thi since it is always black.
0
 
marandrobAuthor Commented:
Also it is fine to add the second field at the end but what if the text that I need a different color is in the middle of a comment and there can be various different comments with different things that need to be different colors?
0
 
peter57rCommented:
I'm getting lost I'm afraid.
The question is changing each time you post.
I think you should repost and ask the question you want answering.
0
 
marandrobAuthor Commented:
The question I have is - In the following formula how do I make the data item before the period and only the data item before the period Navy blue (within the text of the formula)?

IIF ({statementdata.Med Coverage}=0,"You have elected not to participate in the Un-Reimbursed Medical Account (URM)","You habe elected to participate in the Un-Reimbursed Medical Account (URM) in the amount of $" & {statementdata.Med Coverage} & "." )
0
 
MIKESoftware Solutions ConsultantCommented:
I think I'm understanding what you need,..but...you are misunderstanding "HOW" Crystal works in regard to CONDITION COLOR FONT for FIELDS.

It sounds like you are trying to "conditionally" format the COLOR of the RESULT of your formula.

IF this is true....YOU CANNOT DO IT WITHIN THE ORIGINAL FORMULA IN CRYSTAL.

Crystal formats the FONT COLOR ...during the PRINTING phase. SO....what you need to do is create your FIELD FORMULA so as to provide the TEXT RESULTS you desire....THEN....add that formula FIELD to your report...wherever you want it to appear. NOW run your report. The TEXT will be the DEFAULT BLACK color. NO PROBLEM....ok....NOW.... rightCLICK the field in question....to OPEN the FORMATTING MENU.

CHOOSE:   FORMAT FIELD >>> FONT >>> COLOR

Then go to the FORMULA BUTTON that has the "X-2" on it....this will take you to a FORMULA EDITOR.

NOW...you need to tell Crystal WHAT COLOR to format the field...when certain TEXT appears in the field.

IF({Yourtable.field})= "THIS TEXT" then crRED
Else crBlack

Something like this.....

MikeV
0
 
marandrobAuthor Commented:
But won't this solution make all the text the alternate color not just one part of the text - I need to make just part of the resulting text a different color.
0
 
MIKESoftware Solutions ConsultantCommented:
Ok...here is what you need to do....

You need to create a FIELD that will display the TEXT portion of your formula that you want to CONDITIONALLY COLOR. THEN format that to display the COLOR ....however you want it to do...

Then CREATE a second field that will contain the OTHER TEXT WORDING that you want. ADD BOTH FIELDS TO YOUR REPORT.

THEN..."WHILE IN THE ACTUAL REPORT"....right click on the CONDITIONALLY FORMATTED TEXT FIELD and Copy IT.....then.....go over to the OTHER TEXT FIELD...and PASTE it into that field whereever you want.

The original conditionally formatted field ...will retain it's CONDITIONAL FONT COLOR SETTING.

It works..I just tested it here..

MikeV
0
 
marandrobAuthor Commented:
When you say past it into the filed - is that the formula?
0
 
MIKESoftware Solutions ConsultantCommented:
NO....What I did...was i created 2 formulas...

FORMULA 1
//@TextFormula
"This is the text I want to print - "

FORMULA2
//@ConditionalFormat
sum({MyTable.Field})

Ok..then I added BOTH formulas SEPARATELY to the report somewhere....doesn't matter where... just put them in the GROUP FOOTER...somewhere. You only need them so that you can FORMAT the conditional formula (FORMULA2).

Ok...THEN...rightclick on FORMULA2...and  go to ....FORMAT FIELD >>> FONT >>> COLOR
Then click the X-2 button...and enter your formula to CONDITIONALLY SET the COLOR.

Something like this:

IF {@ConditionalFormat} = "Specific Text" then crRED else crBLACK

then OK..and close...

Now...then...the formula itself should show the correct color. So RightCLick FORMULA2....and COPY......then RIGHTCLICK FORMULA1 and paste FORMULA2 INSIDE FORMULA1..whereever you want it to display....

Make sense..??

MikeV
0
 
MIKESoftware Solutions ConsultantCommented:
You must do the CUT/PASTE inside the actual report....that is to say...within the ACTUAL DISPLAY of FORMULA1......
0
 
marandrobAuthor Commented:
I am ok till the last step - I tried to past it in the formula fields edit screen and got an error - I guess I do not understand where I am supposeedto past the formatted filed.
0
 
MIKESoftware Solutions ConsultantCommented:
Oh well.......dang.... I didn't notice but it originally appeared that my FORMULA2 was inserted into FORMULA1's result.

Now I just noticed that it was NOT inserted...but simply pasted next to FORMULA1...which gives the appearance that it is a part of FORMULA1.

I'm not sure if this will work...It might.....if your two formulas can ACT in tandem.....separate from each other...but only DISPLAY  in the same area.....so as to give the appearance of a single field..
MikeV
0
 
bdreed35Commented:
Here is a actually a solution that you can do in one formula.
You can set your formula up with html tags like this:

If {@test} > 0
then "This is a test"
else "This is not a test <font color='red'>" & {@test} & "</font>."

Place the formula on the report, right click it, and choose Format Field.
Under the Paragraph tab, change text interpretation to HTML.
Preview the report and the tags should be gone and the field in between the tags should be red.
0
 
mlmccCommented:
ANother way is to create a text box on the report.

Have a formula
If {@test} > 0
then "This is a test"
else "This is not a test"

Drag it into the text box

Drag the {@test} formula into the text box

You can then format each field on its own.

mlmcc
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 7
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now