How to filter a table using an if,then,else statement 2

[Pegasys2K]

I have a table called Disabled which holds two numbers: 0 and 1 0 = Active 1= Disabled
I can find all my active profiles because the rule is that every profile can only have one Active instance but have multiple Disabled instances within it. So, I have two types: one has just disabled instances and no active instances. The other has active instances with disabled instances within it. What I need to do is write an if, then, else statement to filter out all Active profiles with disabled instances and return to me all the profiles that are actually disabled. Please help….

 

[Pegasys2K]

I forgot tell you, IM using Crystal Reports 8.5

Thanks!

 

[dgillz]

Do you want the report to prompt you for active or disabled and then return the appropriate records?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

Group on your profile field, e.g., {table.profileID}. Then create a formula {@active}:

if {table.disabled} = 0 then 1 //where {table.disabled} is the field
//that returns 0 for active and 1 for disabled

Then go to report->edit selection formula->GROUP and enter:

sum({@active},{table.profileID}) = 0

//where {table.profileID} is your group field

This should return only those profiles with only disabled instances.

-LB

 

[Pegasys2K]

IM sure your formula would of worked have I given more of a landscape view of my tables. There are 3 tables in which I can get data from, i.e. company,cardholder,and badge. I add company name into my report from that table, I add {cardholder_lname} + "," + {cardholder_fname} from Cardholder table, I can also add from Cardholder the person's ID # which is {cardholder_c_nickname}, and from the Badge table I can add the person's badge prox # which is b_number_str, lastly I can add from the Badge table {b.disabled} which is the 0 and 1 where 0=active and 1=disabled. I can select expert d.disabled to =0.00 and get all active because no one is allowed more than one active badge prox# but can have multiple disabled badge prox#'s. So if i do a select expert search to =1.00 then I get all disabled badge prox# even the one's within active profiles. So with that said, whew! Can you help me filter {b.disabled} to look for all of my actual disabled profiles. I hope this helps. Thanks again for your time and effort!

 

[lbass]

Please reread my post. The formula should be created in the formula editor, not the record selection formula area. Then you go to the GROUP selection formula area and enter the second formula I showed above.

-LB

 

[synapsevampire]

There's probably a date field on the Badge table, so you can look for the most recent status of each.

A simpler and more accurate way to convey specs are to supply the following:

Crystal version
Database/connectivity used
Example data
Expected Output

Text can leave a good deal to the imagination, as this post is beginning to demonstrate.

I'm still not certain what you want, do you only want those that are currently inactive, or those that once had inactive, or?

Example data and the expected output that would result would make this very clear.

Stating that we must use an If, Then, Else may prove limiting too, why not simply state what you have and what you need and allow others to apply whatever means seems the most efficient to satisfy the requirements?

-k

 

[Pegasys2K]

IM sorry for the confusion,I've only took a 2 day course on crystal reports 8.5 and I've had about a year of reading help files when time was available. So IM definetly a newby. IM using Crystal Reports 8.5 and the database is ODBC/SQL
Example Data and Expected ouput is the following for identification badges:

Table.Company Name= all {company.names}

Table.Cardholder={last name} & {first names}, & {badge number}

Table.Badge=badge's proximity number i.e.{b_number_str}

Table.Badge={b.disabled} which is not a string but a number value of 0 and 1 where 0=active and 1=disabled

The badge number and badge proximity number can both be the same numbers or different but no two cardholders can have the same numbers so the range is very large. The situation is this: if someone looses a badge it gets replaced and the old prox # is disabled. If someone leaves the company, then the prox # is disabled therefore the cardholder's profile is disabled. What I need to do is find out who are disabled instead of how many disabled prox #'s.

lb- I tried that sum({Active},{b.disabled})=0 and the message I get is that "The summary/running total field could not be created"

Perhaps IM not following your instruction properly, IM sorry for that, it's the newby experience...

 

[Pegasys2K]

Allow me to clarify this statement:

The badge number and badge proximity number can both be the same numbers or different numbers in one cardholder's profile but no two cardholders can have the same badge # nor have the same proximity number as another cardholder therefore each badge # and prox # is unique to each cardholder. My dilema isn't within that area, because im trying to attack the number values 0 and 1 from the {b.disabled} table which identifies the prox # weither it's active or disabled in every single cardholder's profile. IM so, sorry for not being clear. I'll beat my face...i.e. (Pushups) Hooah!

Thanks!
Ranger

 

[Pegasys2K]

Can anybody still help me?

 

[lbass]

Sorry, very busy today. I'm not sure I'm clear on how you want to work with the {b.disabled} status field. Do you want to eliminate everyone who has any "Active" status? Then my earlier solution should work. You would create the formula {@active} in the formula editor:

if {b.disabled} = 0 then 1 else 0

You don't have to place this on the report. Instead you are going to use in the GROUP selection formula. First, though you must insert a group on {table.cardholder}. Then go to report->edit selection formula->GROUP (NOT record) and enter:

sum({@active},{table.cardholder}) = 0

//where {table.cardholder} is your group field.

You would substitute your actual table and field name for{table.cardholder}. This will return those cardholders who have no active statuses.

If you are only trying to evaluate the most recent status for {b.disabled}, then I think you could use the following GROUP selection formula instead of my earlier suggestion, as long as you have a date field, e.g., {table.date}, that corresponds to the {b.disabled} status to work with:

{table.date} = maximum({table.date},{table.cardholder}) and
{b.disabled} = 1

-LB

 

[Pegasys2K]

Hi lb!

Who's da Man? Da Bass Man!!! > {lb} I finally inserted the group the way it was supposed to be then I used the that formula of yours and it was magic to me. I can't thank you enough. I think my headache is finally subsiding. Lastly, yes my main goal was to produce a report of all of my disabled cardholders. It was so hard to do for me because so many active cardholders carried in their profiles disabled prox #'s. Thanks again lb!!!

Ranger