Print Page | Close Window

Case Sensitivity in string field formulas

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21314
Printed Date: 01 May 2024 at 9:47am


Topic: Case Sensitivity in string field formulas
Posted By: adavis
Subject: Case Sensitivity in string field formulas
Date Posted: 26 Jan 2015 at 5:20am
I have a string field that stores usernames each time a user accesses a particular database. Each unique user has one of two values that would be stored in the logon field, either just their username or the domain\username depending on which application they used to access the database.

I wrote a formula to strip out the domain portion of the stored login credentials, then grouped my data on the formula so that I would return results for individual users regardless of how they accessed the database.

My formula @users = replace ({TABLE.LG_NAME}, "DOMAIN\", "")

When I use the formula to group the data, records where the username and DOMAIN\username are the same are grouped together; however, there are a handful of records stored as domain\username with the domain portion in lowercase and crystal is treating these as different records.

So, I sometimes end up with a separate group for an individual user if their login was stored with a lowercase domain\username value.

I hadn't expected Crystal to treat records in a case sensitive fashion like this and have not seen this issue before. Has anyone else experienced this?

I have tried unchecking the "database is case sensitive" feature in both report options and general options. Is there another place to look?

Any other tips?


Replies:
Posted By: DBlank
Date Posted: 26 Jan 2015 at 5:24am
from crystal help
 
Overloads
  • Replace (inputString, findString, replaceString)
  • Replace (inputString, findString, replaceString, startPosition)
  • Replace (inputString, findString, replaceString, startPosition, count)
  • Replace (inputString, findString, replaceString, startPosition, count, compare)
Arguments
  • inputString is a String containing substring to replace.
  • findString is a substring being searched for.
  • replaceString is the replacement substring.
  • startPosition is an optional Number indicating the position within inputString where substring search is to begin. If omitted, 1 is assumed.
  • count is an optional Number of substring substitutions to perform. If omitted, the default value is -1, which means make all possible substitutions.
  • compare is an optional Number indicating the kind of comparison to use when evaluating substrings:
    • 0 performs a comparison that is case-sensitive
    • 1 performs a comparison that is case-insensitive

      If omitted, a case-sensitive comparison is performed.
 

 

Posted By: adavis
Date Posted: 26 Jan 2015 at 5:25am
I missed that. Thanks DBlank.

Posted By: Vimal Nair
Date Posted: 27 Jan 2015 at 12:55am
If only the column value has to be checked without Uppercase or lowercase, Simple formula also will help.

@users = Uppercase(replace ({TABLE.LG_NAME}, "DOMAIN\", ""))

And Do the grouping on @users. But display the user according to your choice. If you need all uppercase, display the same groupname else Pull the value from your formula which mentioned in the question.

-------------
Born To Live

Posted By: adavis
Date Posted: 27 Jan 2015 at 2:13am
I did play around with using uppercase or lowercase, but there were other anomalies in the data that those equations would miss, respectively.

Using the information DBlank posted from the help guide, I did get it to work. I just needed to add the compare argument.

@users = lowercase(replace ({TABLE.LG_NAME}, "DOMAIN\", "",1,-1,1))


Print Page | Close Window