Crystal help. Newbie. Formula

[ililal]

I have three tables: User_role, users, provider.
In the users table, column provider_id can be null. Obviously, these are subsets
of the real tables.


User_role
role_id | description
1 | Admin
2 | SysAdmin
3 | Private
4 | General

users
user_id | role_id | provider_id
1 | 4 |
2 | 2 |
3 | 1 |
4 | 4 |
5 | 2 |
6 | 1 |
7 | 4 |
8 | 3 | 21
9 | 3 | 22
10 | 3 | 23
11 | 3 | 24
12 | 3 | 25



provider
proider_id | Provider_lastName | Provider_FirstName
21 | Kates | Fred
22 | AlastName | FirstName
23 | Kmart | Al
24 | Fantaz | Taz
25 | Nelson | Kirk
26 | Marino | Dan




Here is the sql. This works fine.

select
user_role.role_id,
user_role.DESCRIPTION,
users.user_id,
users.Role_Id,
users.provider_id,
provider.provider_id,
provider.provider_lastName,
provider.provider_FirstName
from
users
inner join user_role
on users.user_id = user_role.ROLE_ID
left outer join
provider
on users.PROVIDER_ID = provider.PROVIDER_ID

order by users.user_id



The end user can select a user_role and provider. The business logic
is:
1. If end-user selects role_id 3, All fields are to be displayed corresponding to the provider_id selected.

2. If end-user selects role_id 1,2, or 4. The query doesn't worry about the provider_id. The provider.provider_id,provider.provider_lastName,
and provider.provider_FirstName will be null, and all remaining fields will be returned.


Problem:
The end-user selects User_role 1 and 3 and provider_id 21 and 23. The Crystal report should report
all Users with a role_id as 1 and all role_id with 3 and provider of 21 or 23.
Is this possible in Crystal? I know it's possible with SQL and Java; I've done it before.


------------------------------------------------------------------
//1. I have to and it to the providerID
if Count({?userRoleId}) = 1 and {?userRoleId} = 3
then
{Command.ROLE_ID} = {?userRoleId} and
{Command.PROVIDER_ID}={?providerId}

//2. If 3 is not in the userRoleId
else if {?userRoleId} <> 3 then
{Command.ROLE_ID} = {?userRoleId}

//3. SQL not working the problem.
else
{Command.ROLE_ID} = {?userTypeId} or
{Command.PROVIDER_ID}={?providerId}


----------------------------------------------------------------
How does Crystal work?
Does it build the SQL every iteration?

 

[synapsevampire]

I think that you're overcomplicating this, try:

if 3 in {?userRoleId} then
(
{Command.ROLE_ID} = {?userRoleId}
and
{Command.PROVIDER_ID}={?providerId}
)
else
{Command.ROLE_ID} = {?userRoleId}

Not sure though, you speak of other fields, and then suddenly introduce a new parm in the last section:

{?userTypeId}

Perhaps a typo...

-k

 

[ililal]

Sorry, {?userTypeId} was supposed to be {?userRoleId}
//3. SQL not working the problem.
else
{Command.ROLE_ID} = {?userRoleId} or
{Command.PROVIDER_ID}={?providerId}

The problem is if the end-user chooses 1 and 3 for a role_id and 24 for a provider_id. The sql will do this:
if 3 in {?userRoleId} then
(
{Command.ROLE_ID} = {?userRoleId}
and
{Command.PROVIDER_ID}={?providerId}
)

This is correct for role_id 1. However,
the problem is that role_id 1 does not have a provider; therefore, nothing will be returned for role_id 1. Is this correct?
Thanks for your help.

 

[ililal]

BTW, {?userRoleId} and {?providerId} can have multiple values.

Thanks

 

[ililal]

That fix doesn't work.

The problem is if the end-user chooses 1 and 3 for a role_id and 24 for a provider_id. The sql will do this:
if 3 in {?userRoleId} then
(
{Command.ROLE_ID} = {?userRoleId}
and
{Command.PROVIDER_ID}={?providerId}
)

This is correct for role_id 3. However,
the problem is that role_id 1 does not have a provider; therefore, nothing will be returned for role_id 1. Is this correct?
Thanks for your help.

 

[Madawc]

You originally said

In the users table, column provider_id can be null.

Do you mean, you want details where provider_id is null? Or that it is allowed to be null for role_id 1? This could be handled by a test line

{Command.ROLE_ID} = {?userRoleId}  
and
(  (isnull({Command.PROVIDER_ID}) and role_id = 1)
   or 
   {Command.PROVIDER_ID}={?providerId}   )

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

 

[ililal]

I mean. If the end-user select 1,2,3,and 4 for a userRole and 24 for provider id, I want.

{Command.ROLE_ID} = 1
{Command.ROLE_ID} = 2
{Command.ROLE_ID} = 4
and
then

{Command.ROLE_ID} = 3 and provider_id=24

All data should be returned. I can write it in Java; however, the client is using this reporting module.

 

[lbass]

Try:

{Command.ROLE_ID} = {?userRoleId}
and
(
(
isnull({Command.PROVIDER_ID}) and
{command.role_id} <> 3
) or
(
{command.role_id} = 3 and
{Command.PROVIDER_ID}={?providerId}
)
)

-LB