Multiple fields to search

[UHsoccer]

Our customer data base has six fields for "account manager" if none assigned the value is either -1 or 0, else it has a UserID value that I can translate to a name.

Here is the record selection method i need. It works fine if I use the first three selections, when I submit as below, it times out

if {vwComp.AcctMgr1} > 0 then {vwName_1.Name} = {?Account Manager}
Else if {vwComp.AcctMgr2} > 0 then {vwName_2.Name} = {?Account Manager}
Else if {vwComp.AcctMgr3} > 0 then {vwName_3.Name} = {?Account Manager}
Else if {vwComp.AcctMgr4} > 0 then {vwName_4.Name} = {?Account Manager}

etc

Tables vwName are linked to the vwComp table with a single connection between the UserID fields, always using Left-Outer-Join

If there a better way to structure this selection? I hope so, because I also need to add a criteria for Country and State/Province and by that time the logic is out of control.

 

[UHsoccer]

I am using Crystal 8.5

 

[Madawc]

I'd use a formula field to find the correct name and then compair it to account manager.

I'd also do a preliminary selection on vwComp - presumably you don't want a record unless at least one field has a value of 1, so test for this first.

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

 

[GJParker]

Couple of ideas to try :

concatenate teh 6 fields and use a LIKE expression i.e.

{vwName_1.Name} + {vwName_2.Name} +......{vwName_6.Name} LIKE {?Account Manager}


You could also create UNION query to add each row in six times.

SELECT {vwComp.AcctMgr1} as Mgr, {vwName_1.Name} as Name
UNION
SELECT {vwComp.AcctMgr2} as Mgr, {vwName_2.Name} as Name
...
...
...
UNION
SELECT {vwComp.AcctMgr6} as Mgr, {vwName_6.Name} as Name

Then you could use a simple record selection

(vwName.Name} = {?Account Manager}

HTH




Gary Parker
MIS Data Analyst
Manchester, England

 

[UHsoccer]

Gary,

The Union idead might not work if my name is in Mgr1 and someone else's name is in one of the other Mgr fields. That will happen all the time