Hi, help on this subject is appreci 3

[gennaro7]

Hi, help on this subject is appreciated. I am creating a crystal report and I want to extract records, in the calldesc field, that have these keywords; voyager, mobility, cash edge. My table is Calllog and the field is calldesc. I am a beginner in SQL programming. What is the (query) code to do this extraction. thanks for the help.

G

 

[Turkbear]

Try ( as your record selection formula):

InStr({calldesc},"mobility") > 0 
or
InStr({calldesc},"cash edge") > 0 
or
InStr({calldesc},"voyager") > 0

If there can be upper or lower case then add those possibilities.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[HowardHammerman]

Tuckbear's solution will work. The only problem is the multiple ORs could cause problems if you have additional elements in your query.
Here is another way:

1) Create a forumla field (call it goodwords, with the following expression

whileprintingrecords;
stringVar array pickit := ["mobility","voyager","cash edge"];
{calldesc} in pickit

2) In the query simple select records where goodwords is true.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[gennaroalpha7]

Thank you Tuckbear and Howard Hammerman. I certainly appreciate your help. I will try this out.

Have a good one.

G.

 

[gennaroalpha7]

Hello Tuckbear and Howard Hammerman,

After completing this (above) query, is there a way to group the different groups i.e., ["mobility","voyager","cash edge"];

Thx.

gennaroAlpha7

 

[pmax9999]

I would recommend the approach suggested by Turbear as the most efficient. If there are "additional elements" in your query, simply add the code suggested at the beginning of the record selection, and enclose it in parentheses followed by an "and".

As for grouping, from the Menu Bar, select Insert >> Group and select the [calldesc] field as the "Group by" field.

Cheers
Pete

 

[gennaroalpha7]

Thank you pmax9999. Have a good one.

gennaroalpha7

 

[gennaroalpha7]

Hi,

In my record formula I have: {Asgnmnt.Assignee} in ["Fidelity", "Steve Ross", "Donna Lazarchik"]

But, in the field "Asgnmnt.Assignee" when I run the report I am getting everyone's name instead of just ["Fidelity", "Steve Ross", "Donna Lazarchik"].

What am I doing wrong - please.

Thanks.

gennaroalpha7

 

[pmax9999]

That part of the record selection formula looks OK, so I can only assume it is being over-ridden by some other part of the record selection.

Please post the entire record selection formula.

Pete.

 

[pmax9999]

And just to be certain, when you say it is in the "record formula", yoyu do mean that found from the menu bar under Report >> Selection Formula >> Record don't you?

Pete

 

[gennaroalpha7]

Hi, here it is...

InStr({CallLog.CallDesc},"mobility") > 0
or
InStr({CallLog.CallDesc},"cash edge") > 0
or
InStr({CallLog.CallDesc},"voyager") > 0
or
InStr({CallLog.CallDesc},"webwest") > 0
or
InStr({CallLog.CallDesc},"OBS") > 0
or
InStr({CallLog.CallDesc},"BOTW.com") > 0 and

{CallLog.CallDesc} in ["Mobility", "Cash Edge", "Voyager", "Webwest", "OBS", "BOTW.com"] and

Date ({CallLog.RecvdDate})>= Last7Days and

{Asgnmnt.Assignee} in ["Fidelity", "Steve Ross", "Donna Lazarchik"]


Thanks.

gennaroalpha7

 

[pmax9999]

Like I said in my first post, the "or" components need to be enclosed in parentheses so force the correct order of execution.

Try this:

(
	InStr({CallLog.CallDesc},"mobility") > 0 
	or
	InStr({CallLog.CallDesc},"cash edge") > 0 
	or
	InStr({CallLog.CallDesc},"voyager") > 0 
	or
	InStr({CallLog.CallDesc},"webwest") > 0
	or
	InStr({CallLog.CallDesc},"OBS") > 0
	or
	InStr({CallLog.CallDesc},"BOTW.com") > 0 
) and
{CallLog.CallDesc} in ["Mobility", "Cash Edge", "Voyager", "Webwest", "OBS", "BOTW.com"] and
Date ({CallLog.RecvdDate})>= Last7Days and
{Asgnmnt.Assignee} in ["Fidelity", "Steve Ross", "Donna Lazarchik"]

Hope this helps.

Cheers
Pete

 

[gennaroalpha7]

Pete,

It worked to good. I ran the report and no records showed. Should the paren encircle everything?

thanks.

g

 

[pmax9999]

No. The parentheses only need to go around those parts of the selection formula containing the "OR"s.

It could be that you database is case sensitive and you are not using the right case, of that the data is not quite what you think it is, ie leading trailing spaces, additional characters etc.

I would start commenting out parts of the selection code (1 line at a time) to identify which part is causing the situation. Once you know which part of the code it is, you can review the data to see what it really is.

Cheers
Pete.

 

[gennaroalpha7]

Thanks Pete for your time and help. I appreciate it.

Have a good one.

G.

 

[lbass]

(
InStr({CallLog.CallDesc},"mobility") > 0
or
InStr({CallLog.CallDesc},"cash edge") > 0
or
InStr({CallLog.CallDesc},"voyager") > 0
or
InStr({CallLog.CallDesc},"webwest") > 0
or
InStr({CallLog.CallDesc},"OBS") > 0
or
InStr({CallLog.CallDesc},"BOTW.com") > 0
) and
//[red]{CallLog.CallDesc} in ["Mobility", "Cash Edge", "Voyager", "Webwest", "OBS", "BOTW.com"] and [/red]
Date ({CallLog.RecvdDate})>= Last7Days and
{Asgnmnt.Assignee} in ["Fidelity", "Steve Ross", "Donna Lazarchik"]

Remove the red lines since you have already selected that field when it CONTAINS the desired words. The red line imply that the field is ONLY composed of those segments, which I'm guessing is not the case.

-LB