VT711: Settings_Picklist_Module_Model->renamePickListValues() bug

See this line of code in the above method:

http://code.vtiger.com/vtiger/vtigercrm/blob/master/modules/Settings/Picklist/models/Module.php#L96

Whilst this is fine for UIType 15 and 16 picklists - it doesn't work for muti-select picklists. I think the same bug is present in the remove() method too. If you have a multi-select picklist and the field contains more than one value then the query is wrong because the column will contain something like:

picklistValue1 |##| picklistValue2 |##| picklistValue3

etc...

Here you go - I think this fixes it:

$query = 'SELECT tablename, fieldid, columnname, uitype FROM vtiger_field WHERE fieldname=? and presence IN (0,2)';
$result = $db->pquery($query, array($pickListFieldName));
$num_rows = $db->num_rows($result);

//As older look utf8 characters are pushed as html-entities,and in new utf8 characters are pushed to database
//so we are checking for both the values
$primaryKey = Vtiger_Util_Helper::getPickListId($pickListFieldName);
    if(!empty($color)) {
        $query = 'UPDATE ' . $this->getPickListTableName($pickListFieldName) . ' SET ' . $pickListFieldName . '= ?, color = ? WHERE '.$primaryKey.' = ?';
        $db->pquery($query, array($newValue, $color, $id));
    } else {
        $query = 'UPDATE ' . $this->getPickListTableName($pickListFieldName) . ' SET ' . $pickListFieldName . '=? WHERE '.$primaryKey.' = ?';
        $db->pquery($query, array($newValue, $id));
    }

for ($i = 0; $i < $num_rows; $i++) {
    $row = $db->query_result_rowdata($result, $i);
    $tableName = $row['tablename'];
    $columnName = $row['columnname'];
    if($row['uitype'] == 33) {
        $query = 'UPDATE ' . $tableName . ' SET ' . $columnName . ' = REPLACE(' . $columnName . ', ?, ?)';
        $db->pquery($query, array($oldValue, $newValue));
    } else {
        $query = 'UPDATE ' . $tableName . ' SET ' . $columnName . '=? WHERE ' . $columnName . '=?';
        $db->pquery($query, array($newValue, $oldValue));
    }
}

I added the uitype to the initial query and then made use of MySQL's REPLACE() function which is like php's str_replace() so it only replaces a matching substring.

And for the remove() method I did this:

if($row['uitype'] == 33) {
    foreach($pickListValues as $oldValue) {
        $query = 'UPDATE ' . $tableName . ' SET ' . $columnName . ' = REPLACE(' . $columnName . ', ?, ?)';
        $db->pquery($query, array($oldValue, $replaceValue));
    }
} else {
    $query = 'UPDATE '.$tableName.' SET '.$columnName.'=? WHERE '.$columnName.' IN ('.  generateQuestionMarks($pickListValues).')';
    $params = array($replaceValue);
    array_push($params, $pickListValues);
    $db->pquery($query, $params);
}```

@lord_alan The below mentioned format of picklist values is only applicable during save of record if user picks multiple values right.

picklistValue1 |##| picklistValue2 |##| picklistValue3

Where as in rename and remove, operations will be handled on single values only.Let me known if i have missed any case.

@uma.s I don't follow. If you look at the rename function, it tries to match on the whole data of the column using "="...

Let's assume I have a UItype 33 Multi select Picklist and the available values are Opt1, Opt2, Opt3.

I have a record in the system which has the values Opt1 and Opt3 in it. So the column in the entity table will contain "Opt1 |##| Opt3"

Then I go and change the spelling of Opt1 in the Picklist Editor to Opt4. Now look at your function query:

$query = 'UPDATE ' . $tableName . ' SET ' . $columnName . '=? WHERE ' . $columnName . '=?';
$db->pquery($query, array($newValue, $oldValue));

This will not work. $newValue will be "Opt4" and $oldValue will be "Opt1" but the column doesn't contain that. It contains the string "Opt1 |##| Opt3"

Make sense?

@lord_alan Right! updating the fieldvalue in related table will break if multiple values are selected. But renaming picklist values in picklist tables will be fine.

Will get this fixed. Thank you!

The suggestion above works fine and is fast. If you resort to using php str_replace/preg_replace and iterate over all rows that will be incredibly slow on large systems where there are hundreds of thousands or millions of rows of data (and we have some of these systems).

mentioned in merge request !433 (merged)

mentioned in commit 4bd10b78

mentioned in commit 82c0e019

Status changed to closed by commit cb8f3118

Milestone changed to 7.2.0