if small caps then crred

Hello,

I'm wondering if someone would be able to help with a formula for a font color change based on the data containing small caps.

Something like this:

if {this.table} = lowercaps then crred
else if {this.table} = uppercase then crblack

there are probably better ways but here is one

if instr(lowercase({this.table}),{this.table},0)= 1 then crred else crblack

This worked flawlessly! Thank you very much!

Any chance you would part with a little more of your CR knowledge? The formula does work very well with all lower case. However, when there is a dash in-between, all of the data on every line turns red. Any ideas?

Works well for lower case: 2fh34r
Does not work for lower case: 2f-h34r

Thanks again!

Some basic tests using your sample string (2f-h34r) are not causing issues for me.
You can try and use a replace to remove the dash

instr(lowercase(replace('{this.table},"-","")),replace({this.table},"-",""),0)= 1

I tried your edited version, but received the following error:

The matching ' for this string is missing.

had an extra ' in it after the first replace. Just remove it

instr(lowercase(replace({this.table},"-","")),replace({this.table},"-",""),0)= 1

Thank you for your reply. I did try that, but was given another error:

"A number, or currency amount is required here."

you replaced {this.table} with your field and it is a string, correct?
What did it highlight when you got the error?

Yes. That's the odd part, nothing is highlighted as you would suspect. It simply moves the cursor back to the beginning. In front of:

// This conditional formatting formula must return one of the following Color Constants:

As before, I'm adding the formula to the font color.

ah - I thought this was a test formula outside the formatting
you need the if-then
if instr(lowercase(replace({this.table},"-","")),replace({this.table},"-",""),0)= 1 then crred else crblack

That got me closer. There is no error now. However, the first line of data now remains black, but all of the succeeding lines turn red, regardless of lower case and dashes.

a few samples please?

and your exact formula

Thank you again for taking the time to help with this.

The formula:

if instr(lowercase(replace({Large.Material},"-","")),replace({Large.Material},"-",""),0)= 1 then crred else crblack

The result:

B12-85632-003
30-6589256-025
23-6589564-026
63-3256895-028
a63-6325654-005
21-6925686-000
b32-2535689-002

a. It is likely not the "-" but rather the fact that they are all numeric values.
you can add another exclusion to deal with that

if ISNUMERIC(replace({Large.Material},"-","")) then crblack else if
instr(lowercase({Large.Material}),{Large.Material},0) = 1 then crred else crblack

Perfect! Thanks again.

One last thing, can you recommend a worthwhile CR course that would help me to improve my understanding of these types of formulas?

personally I think you can order Brian's book from this site and read it front to back to get a big picture of the possibilities and limitations. Then just read a lot of the posts every day in the various sub forums and then test them in your own environment in a sandbox report. It is a great way to see various approaches as well as get introduced to different functions and operators.