WorkBench / Batch Edit errors when attempting to upload data to YesNo field

[nfshoobs]

v7.11.1

Noticed this morning that I cannot upload data into a YesNo field via Batch Edit or the Workbench. Am I doing something wrong? I can’t recall seeing this error before. When I include a YesNo field (in this case, I’m trying to upload “TRUE” into the isFigured field in CollectionObjectCitation table) I see the error below:

Error occurred during Validation
{
"uploaderstatus": {
"operation": "validating",
"taskid": "d7ce2de4-9067-497a-9614-3c3e7424a15a"
}, 
"taskstatus": "FAILURE"
"taskinfo": "IndexError('list index out of range')"
｝

This happens whether I try “TRUE” “True” “true” “Yes” “yes” “1” and “T” in the field. It also occurs when the field is blank. As long as I have a yesNo field as a column in the dataset, it throws that error.

Update: Can confirm that it seems to be an issue with my data or our DB, as the same batch edit works flawlessly on Sp7DemoFish.

Upload plan is below:

upload plan.json (2.0 KB)

[Grant]

Hi @nfshoobs,

Is it possible there is a pick list assigned to one of those yesNo fields?

This issue has historically always been caused by a pick list being assigned to a field in the Schema Config but the pick list no longer exists. This has been encountered a few times across different databases, and I’ve written up such cases here: GitHub #7375

If you can query on the database using SQL directly, it is very simple to solve:

To fix this, you’ll 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.

[nfshoobs]

Aha! Bingo. I had assigned a picklist to that field when initially setting up the table in our database and never removed it. I just removed the picklist from the isFigured field and it works like a charm.
Thanks Grant!