Contacts search query has "where" clause with wrong data

[danti]

Hi all,

SugarCRM 6.1GA here, having an issue with the SQL query on the Contacts advanced search.

I have the email opt out parameters set to a number of contacts and it is important that it stays like that
(having to deal with german customers, email opt out is a legal requirement).
Now when I run a search, looks like the SQL query is constructed with this WHERE clause:

AND ( contacts.email_opt_out like '1%'))

Since the SugarCRM instance was migrated from v4.5, the opt out values for the contacts are either "off" or "on", while it seems to me that now the query searches for "0" and "1" respectively.

How (and especially where) can I change the query to reflect the actual values in the db?
or do I have to update the values in the db?

thanks,
A.

[eggsurplus]

It sounds like a one time data fix is needed.

update contacts set email_opt_out = 1 where email_opt_out = 'on';
update contacts set email_opt_out = 0 where email_opt_out = 'off';

[danti]

@eggsurplus

thanks for the reply.
So your suggestion is to update the values in the table...
If the intention is to be "upgrade safe" then I am ok with it.

a.

[eggsurplus]

Yes, suggestion is to update the db because it sounds like this is due to the upgrade not altering the off/on to 0/1 for you.

[danti]

@eggsurplus
thanks a lot for your help