Using values that are not in database as formula criteria

[cmmc]

I am trying to write a forumula that is based on my parameter field being found in a database field and displaying a value based on this. If the parameter field value is not found in the database fields I want to display a different value in my formula. This is the formula I have tried but I get the error "keyword 'then' is missing".
I am using Crystal Reports version 8. I may not be approaching this the correct way so any suggestions are appreciated.

//Global stringvar active;

if {?Agent} in {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'Y'
then active := 'Active'
else if {?Agent} in {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'N'
then active := 'Inactive'
else if {?Agent} in{V_AGTSTAT_RPT.A04_ANUM}not
then active := 'Not Assigned'//

 

[synapsevampire]

Try:

if {?Agent} in {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'Y'
then 'Active'
else if {?Agent} in {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'N'
then 'Inactive'
else if not({?Agent} in {V_AGTSTAT_RPT.A04_ANUM})
then 'Not Assigned'

I guessed that you wanted the last condition to mean that you wanted not assigned to represent those not found in
{V_AGTSTAT_RPT.A04_ANUM})

If not, please state what the requirements are.

-k

 

[cmmc]

That is what I want, however, the formula you provided works in that it returns no errors and allows me to save it and use it in my report, but when I run the report using a parameter value that I know is not in my table, I get nothing. The other two parts of the formula are working as expected. I just can't figure out why this last part of the formula will not display the message I want it to. Any more suggestions? Thanks!

 

[Ngolem]

where is this formula being used??

I don't understand the use of

if {?Agent} in {V_AGTSTAT_RPT.A04_ANUM}

{V_AGTSTAT_RPT.A04_ANUM} is not an array but rather a single value in the database. So you are testing each element as it cycles though the retrieved data.

Explain how you are using this formula....doesn't sound right


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[cmmc]

My report is built from a view of a table that holds the field {a04_anum} and also the field {PAP}. The {a04_anum} field is really just an account number field and the {PAP} field holds an indicator that identifies the account status. When running the report the user is prompted to enter an account number (my parameter field). Based on the account number they enter and the value held in the indicator field, they should get a message that alerts them if the account is Active or Not Active. This part works fine. If a user enters an account number that does not yet exist in the {a04_anum} field of my view, then I want to still return a message to the user that will identify this account as not yet being assigned. It seemed simple to just include a part in the formula that says if the account number entered as a parameter is not in the {a04_anum} field of my view then the formula value would equal 'Not Assigned' - surely there is a way to do this. My report is being run against an Oracle database. I hope I explained this well enough - if not please let me know. Thanks!

 

[lbass]

I think the issue is that your view probably does not contain any records with null {a04_anum} fields. In order to return the message you want, you would need a table that includes the missing fields. Then you would use a left join from that table to your current table. Then try a formula like:

stringvar active;

if isnull({V_AGTSTAT_RPT.A04_ANUM}) or
{?Agent} <> {V_AGTSTAT_RPT.A04_ANUM}
then active := 'Not Assigned' else
if {?Agent} = {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'Y'
then active := 'Active'
else if {?Agent} = {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'N'
then active := 'Inactive';

-LB

 

[Ngolem]

I think LBass has it....since if the account does not exist then no records will be drawn back....assuming that the records are being drawn on the basis of the parameter.

I think I would add WhilePrintingRecords to LBass's formula


WhilePrintingRecords;
stringvar active;

if isnull({V_AGTSTAT_RPT.A04_ANUM}) or
{?Agent} <> {V_AGTSTAT_RPT.A04_ANUM}
then active := 'Not Assigned' else
if {?Agent} = {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'Y'
then active := 'Active'
else if {?Agent} = {V_AGTSTAT_RPT.A04_ANUM}
and {V_AGTSTAT_RPT.PAP} = 'N'
then active := 'Inactive';

active;


My experiments with this type of formula are that without "WhilePrintingRecords" at the top....it returns a NULL>

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

I didn't understand that you wanted data returned when you don't get a match, this could be quite complex with 3 different criteria.

What you seem to be saying is that you want a message when the repoort does NOT contain any data for a given parameter, which means fabricating data.

There are means to do so, but they are somewhat ugly to devise.

What you really want is a conditional section which displays when there isn't any data returned by the report, but if you are entering multiple values in the parm, then this won't work as simply.

Right click the Report Header and select insert section below.

Create a formula called NoRecsReturned and place the following in it:

"The parameter "+ {?MyParm} + " did not return any rows"

Or whatever text you want.

Then place it in the new section .

Right click the new report header section and select format section->X2 next to suppress

not(isnull({table.field}))

Placing some field that should never be null as the table.field.

Now you'll get a conditional display when you don't get any rows.

Hopefully this is what you're after.

-k

 

[cmmc]

Thanks for your suggestions, but I am not sure either of these solutions are what I need. I do not have any null fields in my table (though I tried something like this with a view). Only one parameter will be entered each time the report is ran. The parameter will either be 1. an acct number that exists in the table and is Active, 2. an acct number that exists in the table but is Inactive, or 3. an acct number that does not exist in the table at all and therefore is considered Not Assigned (and not other data will be expected if this is the case). I have been using the Select Expert to set Record Selection Criteria to AGENTS.A04_ANUM} = {?Agent}, but when I take this off and use my formula with the 'else 'active' := 'Not Assigned' it sets all to Not Assigned - regardless of acct number entered in parameter field.

 

[lbass]

I'm not sure why your table name changed (or why your account number parameter is called "Agent"--it's a little confusing), but if the account number is in the agents table and the parameter is for account numbers, then I think you should use a record selection formula of:

{AGENTS.A04_ANUM} = {?Agent}

This will return all account numbers with the selected parameter values, assuming you have it set up for multiple values. Then create two formulas:

//{@status} to be placed in the detail section along with
//{AGENTS.A04_ANUM}:
if {V_AGTSTAT_RPT.PAP} = "Y" then "Active" else
if {V_AGTSTAT_RPT.PAP} = "N" then "Inactive"

//{@Unassigned} to be placed in the report header section:
if not isnull({AGENTS.A04_ANUM}) then "" else
"Account number " + totext({?Agent},0,"") + " is unassigned."

-LB