If...then...else statement query

[amrnewman]

Dear all

I am working with CR XI on a SQL database.

I have a Rent account name field which if is a joint tenancy will contain either and '&' or the word 'and' e.g. Mr and Mrs X or Mr & Mrs X. Where the tenancy is a sole tenancy it will only be Mr X or Mrs X.

I have grouped my report by property codes which is fine. I have then listed all the clients associated with the active rent account against that property in details. This bring in all persons living ni the household, including children, all of whom have a client number. The report also shows whether the client is the primary client (true or False)

I need to be able to send a mailing to all clients who hold a tenancy - this is fine with sole tenancies, I cannot work out how to do this with joint tenancies and where there are more than one client on a sole tenancy.

I have tried an if...then...else statement as a formula (@Joint) so that all clients with a joint tenancy are '1' whether they are primary tenant or not, and only the primary tenant is '1' on a sole tenancy. All other clients are '0'

I then intended to use the 'Section expert' to suppress details where @Joint=0

The formual I have been trying in numerous ways is

if ((({hracracr.prim_clnt_yn}=False)
and
(not ({hrartamt.rent_acc_nm} like "?&?")))
or
(({hracracr.prim_clnt_yn}=True)
and
(not ({hrartamt.rent_acc_nm} like "?&?")))
then 1
else 0

So far it has not returned the result I want, and I have not yet tried to write in the suppression where there are children/other clients!

Can anyone please help with a simple solution!!!

Thanks, Ade

 

[Madawc]

Break the statement up. Have it as a set of Formula Fields and make them boolians, e.g.

({hrartamt.rent_acc_nm} like "?&?"

Display them on the details line, maybe an extra details section you add just for report development. They should show True or False and you'll discover if you're making false assumptions about the data.

One common problem is nulls, which stop a formula when they are encounted. The IsNull test must come first, when needed.

Note also that you don't need to say =True, at least not for a formula field boolian, I've never used boolian data. If you just cite the name that is treated as a test for True, or say not and the name for false.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CR85user]

Firstly,

From what you have described you want to only list the details for either :-

The primary candidate where the account is a sole tenant
or
The primary candidate where the account is joint tenancy

If so why not just place {hracracr.prim_clnt_yn}=True in your selection criteria?

If this isn't the case then maybe try the following

The ? wilcard is a replacement for a single character in crystal if I recall correctly. If that is the case then your filter of '?&?' will only match accounts like 'a&b' and not 'Smith & Jones'

The correct wildcard is the asterisk (*) symbol.

For your above selection the following would perform the same check.

if {hracracr.prim_clnt_yn} in [False,True] and
not(ucase({hrartamt.rent_acc_nm}) in ['*AND*','*&*']
then 1 else 0

I suspect though that you may be after something like the following:

//{@Check}
if {hracracr.prim_clnt_yn} = 'False' and
not(ucase({hrartamt.rent_acc_nm}) in ['*AND*','*&*']
then 1 else 0

And then suppress details where {@check} = 1

'J