Validating a data set containing a field with an invalid pick list assigned results in an `IndexError('list index out of range')`

[grantfitzsimmons]

Describe the bug
When validating a data set with a field assigned to an invalid pick list, the user sees the following error: IndexError: list index out of range

To Reproduce
Steps to reproduce the behavior:

1. Set a field in the Schema Config to point to an invalid pick list (create a pick list, assign it to a field, then remove it from query builder search results)
2. Map that field in the WorkBench
3. Validate the data set
4. See error

Expected behavior
It should treat the field as if no pick list is assigned (no crash, no list item validation for cells).

Screenshots
Most recent incident of this:

Crash Report

{
  "uploaderstatus": {
    "operation": "validating",
    "taskid": "d93bc594-2d7d-4d34-ac11-9ee55cac75be"
  },
  "taskstatus": "FAILURE",
  "taskinfo": "IndexError('list index out of range')"
}

From the worker logs directly (a truncated version of this is shown to the user):

[2025-08-28 15:21:32,342: ERROR/ForkPoolWorker-1] Task specifyweb.workbench.tasks.upload[da31eaae-a9ea-4e1b-879d-1ed9deb7ae2f] raised unexpected: IndexError('list index out of range')
Traceback (most recent call last):
  File "/opt/specify7/ve/lib/python3.12/site-packages/celery/app/trace.py", line 453, in trace_task
    R = retval = fun(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/ve/lib/python3.12/site-packages/celery/app/trace.py", line 736, in __protected_call__
    return self.run(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/tasks.py", line 57, in upload
    upload_data(collection_id, uploading_agent_id, ds_id, no_commit, allow_partial, self, progress)
  File "/opt/specify7/specifyweb/workbench/tasks.py", line 45, in upload_data
    do_upload_dataset(collection, uploading_agent_id, ds, no_commit, allow_partial, progress)
  File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 185, in do_upload_dataset
    results = do_upload(
              ^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 340, in do_upload
    scoped_table = upload_plan.apply_scoping(collection, scope_context, row)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/upload_table.py", line 75, in apply_scoping
    return apply_scoping_to_uploadtable(self, collection, context, row)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 299, in apply_scoping_to_uploadtable
    f: extend_columnoptions(colopts, collection, table.name, f, row, ut.toOne, context)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 122, in extend_columnoptions
    picklist = None if picklists is None else picklists[0]
                                              ~~~~~~~~~^^^
  File "/opt/specify7/ve/lib/python3.12/site-packages/django/db/models/query.py", line 450, in __getitem__
    return qs._result_cache[0]
           ~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range

Reported By
Reported by Kamil Dobosz on behalf of MCSN (Lugano)

---

Workaround

You need to find all items in the splocalecontaineritem table where the PickListName does not exist in the ⁠picklist table in the ⁠Name column

You can find all pick list items that will cause this case by running the following:

SELECT
    d.Name                   AS `Discipline`,
    sc.Name                  AS `Table`,
    slc.Name                 AS `Column`,
    slc.SpLocaleContainerItemID,
    slc.PickListName,
    p.TableName              AS `PickList Table`,
    sps.Text                 AS `Field Label`,
    slc.IsHidden             AS `Is Hidden?`,
    sps.Language             AS `Language`,
    sps.Country              AS `Country`
FROM splocaleitemstr AS sps
JOIN splocalecontaineritem AS slc
  ON sps.SpLocaleContainerItemNameID = slc.SpLocaleContainerItemID
JOIN splocalecontainer AS sc
  ON slc.SpLocaleContainerID = sc.SpLocaleContainerID
JOIN discipline AS d
  ON sc.DisciplineID = d.UserGroupScopeId
LEFT JOIN picklist AS p
  ON slc.PickListName = p.Name
WHERE slc.PickListName IS NOT NULL
  AND p.Name IS NULL;

In the results, you'll see this:

Discipline	Table	Column	PickListName	PickList Table	Field Label	Is Hidden?	Language	Country
Paleobotany	collectionobject	text1	Phenology		Phenology	1	en

You can fix this either in the Schema Config directly or by removing the PickListName value from the splocalecontaineritem record directly in the database.

[alesan99]

Possibly same issue: #5328

Describe the bug
If a field in the schema config is set to a picklist that doesn't exist (appears to happen on database creation), the workbench validation stops abruptly with a vague error.

To Reproduce
Steps to reproduce the behavior:

1. Download this database: Download (username: username, password: password)
2. Use the "Broken Picklist" workbench plan.
3. Attempt to validate.
4. You should see an error.

To recreate from scratch, create a new database (I made it with Vertebrate Paleontology) or look for a field in schema config that is set to Pick List and displays (current, invalid value). You probably can cause this by manually changing it through sql as well.

Expected behavior
The error should be handled in a clearer and more elegant way. Something like the problem column turning red with the error "Picklist is invalid value".

Screenshots
See the invalid selected picklist here:

This causes an error during workbench validation that can be hard to track down:

chrome_UXDVdt1ZoF.mp4

• OS: Windows 11
• Browser: Chrome
• Specify 7 Version: 7.9.6.2
• Database Name: Newly created databases (Example) (username: username, password: password)

specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.8/site-packages/celery/app/trace.py", line 451, in trace_task
specify7-worker-1  |     R = retval = fun(*args, **kwargs)
specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.8/site-packages/celery/app/trace.py", line 734, in __protected_call__
specify7-worker-1  |     return self.run(*args, **kwargs)
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/tasks.py", line 43, in upload
specify7-worker-1  |     do_upload_dataset(collection, uploading_agent_id, ds, no_commit, allow_partial, progress)
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 126, in do_upload_dataset
specify7-worker-1  |     base_table, upload_plan = get_ds_upload_plan(collection, ds)
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 190, in get_ds_upload_plan
specify7-worker-1  |     return base_table, plan.apply_scoping(collection)
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/upload_table.py", line 33, in apply_scoping
specify7-worker-1  |     return apply_scoping_to_uploadtable(self, collection)
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 128, in apply_scoping_to_uploadtable
specify7-worker-1  |     wbcols={f: extend_columnoptions(colopts, collection, table.name, f) for f, colopts in ut.wbcols.items()},
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 128, in <dictcomp>
specify7-worker-1  |     wbcols={f: extend_columnoptions(colopts, collection, table.name, f) for f, colopts in ut.wbcols.items()},
specify7-worker-1  |   File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 104, in extend_columnoptions
specify7-worker-1  |     picklist = picklists[0] if len(collection_picklists) == 0 else collection_picklists[0]
specify7-worker-1  |   File "/opt/specify7/ve/lib/python3.8/site-packages/django/db/models/query.py", line 318, in __getitem__
specify7-worker-1  |     return qs._result_cache[0]
specify7-worker-1  | IndexError: list index out of range
specify7-worker-1  | [2024-10-17 14:15:11,125: ERROR/ForkPoolWorker-2] Task specifyweb.workbench.tasks.upload[bfbea947-befc-49d4-b5ce-27d81bb9e228] raised unexpected: IndexError('list index out of range')```

@alesan99 – Can you provide an example database for this?

Importantly, that pick list might not be invalid. Does it exist in another collection in the same discipline? If so, you must switch to that collection to edit it.

Update the issue with a link to an example database. Freshly created and no changes made aside from starting a workbench upload.

Looks like the picklist was never created.

[grantfitzsimmons]

Reported by Lucas Blattner at Naturhistorisches Museum Basel.
Resolved this by using the described workaround in the issue description.

nmb-finvert database:

{
  "uploaderstatus": {
    "operation": "validating",
    "taskid": "4d1ccee8-2f2b-4de2-932a-4a1be6eb1c1d"
  },
  "taskstatus": "FAILURE",
  "taskinfo": "IndexError('list index out of range')"
}

Full error from worker logs:

[2025-09-10 11:04:54,810: ERROR/ForkPoolWorker-1] Task specifyweb.workbench.tasks.upload[4d1ccee8-2f2b-4de2-932a-4a1be6eb1c1d] raised unexpected: IndexError('list index out of range')
Traceback (most recent call last):
  File "/opt/specify7/ve/lib/python3.12/site-packages/celery/app/trace.py", line 453, in trace_task
    R = retval = fun(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/ve/lib/python3.12/site-packages/celery/app/trace.py", line 736, in __protected_call__
    return self.run(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/tasks.py", line 57, in upload
    upload_data(collection_id, uploading_agent_id, ds_id, no_commit, allow_partial, self, progress)
  File "/opt/specify7/specifyweb/workbench/tasks.py", line 45, in upload_data
    do_upload_dataset(collection, uploading_agent_id, ds, no_commit, allow_partial, progress)
  File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 185, in do_upload_dataset
    results = do_upload(
              ^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/upload.py", line 340, in do_upload
    scoped_table = upload_plan.apply_scoping(collection, scope_context, row)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/upload_table.py", line 75, in apply_scoping
    return apply_scoping_to_uploadtable(self, collection, context, row)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 299, in apply_scoping_to_uploadtable
    f: extend_columnoptions(colopts, collection, table.name, f, row, ut.toOne, context)
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/specify7/specifyweb/workbench/upload/scoping.py", line 122, in extend_columnoptions
    picklist = None if picklists is None else picklists[0]
                                              ~~~~~~~~~^^^
  File "/opt/specify7/ve/lib/python3.12/site-packages/django/db/models/query.py", line 450, in __getitem__
    return qs._result_cache[0]
           ~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range

[grantfitzsimmons]

Most Common Cause:

• New invertpaleo disciplines are created with the Phenology pick list being assigned to Collection Object Attribute text1.

  Unfortunately, it does not exist in the picklist.xml file defined for the discipline:
  https://github.com/specify/specify6/blob/master/config/invertpaleo/picklist.xml

  https://github.com/specify/specify6/blob/bfc3083c74db068bde9af5093ede3ba19b600bd6/config/schema_localization.xml#L142485

         <desc name="text1" type="java.lang.String" isHidden="true" isUIFormatter="false" pickListName="Phenology" isRequired="false">

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/workbench-batch-edit-errors-when-attempting-to-upload-data-to-yesno-field/2921/2