In 5 (Often Forgotten) Productivity Tools I mentioned that List Views are a great way to review data. However, the TMS interface expects the SQL view to return only one row per selected record. If your table joins create more than one row per record, then TMS will truncate the SQL view results. If your table join excludes records, then TMS will repeat rows in the SQL view results. Either way, you get an Invalid property array index error and confused end-users. Here’s how to avoid it altogether.

In 5 (Often Forgotten) Productivity Tools I mentioned that List Views are a great way to review data. However, the TMS interface expects the SQL view to return only one row per selected record. If your table joins create more than one row per record, then TMS will truncate the SQL view results. If your table join excludes records, then TMS will repeat rows in the SQL view results. Either way, you get an Invalid property array index error and confused end-users. Here’s how to avoid it altogether.

One technique for avoiding an Invalid property array index error, is to use a table subquery inside the outer join.

SELECT O.ObjectID AS ID, OG.Country
FROM Objects O
LEFT OUTER JOIN ObjGeography OG ON O.ObjectID = OG.ObjectID AND OG.ObjGeographyId = 
	( SELECT MAX( OG2.ObjGeographyID ) 
	FROM ObjGeography OG2 
	WHERE O.ObjectID = OG2.ObjectID )

Another technique is to use a scalar subquery.

SELECT O.ObjectID AS ID, 
	Country = 
		( SELECT TOP 1 OG.Country 
		FROM ObjGeography OG
		WHERE OG.ObjectID = O.ObjectID )
FROM Objects O

Notice how only one row is retrieved because the subqueries are limited by either a TOP 1 clause or the use of aggregate functions, such as MAX() or MIN ().

It may appear to the end-user that this is the de facto field value, instead of one of many. Therefore, I like to pair these columns with a second column using a scalar subquery with a COUNT() function. This acts as a visual reminder to the end-user that other values exist for the same field.

SELECT O.ObjectID AS ID,
	AltNum = 
		( SELECT TOP 1 AN.AltNum 
		FROM AltNums AN 
		WHERE AN.ID = O.ObjectID AND AN.TableID = 108 AND AN.Description = 'Pre-accession'
		ORDER BY AN.EnteredDate DESC ),
	AltNumQty =
		( SELECT COUNT(*) 
		FROM AltNums AN 
		WHERE AN.ID = O.ObjectID AND AN.TableID = 108 AND AN.Description = 'Pre-accession' )
FROM Objects O

This example returns three columns: the ObjectID, the last pre-accession number for this object added to the Alternate Numbers submodule, and the quantity of pre-accession numbers that exist for this object.