Instr; Searcing for DXXXX or LXXX

[gennaroalpha7]

Hello -

Hello -

I know a search can be made within a field for a word, but I am not to sure, or I don't know how to accomplish this...To do a search in a field for certain characters within a larger set of characters.

For example: I would search, in the Call Description field, for DXXXX within CA0001DXXXXYYY or search for LXXXX within CA0001LXXXXYYY, the X is numeric characters. So it would be D or L with 4 numeric characters after the letter. I don't know what are those numeric characters. The x are variables. Can crystal accomplish this....if so, how can it be completed.

Thanks for your help....


G.

 

[pmax9999]

It will really depend on the consistency of the data you are using. For example, if the D and L is always the 7th character and the numbers are always the 8th, 9th, 10th and 11th, and you want to indicate on the report whether the field matches the criteria, you could use a formula like:

If      {Table.CallDescription}[7] in ["D", "L"] and
        Isnumeric({Table.CallDescription}[8 to 11])
Then    "Yes" 
Else    "No"

If you want the report to return only those records that match the pattern described in your post, you could add the following code to your record selection:

[COLOR=#F57900][existing Record Selection criteria][/color] and
{Table.CallDescription}[7] in ["D", "L"] and
Isnumeric({Table.CallDescription}[8 to 11])

If the data does not consistently match the patter you describe, the approach will not work but this should at least give you something to base your approach on.

Pete

 

[gennaroalpha7]

Hi Pete -

I have added your second suggestion above to the existing record selection. Here's how it looks...

(

InStr({CallLog.CallDesc},"Laptop", 1) > 0
or
InStr({CallLog.CallDesc},"PC", 1) > 0 )

and

{CallLog.CallStatus} = "Closed" and
not ({CallLog.Cause} in ["Cics errors", "Net-Circuit Err", "Net-Hardw Err", "Net-System Err", "No Activity", "Power Outage", "Procedural / User Error", "Procedural err"]) and
{Asgnmnt.GroupName} in ["Lan Ops Midwest", "Lan Ops North", "Lan Ops South"] and
{CallLog.RecvdDate} in "2013-06-15" to "2014-06-15" and
not ({CallLog.CallType} in ["ACD", "ATM", "ATM Debit Card", "ATM Debit Card Maint", "ATM Night Bag", "Audit/Jrnl Prnt", "Backup Failed", "Balance Detail", "Battery", "Blackberry", "Call Stack Error", "Card Reader", "Cash Check", "Cash Dispenser/TAU", "Cell Phone", "CIF/Data Warehouse", "Cisco Hardware", "Cisco UCCE (ACD)", "Combined Maintenance", "Comm Errors", "Communications", "Connection", "Customer/Account Inquiry", "Dataset", "Depository", "Dispenser", "Envelope Dispenser", "Enviroment", "Enviroment*", "Environment", "Environment.", "File Restore", "General Ledger", "Jet Forms", "Keyboard", "Keys/Combo", "Line Circuit", "Missing", "Monitor", "Mouse", "Multi Transaction", "No Activity", "Password -FC8", "Password-App", "Password-CACS", "Password-Evision", "Password-Lan", "Password-Lan.", "Pin Pad", "Printer", "Printing", "Printing.", "Procedural", "Question", "Receipt Printer", "Reg CC", "Slow Response", "Space Alloc", "Space Alloc.", "Supervisor Override", "T1", "Teller Capture", "Transfering data", "Unavail.", "Unavailable", "Unavailable..", "Unavailable...", "Unavailable....", "Vandalism", "Vault", "Vendor", "Vendor Meet", "Voicemail", "VPN"]) and
{CallLog.Category} in ["Hardware", "PC Hardware", "Personal Computer"]

and

({CallLog.CallDesc}[7] in ["D", "L"] and
Isnumeric({CallLog.CallDesc}[8 to 11])
)


If they go the route of your first suggestion. Would this be a formula? And where will I place it?

Thanks.

G.

 

[gennaroalpha7]

Hi-

After entering in the record selection...

({CallLog.CallDesc}[7] in ["D", "L"] and
Isnumeric({CallLog.CallDesc}[8 to 11])
)

The records that were on the report have disappeared. I also entered the code in the Group selection, just to see what happens, and the results were the same. Did I miss something?

Thanks.

G.

 

[pmax9999]

Like I said in my first post, whether you use a formula on the report or the code in the Record Selection will depend on whether you want the report to:
[ol 1]
[li]return all records and indicate whether they match the pattern indicated (my code does this with the words "Yes" and "No"); or[/li]
[li]return only the records that match the pattern (bearing in mind that if no records match, none will be returned).[/li]
[/ol]

Your latest post suggests to me there are no records that match the pattern, but the simplest way to test this would be to remove the code I provided from the record selection, create the formula I provided and place it on the report in the Details section beside the {CallLog.CallDesc} field. The formula will indicate those that match the pattern with "Yes" and those that don't with "No". If the results are all "No", none of the records have a {CallLog.CallDesc} that match the pattern you describe, which would explain why the record selection approach returns no records.

This is basic report troubleshooting.

If this doesn't help, the only suggestion I can offer is to share the report (with saved data) on Dropbox (or similar) without the additional record selection so we can see {CallLog.CallDesc} for ourselves.

Pete

 

[gennaroalpha7]

Hi -

When I enter the code in the record selection I get no records.

When I use the code in a formula and place it besides CallLog.Calldesc(memo) I receive records with the pattern, which is what is required. For example, in the second record (1085417) and third (1086579) record, in the CallLog.CallDesc(memo) the pattern is evident.

Please click the link to review the report.

https://www.dropbox.com/s/krgczadqq..., Hardware Search) - 06.15.13 -- 06.15.14.rpt

Thx,

G.

 

[gennaroalpha7]

Here's another version with all the other code removed out of the record selection. It appears to be working. I have only added what you gave me in the record selection. Why would it work without the other code? And why didn't it work with it in the record selection? How odd.

Please click to view the report.

https://www.dropbox.com/s/ngvqfqlka...dware Search) - 06.15.13 -- 06.15.14 v0.2.rpt

Thx.

G.

 

[pmax9999]

I've just had a look at the reports you shared but do not agree with your statement from the post of 24 Jun 14 17:07 that the pattern is evident in Ticket Numbers 1085417 and 1086579.

The first 11 characters of the {CallLog.CallDesc} field for those records are "My Hrive," and "Jessica Sta" which clearly do not match the pattern you identified.

Perusal of the output from the original file posted shows no records where the pattern is matched. In some cases there is a string that does match the pattern but it is not the first 11 characters of the field. It is going to be very difficult to work with memo fields where the relevant data could be anywhere within the text.

While technically possible, it would be extremely slow and inefficient. I can think of a couple of ways to do it such as using a loop to repeat the search multiple times within each field and each time starting 1 character further along. As I said, it will be very inefficient and therefore slow to run.

As I have said to you previously, you need to understand the data you are working with. This is yet another example of you giving the wrong information and me giving a solution that won't work consistently; it will work, but only when the computer number (?) is entered as the first 14 characters of the {CallLog.CallDesc} field.

Pete

 

[pmax9999]

Just for fun (I gotta get me a job), I thought I would give it a crack. This approach isn't pretty but does work (and simpler to code than the loop logic):

If      Instr(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace({CallLog.CallDesc},"1","^"), "2","^"), "3","^"), "4","^"), "5","^"), "6","^"), "7","^"), "8","^"), "9","^"),"0","^"),"D","^"),"L","^"),"^^^^^") > 0 
Then    "Yes"
Else    "No"

It relies on changing all "D", "L" and numeric characters to a "^" symbol and then uses INSTR function to look for 5 consecutive "^".

Like I said, not pretty ...

Hopefully, someone else can think of a more efficient way to do it.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete,

Thanks for giving it a shot. I didn't know that your code looks at specifically the first 14 characters of an entry. I thought it scanned all the entry text to look for the pattern. I didn't understand, my apologies.

I'll try your last post and revert back to you.

Thx.

G.

 

[gennaroalpha7]

When I (in record selection)enter the code it highlights the entire code and says "A boolean is required here."

I tried adding parenthesis in different areas of the code but that didn't seen to work.

Thx.

G

 

[gennaroalpha7]

Removed the quotation marks from No and Yes and no errors were detected.

In the Record Selection I am trying to group on 3 (Lan Ops Mid, Lan Ops North, Lan Ops South) groups but it's not taking. Instead it includes other groups as well. And I included a date range of, CallLog.RecvdDate(String), 2014-06-15 - 2014-06-16 but its bringing records in from 2013-04-04, the first record.

If Instr(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace({CallLog.CallDesc},"1","^"), "2","^"), "3","^"), "4","^"), "5","^"), "6","^"), "7","^"), "8","^"), "9","^"),"0","^"),"D","^"),"L","^"),"^^^^^") > 0
Then Yes
Else No

and
{CallLog.RecvdDate} in "2014-06-15" to "2014-06-16"


Thx.
G.

 

[gennaroalpha7]

I have recreated the report, with the code, and it's accepting the filtering...date range and groups. Seems to be fine. Please review...thx.

G.

https://www.dropbox.com/s/d0t2i3h2bqdzra8/Computer Report v0.2.rpt

 

[pmax9999]

Firstly, you never specified whether the objective was to
[ol 1]
[li]return only the records that matched the pattern; or[/li]
[li]all records and flag those that matched.[/li]
[/ol]

These two outcomes are very different and the approaches required are therefore different. The most recent code I provided was a formula to indicate whether the record matched (with a "Yes" or "No"). It was not intended to be used in the Record Selection, something I would have hoped was obvious to you.

To use it in the Record Selection, you would need to use the following code:

Instr(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace({CallLog.CallDesc},"1","^"), "2","^"), "3","^"), "4","^"), "5","^"), "6","^"), "7","^"), "8","^"), "9","^"),"0","^"),"D","^"),"L","^"),"^^^^^") > 0

Given your last statement that the report now seems to be working, I don't see much point in reviewing it, but I strongly suggest you test it thoroughly before accepting it as accurate. If you find it is not working, please provide specific and accurate details of what is wrong with the results and we can take a look then.

Pete

 

[gennaroalpha7]

Thank you for your help on this report.