`MultipleObjectsReturned` error when multiple pick list items exist with the same text in different case

[grantfitzsimmons]

Lucas De Cicco in KU ornithology just reported this error to me that he is getting during validation of a dataset in the workbench in KU bird database. The dataset name is specify.upload.2023.Aug.2 (2). Any ideas? He has uploaded multiple other datasets to the mammal database without any issues so this appears to be database specific.

Here's the full taskinfo response for context:

  "taskinfo": "MultipleObjectsReturned('get() returned more than one Picklistitem -- it returned 3!')"

Main Issue

The issue is that the WorkBench assumes picklistitem values are case insensitive, though this pick list has both a lowercase G and uppercase G as options. Once you remove either one, it works correctly.

This was discovered in the KU Birds database.

How it was discovered

This issue was caused by the field text12 in the Collection Object Attribute table. The column heading is "WeightUnits", and it has this picklist assigned to that field.

problematic_mapping.json.zip

Here is what the pick list looked like before:

PickList_SQL.csv

Solution

Once you remove one of the pick list items (either g or G), you can proceed.

Reported By: KU Birds

[grantfitzsimmons]

Similar to #1064

[grantfitzsimmons]

Same issue happened again today, same database.

It has to do with the "Vouchered?" field (objectcondition) in the KU Birds database from the same time.

It has three variations of the same word in different cases!

[grantfitzsimmons]

Same more than one Picklistitem issue again today, this time it took me 20 minutes of removing each field line by line until I eventually found the culprit. So tedious and avoidable...

[melton-jason]

Similar to #1064

This issue was resolved relating to Picklists in #4272.

The error likely occurs in the following place (the picklist.picklistitems.get(title=value) line) in the codebase

specify7/specifyweb/workbench/upload/parsing.py

Lines 198 to 202 in 4306a8a

	def parse_with_picklist(collection, picklist, fieldname: str, value: str, column: str) -> Union[ParseResult, ParseFailure, None]:
	if picklist.type == 0: # items from picklistitems table
	try:
	item = picklist.picklistitems.get(title=value)
	return filter_and_upload({fieldname: item.value}, column)

Picklists with type 0 are picklists whose item values are picklistitems.
Thus the error occurs because the picklist being matched by the WorkBench contains more than one picklistitem where the title field is the value that is being uploaded with the workbench.

As mentioned previously, this can easily occur when case-sensitivity matters in picklistitems.
If a solution can not be found to make the workbench respect sensitivity in this case, then there is likely no easily automated way to resolve which picklistitem is intended.

There are a few solutions:

• Handle the error and return a ParseFailure for that row/cell with user-friendly information about the error and let the user resolve it before the Dataset is uploaded again
• Choose an arbitrary picklistitem (such as the first result returned by a Queryset filtering on picklist.picklistitems.filter(title=value))
• Integrate this case into disambiguation and provide a UI which lets the user choose which picklistitem is intended

[emenslin]

Can recreate in edge (7.9.6)

[grantfitzsimmons]

As confirmation, this was encountered today again by Museum für Naturkunde Berlin in v7.11.1:

{
  "uploaderstatus": {
    "operation": "validating",
    "taskid": "f9442cad-b6d1-4e8b-b347-ca41ed815be4"
  },
  "taskstatus": "FAILURE",
  "taskinfo": "MultipleObjectsReturned('get() returned more than one Picklistitem -- it returned 2!')"
}

Same cause, same solution (removing the duplicate "unknown" value)

[grantfitzsimmons]

I think we should approach it with the following fix (re: @melton-jason's earlier comment):

Choose an arbitrary picklistitem (such as the first result returned by a Queryset filtering on picklist.picklistitems.filter(title=value))

[grantfitzsimmons]

Had this issue come up again in donana:

SELECT pli.title, pli.value, pli.PickListID, pl.CollectionID, COUNT(*)
FROM picklistitem pli
JOIN picklist pl ON pli.PickListID = pl.PickListID 
GROUP BY pli.value, pl.CollectionID, pli.PickListID
HAVING COUNT(*) > 1;

Sorted them out using a SQL statement like this ^

[bronwyncombs]

Reported by Rabern @ Purdue | [ticket 6190] (https://specifysoftware.bolddesk.com/agent/tickets/1320#update-6190)

After using the data mapper from a previous entry, as I generally do, I’ve clicked to validate the data before upload, and I get this message:

Error occurred during Validation

{

  "uploaderstatus": {

    "operation": "validating",

    "taskid": "419917c3-f613-42f7-af79-fa916b43173a"

  },

  "taskstatus": "FAILURE",

  "taskinfo": "MultipleObjectsReturned('get() returned more than one Picklistitem -- it returned 2!')"

}