Record Select Criteria to Only Show Fields Containing Certain Characters

[lesleycampbell]

I am using CRXI. I need my report to only show fields that contain the following characters.

' - ! $ % * / ? @ [ ] ^ _ ` { | } ~ < >

I need the report to ONLY show the EmployeeID for any employee that has the above characters in any of these fields:
EmployeeMaster.EmployeeNameFirst
EmployeeMaster.EmployeeNameMiddle
EmployeeMaster.EmployeeNameLast
EmployeeMaster.EmployeeAddress1
EmployeeMaster.EmployeeAddress2

Help?

 

[pmax9999]

Not sure there is an easier way to do this. I certainly can't think of one.

Your record selection will need to look at each character in each field. On that basis, it is a record selection formula of 5 (fields) x 20 (characters) = 100 lines.

Your record selection formula will look like this:

Instr({EmployeeMaster.EmployeeNameFirst}, "/") > 0 or
Instr({EmployeeMaster.EmployeeNameFirst}, "?") > 0 or

... and so on for each character, then 

Instr({EmployeeMaster.EmployeeNameMiddle}, "/") > 0 or
Instr({EmployeeMaster.EmployeeNameMiddle}, "?") > 0 or

... and so on for each character, then 

Instr({EmployeeMaster.EmployeeNameLast}, "/") > 0 or
Instr({EmployeeMaster.EmployeeNameLast}, "?") > 0 or

... and so on for each character, then 

Instr({EmployeeMaster.EmployeeAddress1}, "/") > 0 or
Instr({EmployeeMaster.EmployeeAddress1}, "?") > 0 or

... and so on for each character, then  

Instr({EmployeeMaster.EmployeeAddress2}, "/") > 0 or
Instr({EmployeeMaster.EmployeeAddress2}, "?") > 0

If there are other selection criteria, add the code above at the end, preceded with "and", and wrapped in parentheses, ie "(" and ")".

Hope this helps

Cheers
Pete

 

[BobSuruncle]

You'll get better performance if you use SQL expressions
It's a little clunky with so many characters to check, but basically create one SQL Expression for each character that you need to check for.
In the SQL expression, concatenate the 5 columns together, and test for one of the characters in the resulting string.
My examples use SQL Server - Oracle will have different syntax:

CheckForDash

CHARINDEX('-',IsNull(EmployeeMaster.EmployeeNameFirst,'') + IsNull(EmployeeMaster.EmployeeNameMiddle,'') + IsNull(EmployeeMaster.EmployeeNameLast,'') + IsNull(EmployeeMaster.EmployeeAddress1,'') + IsNull(EmployeeMaster.EmployeeAddress1,'') )

CheckForComma

CHARINDEX(',',IsNull(EmployeeMaster.EmployeeNameFirst,'') + IsNull(EmployeeMaster.EmployeeNameMiddle,'') + IsNull(EmployeeMaster.EmployeeNameLast,'') + IsNull(EmployeeMaster.EmployeeAddress1,'') + IsNull(EmployeeMaster.EmployeeAddress1,'') )

In your Record Selection Formula, add each of these SQL Expressions, testing to see if they return a value greater than 0

e.g. (showing just 3 of the SQL expressions)

<your existing Record Selection>
AND
(
 {%CheckForDash} > 0
OR {%CheckForDollar} > 0
OR {%CheckForUnderscore} > 0
)

Bob Suruncle

 

[lesleycampbell]

The 'check for dash' and 'check for comma' SQL expressions would be saved as formulas?

 

[BobSuruncle]

No, these would be created as SQL Expressions, not Formulas.
SQL Expressions are found in the Field Explorer below Formulas.
SQL Expressions will only be available if you're connecting to a sql type database - SQL Server, Oracle, Sybase, DB2, etc.
If you're connecting to a local PC database like Access or Excel (not using ODBC) then SQL Expressions will not be available.


Bob Suruncle