BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Defining variable account numbers


 
Search this topic... | Search Legacy Crystal Products... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Legacy Crystal Products  Previous TopicPrint TopicNext Topic
Author Message
IT-Joe
Forum Member
Forum Member



Joined: 28 Feb 2019

Posts: 2



PostPosted: Thu Feb 28, 2019 6:32 pm 
Post subject: Defining variable account numbers

Hi,

I've trying to define a variable account number for customers to pay into.

We have a field called ZVaccNum that we have populated for those that need to pay into that account number. We also have a default account number.

I have defined a Formula Field called "ACCDefault" and all it contains is "AC: ######"

I've defined another formula field called "ACC" and it contains the following code.

Version 1
if {CUSTOMERS.ZVAccNum} > 0 then "AC: {CUSTOMERS.ZVAccNum}"
else {@ACCDefault}

Version 2
if {CUSTOMERS.ZVAccNum} = 0 then {@ACCDefault}
else "AC: {CUSTOMERS.ZVAccNum}"

Both versions both return the value "AC: ######" listed in ACCDefault

I need it to return either the value in the field ZVAccNum if the field is greater than 0 or @ACCDefault.

Can anyone help?
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



Joined: 16 Apr 2007
ASUG Icon
speaker.gif*3
Posts: 1894
Location: Atlanta, GA


flag
PostPosted: Wed Mar 06, 2019 3:09 pm 
Post subject: Re: Defining variable account numbers

Is ZCAccNum a number field in the database or is it a string field? Also, can it have a null value? If it could be null you can change the formula like this:

if isnull({CUSTOMERS.ZVAccNum}) or {CUSTOMERS.ZVAccNum} = 0 then {@ACCDefault} else "AC: " + ToText({CUSTOMERS.ZVAccNum}, 0, '')

(this assumes that ZVAccNum is a number field.)

Another way to do this would be to set the Null Handling option in the formula editor to use default values for nulls.

-Dell

_________________
Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics
Back to top
IT-Joe
Forum Member
Forum Member



Joined: 28 Feb 2019

Posts: 2



PostPosted: Sun Mar 17, 2019 7:28 pm 
Post subject: Re: Defining variable account numbers

Hi Hilfy,

Sorry for the late reply. It's been a bit busy the last couple of weeks.

The field is a number field in the database. SQL DB showing it as INT.

I've checked the data in the field and for non-populated fields, the data comes up as 0 otherwise it is populated.

I've tried your syntax above and it still didn't pick up the correct details.

I'm new to Crystal and so not sure where you're referring to when you refer to the Null Handling option.

Thanks
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



Joined: 16 Apr 2007
ASUG Icon
speaker.gif*3
Posts: 1894
Location: Atlanta, GA


flag
PostPosted: Tue Apr 09, 2019 9:18 am 
Post subject: Re: Defining variable account numbers

Sorry for my own late reply!

The option for null handling is in two places:

1. In the Formula Editor there are two menu bars. In the bottom menu bar there are two drop-down lists. The one on the right has two options: "Exceptions For Nulls" and "Default Values for Nulls". This option only affects the current formula.

2. Go to File>>Options>>Formula Editor. Toward the bottom of the screen is a drop-down list for "Null Treatment" that has the same two options. Changing this setting will affect all new formulas created after the setting was changed.

-Dell

_________________
Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Legacy Crystal Products  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0299 seconds using 17 queries. (SQL 0.0023 Parse 0.0009 Other 0.0267)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo