`HierarchyException` when loading Collectionreltype

[grantfitzsimmons]

Describe the bug
When displaying the collectionRelType field as a query combo box, the user is presented with the following error:

HierarchyException at /api/specify_rows/collectionreltype/ queryset model 
Collectionreltype has no hierarchy field

This leads to the form being unable to load and the user no longer being able to use the application.

Form XML

<cell type="field" id="collectionRelType" name="collectionRelType" uitype="querycbx" initialize="name=CollectionRelType;isrequired=true;newbtn=false;editbtn=false" isrequired="true"/>

To Reproduce
Steps to reproduce the behavior:

1. Go to https://mpabi20240604-edge.test.specifysystems.org/specify/view/collectionobject/2135/?recordsetid=41
2. Wait for the record to load
3. See crash

Screenshots

Left is [v7.9.3.1](https://mpabi20240604-v793.test.specifysystems.org/specify/view/collectionobject/2135/?recordsetid=41) and right is [edge](https://mpabi20240604-edge.test.specifysystems.org/specify/view/collectionobject/2135/?recordsetid=41) (during v7.9.6 testing)

Crash Report
Specify 7 Crash Report - 2024-06-05T17_42_31.246Z.txt

Reported By
University of Michigan

IMPACT: This prevents me from keeping our newest project, the MPABI [michigan pathogen] database from upgrading to the newest versions of Specify [beyond 7.9.1.1].

User reports the following:
Problematic in v7.9.4
Originally working in v7.9.1.1
Tested to seemingly be working ok in v7.9.3.1 but the test was not exhaustive.

[melton-jason]

This issue was caused by #3304.
Specifically, this happens because as of #3240, the frontend overrides the CollectionRelType -> name field to be a picklist with items matching the name of CollectionRelTypes.

specify7/specifyweb/frontend/js_src/lib/components/PickLists/definitions.ts

Lines 207 to 212 in 43c4b74

	CollectionRelType: {
	name: definePicklist('_CollectionRelType', [])
	.set('type', PickListTypes.FIELDS)
	.set('tableName', 'collectionreltype')
	.set('fieldName', 'name'),
	},

When Specify is formatting the CollectionRelType, it has to fetch this frontend picklist and in this case, fetch all of the picklist items.

specify7/specifyweb/frontend/js_src/lib/components/Formatters/fieldFormat.ts

Lines 39 to 45 in 43c4b74

	// Find Pick List Item title
	const pickListName = parser?.pickListName ?? field?.getPickList();
	if (typeof pickListName === 'string') {
	const pickList = await fetchPickList(pickListName);
	const formatted = formatPickList(pickList, value);
	if (typeof formatted === 'string') return formatted;
	}

The picklist items for picklists based on a field are fetched via the below lines.
In #3304, it was made so that Specify will always try and scope the returned results for the picklist (for example, if you define a picklist based off of CollectionObject -> catalogNumber, only the catalogNumber's for CollectionObjects in the current collection will be converted into picklist items).
Previously, all picklist items for picklists of this type were unscoped.

specify7/specifyweb/frontend/js_src/lib/components/PickLists/fetch.ts

Lines 144 to 167 in 43c4b74

	/** From the field picklist */
	async function fetchFromField(
	pickList: SpecifyResource<PickList>,
	limit: number
	): Promise<RA<PickListItemSimple>> {
	const tableName = defined(
	pickList.get('tableName') ?? undefined,
	'Unable to fetch pick list item as pick list table is not set'
	);
	const fieldName = defined(
	pickList.get('fieldName') ?? undefined,
	'Unable to fetch pick list items as pick list field is not set'
	);
	return fetchRows(tableName as keyof Tables, {
	limit,
	fields: { [fieldName]: ['string', 'number', 'boolean', 'null'] },
	distinct: true,
	domainFilter: true,
	}).then((rows) =>
	rows
	.map((row) => row[fieldName] ?? '')
	.map((value) => ({ value, title: value }))
	);
	}

The issue here is that CollectionRelType can not be reliably scoped, so the backend throws the HierarchyException.
See related #3564.

[bronwyncombs]

• Reported by: Soraya
• Institution: Swiss Federal Institute of Aquatic Science and Technology (EAWAG)
• via Discourse

[specifysoftware]

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

https://discourse.specifysoftware.org/t/error-querying-the-collection-relationship-table/1827/3

[specifysoftware]

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

https://discourse.specifysoftware.org/t/create-a-collection-relationship-in-specify-7/1837/3

[mcruz-umich]

Very excited to see this get fixed so we can update to newest S7! Thanks for putting this in the 7.9.8 milestone!