search hides records... sometimes: leading spaces

[DJuser]

I've looked widely around the forums and wiki but can't see a reference to this problem - searched for 'leading spaces' and etc. Did see what looked like people struggling with similar symptoms, with potentialoly the same root cause, but folks hadn't made the connection to leading spaces.

I'm talking here about the search facilities within a module, not the top-left search box.

I) Search text box doesn't strip leading spaces (eg Contact search boxes, or Account search boxes)
Sugar allows you when searching to have a leading space character in the search textbox - and it then will only match on records that also have a leading space! ie mostly none. This means that users who accidentally add a leading space, or who cut+past text into the search box can sometimes but not always find a record is 'missing'... = confused users!

ie Firstname:'Fred' (note no leadigng space)
can't be found if you type ' fred' (with leading space) in the search box - but the user doesn't see the extra space, so is confused - 'where did all the Freds go?!'

Solution: the search box should strip leading spaces - should be easy?

II) related - new records can have fields with leading spaces.
when adding Contacts or Accounts, Sugar allows leading spaces in most of the fields (first name / last name, address fields, telephone fields etc).
This means that you can't find them by searching, unless you use a leading space in the search textbox!


= confused users who've just added a record and can't find it afterwards by searching, but may stumble across it elsewhere

Solution: as above, the Edit fields should strip leading spaces before a record is saved.

Seems like a simple fix (I'm not a programmer) - I have PHP colleagues here I could ask to check it out ad submit changes - but can't believe this hasn't been addressed somehow already?

I'm using Firefox browser: Our server has SugarCRM Version 4.5.1d (Build 1273), OpenSource: no modules added or changed. Installed by my colleagues, on top of our existing Apache/MySQL/PHP set-up.

thanks for any input.

DJ
On Linux(fedora core 5) Apache (2.2.0 fedora). MySQL (5.0.22), PHP (5.1.6 - hope I've got this right)

[pblag]

Hi All!

I know how to fix it!

The problem is in the database structure.

As rule you do the search by fields with 'varchar' type

for example
name, last_name, first_name

so your search query will be like this

select id,first_name,last_name from contacts where deleted = 0 and first_name like 'Fread%'

unfortunately if you use db field with type varchar the fields 'Fread' <> 'fread'

so hot fix is to change the db field type from varchar to text

However you can't just change the field type from varchar to text you have to copy table structure to other database
delete old field and add new field with same name and type text.

after that you have to copy data from old table to new table
and last step is replace old table with new

Important! If you use windows version of mysql there is no problems with leading letters.

Hope it will help you!

[DJuser]

Petro - thanks for the quick reply!

Looks like it is indeed a real problem effecting everyone (nearly... not in Windows MySQL you say?)

However, your work-round sounds like a pain.

Is this something that the Sugar guys already plan as a bug-fix / patch?

I would hate to make all the changes as you suggest, and then have a problem upgrading to the next Sugar release because we break future compatibility.

Do people think I should copy this issue to the 'feature wishlist' forum?

Thanks

DJ

[pblag]

Hi!

I think that it is not a bug.

We have a choice to develop good search system or a good efficiency of sugar.

It is because we can't use all fields with type 'text' because the database will be very large.

[DJuser]

Maybe I wasn't very clear.

It's not about the database structure at all. That can stay exactly as it is.

It IS about filtering user entered text, BEFORE it is either
inserted into records
or
used in a search query.


Sugar could strip leading space from a name when the Save button is pressed. This would prevent a field starting with a space - which is half the problem.

Likewise, the Search pages could remove leading spaces from the text fields, before the search is actually done.

No change to the database at all - but makes it impossible for users to think that existing records are very occasionally lost, but do show up later = happier users!

Hopefully this is clearer now, why it is not about a trade-off of good or bad 'efficiency of sugar'

DJ

[Angel]

Maybe I wasn't very clear.

It's not about the database structure at all. That can stay exactly as it is.

It IS about filtering user entered text, BEFORE it is either
inserted into records
or
used in a search query.


Sugar could strip leading space from a name when the Save button is pressed. This would prevent a field starting with a space - which is half the problem.

Likewise, the Search pages could remove leading spaces from the text fields, before the search is actually done.

No change to the database at all - but makes it impossible for users to think that existing records are very occasionally lost, but do show up later = happier users!

Hopefully this is clearer now, why it is not about a trade-off of good or bad 'efficiency of sugar'

DJ

FWIW, I agree with you. I've also found that it doesn't strip the spaces at the end, which also leads to inadvertent problems when searching.

Yes, it is possible that someone might want to search for 'Angel ' instead of 'Angel' to distinguish between 'Angel' and 'Angela', but the number of times that comes up is likely to be far less than those solved by simply searching for 'Angel' and getting a slightly larger result list.

I can't think of a reason why leading spaces should be left.

[sherlock]

We too have the same issue.. all our Sales staff are getting very annoyed with this to the point they don't want to use SugarCRM because of it!

I can understand some users may want to search with a space at the end, but all of the users I have spoken with DO NOT!

As Sugar have identified that this is not a bug can we at least have an option in the ADMIN section (or the user preferences section) to enable/disable in search boxes the removal of white space from the end of the search query before the search request is processed? ... This is not a this would be great request but a we need this in-place for our business to continue to use SugarCRM in an effective & efficient manner!

As I said this is causing issues with all our Sales users that use the system daily.

Will this be re-assesed or do we have to resolve this ourselves?

Thank you.

AW.