| Author |
Message |
gmorrow
Newbie
Joined: 27 May 2010
Online Status: Offline
Posts: 5
|
  
Topic: Formatting Formula
Posted: 27 May 2010 at 5:17am |
|
Obviously I am new to Crystal Reports -
What would be the best way to make a field that comes up with a blank entry from time to time to display "No number supplied" or what have you? Would I need to do a formatting formula? Do I need to include a WhileReadingRecords entry or anything like that?
I am trying this formula, but I'm not sure that I'm doing this right in any way shape or form:
if {JrnlHdr.QuoteIDForSales} = "" then
{JrnlHdr.QuoteIDForSales} + 'No quote number'
else
{JrnlHdr.QuoteIDForSales}
and
if isNull({JrnlHdr.QuoteIDForSales}) then
{JrnlHdr.QuoteIDForSales} + 'No quote number'
else
{JrnlHdr.QuoteIDForSales}
Thank you to anyone who can help!!
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 May 2010 at 5:53am |
I am going to assume your field is numeric rather than a string
Create a formula
Set the formula option to 'Use defualt values for Nulls'
if {JrnlHdr.QuoteIDForSales} = 0 then 'No quote number'
else totext({JrnlHdr.QuoteIDForSales})
|
| IP Logged |
|
gmorrow
Newbie
Joined: 27 May 2010
Online Status: Offline
Posts: 5
|
Posted: 27 May 2010 at 6:49am |
|
Thank you very much for your help!
Actually I think the field is a string. The quote numbers are alphanumeric if that helps understanding. I'm assuming that makes it a different animal.
I did try your formula, no dice.
Do I make it as a formatting formula?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 May 2010 at 6:53am |
just need to switch the Zero to an empty string ("") and remove the totext conversion...
make sure you still set the formula option to 'Use default values for Nulls'
if {JrnlHdr.QuoteIDForSales} = "" then 'No quote number'
else {JrnlHdr.QuoteIDForSales}
|
| IP Logged |
|
gmorrow
Newbie
Joined: 27 May 2010
Online Status: Offline
Posts: 5
|
Posted: 27 May 2010 at 9:57am |
|
Not sure what I'm doing wrong! I put it in exactly as told, and... it's still displaying them.
Anything else I can try?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 May 2010 at 10:07am |
maybe I am not understanding what you want to do.
Sans script or formula speak, what do you want to accomplish?
|
| IP Logged |
|
gmorrow
Newbie
Joined: 27 May 2010
Online Status: Offline
Posts: 5
|
Posted: 27 May 2010 at 10:38am |
|
We use Peachtree Quantum with Crystal Reports. There are times when the salesperson won't make sure there is a quote number in for a particular quote. The system will generate one if they print or e-mail it, otherwise it will not generate one when it is saved. (silly, I think)
So there are lots of quotes which come up on the quote report I've created that are empty. As far as the layout is concerned, it looks like an error, and I want to clearly put the burden of proof back on sales, hence, I'd like to replace those empty quote number fields with the text
"no quote number entered" Do note that the quote appears just fine, it just doesn't have a quote number, which makes them harder to keep track of.
Edited by gmorrow - 27 May 2010 at 10:40am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 May 2010 at 10:50am |
Staring from scratch.
go into the Field Explorer
Right click on the FOmrula Fields and select New
Name=QuoteWithMissingText
in the formula editor add
if {JrnlHdr.QuoteIDForSales} = "" then "No quote number" else {JrnlHdr.QuoteIDForSales}
In the upper right hand of the tool bar (usually because you can move this) there are 2 pick list options
one should say "Crystal Syntax"- leave that as is
The other should say 'Exceptions for Nulls' or 'Default Values for Nulls'. Change it to use 'Default Values for Nulls'.
On your detail section in the report place the original {JrnlHdr.QuoteIDForSales} and this new formula field @QuoteWithMissingText next to each other and compare.
is it working as expected?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 May 2010 at 10:52am |
Wait....
Given the bad values of the data your users may have been bypassing this as a required string field in teh GUI by adding a space or multiple spaces...
try adding a trim to this formula to deal with this because you probably have all kinds of variations of spaces in the record set....
if trim({JrnlHdr.QuoteIDForSales}) = "" then "No quote number" else {JrnlHdr.QuoteIDForSales} Edited by DBlank - 27 May 2010 at 11:04am
|
| IP Logged |
|
gmorrow
Newbie
Joined: 27 May 2010
Online Status: Offline
Posts: 5
|
Posted: 27 May 2010 at 11:47am |
|
Works beautifully. Thank you so very much for all your time and attention. Believe me, we're working on them to enter the darn numbers like they are supposed to.
|
| IP Logged |
|
|
|
