Conditional Formatting 1

[mstrcmtr]

How can Format a Numeric Field Format Expression on condition in report

e.g.

iif(lnFrmt = 1 , 99,99,99,99,999.99 , 999,999,999,999.99 )

 

[torturedmind]

Hello,

There was once a client had the similar requirement. What I did was to put two (2) field objects with the same expression (containing same field). One field have the the first format and the other field the other format. Then I put the condition in the "Print when..." in the first field and the NOT condition in the other field's "Print when...".

Maybe others will have a better idea.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Tore Bleken]

Create a function, and call it.

 

[Mike Lewis]

I'm with Kilroy. Put one field on top of the other. Use Print When to decide which of the two fields to display.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Putting two fields on top of each other may sound like a good idea, but it's a PITA to maintain. Bad idea!!!

I showed the "correct" solution on Foxite.

 

[Mike Lewis]

Putting two fields on top of each other may sound like a good idea, but it's a PITA to maintain.

I've never found this to be too much of a problem. But I suppose if you have a large number of fields that you want to treat that way, it could become tricky.

Another option would be to use a single (character) field, and to set its expression to:

[tt]IIF(lnFrmt = 1, TRANSFORM(NumField, "99,99,99,99,999.99"), TRANSFORM(NumField, "999,999,999,999.99"))[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

They don't have to be literally 'on top' of one another, just duplicate the detail line, using 'print when'
and set the line not to print if blank.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Tore Bleken]

It's still a PITA to maintain. A UDF is far better.

 

[JRB-Bldr]

First of all, a comma (',') is not part of a number. It is a Character that is used to clarify the reading of a number.

So you would first need to consider changing how the number is represented on a Report.

A Report Form can accept a Number, optionally work on it (such as summarize numbers, etc) and print it.
Or it can accept Characters. These characters may look like a Number or not, but they are Characters.

Based on what you show as your desired result above you might want to look in your VFP Help system for the command TRANSFORM().
It will enable you to change how a number appears when presented as a Character string.
And in your data you could have a Number, but on the Report Form have the Textbox Expression for that number be something like:

ALLTRIM(TRANSFORM(nTheNumber,"999,999,999,999.99"))

As to the "conditional" part of your questions...
Based on what you show in your original posting

iif(lnFrmt = 1 , 99,99,99,99,999.99 , 999,999,999,999.99 )

I have never seen a number with 2 digits between the commas, but regardless you could use something like:

IIF(lnFrmt, ALLTRIM(TRANSFORM(nTheNumber,"99,99,99,99,999.99")), ALLTRIM(TRANSFORM(nTheNumber,"999,999,999,999.99")))

However if it were me doing it, I would do all of this in a separate Report Cursor/Table BEFORE sending it to the REPORT FORM.

Good Luck,
JRB-Bldr

 

[TamarGranor]

If you're using VFP 9 SP2, there's no reason to put two fields on top of each other just to get different formatting.

After you put the field onto the report, use the Dynamics tab of the Field Properties dialog to set up the various formatting cases you want. In this case, you probably just need a slightly different Transform expression in the "Replace expression result with" textbox for each case.

Tamar

 

[mstrcmtr]

Tried through Dynamic Tab but dynamic tab conditions not working

 

[Olaf Doschke]

Well, take a close look, you NAMED the condition lnOpgDbtG=1 and 2, the condition itself has to go into the box labeled "Apply, when THIS condition is true".

.T. is true - always - it doesn't make use of a variable lnOpgDbtG (or whatever variable or field name, that's what I read from the screenshot).

Bye, Olaf.

Edit: Let me give another example:
Assume you have a gender field in data, then you could have formatting for male and female persons and also for persons, which gender is unknonwn (not sepcified in the database), eg the technical conditions are: gender=2 for female, 1 for male and 0 for unknown, then these are the three conditions and you could name them "Gender female", "Gender male" and "Gender unknown" and thereby also have a documentation of what the technical conditions mean.

 

[Tore Bleken]

You asked the very same question on Foxite, and I gave you the correct answer. Why don't you reply there?

 

[Olaf Doschke]

Besides: This tab doesn't give you a chance to change the formatting, you set the TEXT, the value displayed, so instead you'd have to add a TRANSFORM() code in there with the corresponnding format used on the data, not just the format expression.

And last not least: Read the manual under the Sample section, the conditions are evaluated in the order you give them, if you keep the <default> at the top, it is taken always, this has to be pulled down to the bottom as "otherwise" case.

Overall it should be easier to use the general tab with an IIF expression like jrbbldr suggested or call a function, because your need is about the format expression and not about the font or coloring style.

Bye, Olaf.

 

[Mike Lewis]

This tab doesn't give you a chance to change the formatting, you set the TEXT, the value displayed, so instead you'd have to add a TRANSFORM() code in there with the corresponnding format used on the data, not just the format expression.

That's right. In which case, he might as well use the TRANSFORM() function directly in the field's expression, as I suggested right at the start of the thread.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads