-- populate Products_ProductDetails_UniqueName field as 
-- Products_ProductDetails_ProductDescription: Products_ProductDetails_Variant (Products_ProductDetails_UnitSize)

--CVH adding this to prevent from running on all databases
IF (0=1)
BEGIN

	delete from pal_SurfaceFieldData
	where surfacefieldid = (select recid from pal_SurfaceField where surfaceFieldName = 'Products_ProductDetails_UniqueName')

	insert into pal_SurfaceFieldData
	select (select top 1 recid from pal_Surface where name = 'products') as surfaceId, 
		t.surfaceItemId as surfaceItemId, 
		(select recId from pal_SurfaceField where surfaceFieldName = 'Products_ProductDetails_UniqueName') as surfaceFieldId,
		null as surfaceFieldValueDate,
		null as surfaceFieldValueDecimal,
		(
			(select top 1 sfd.surfaceFieldValueChar from pal_SurfaceField sf
			left join pal_SurfaceFieldData sfd on sf.recId = sfd.surfaceFieldID
			where sf.surfaceFieldName = 'Products_ProductDetails_ProductDescription' and surfaceItemId = t.surfaceItemId) + ': ' + 
			(select top 1 sfd.surfaceFieldValueChar from pal_SurfaceField sf
			left join pal_SurfaceFieldData sfd on sf.recId = sfd.surfaceFieldID
			where sf.surfaceFieldName = 'Products_ProductDetails_Variant' and surfaceItemId = t.surfaceItemId) + ' (' + 
			(select top 1 sfd.surfaceFieldValueChar from pal_SurfaceField sf
			left join pal_SurfaceFieldData sfd on sf.recId = sfd.surfaceFieldID
			where sf.surfaceFieldName = 'Products_ProductDetails_UnitSize' and surfaceItemId = t.surfaceItemId) + ')'
		) as surfaceFieldValueChar,
		null as surfaceFieldValueNum,
		null as surfaceFieldValueBool,
		null as surfaceFieldValueTime,
		null as surfaceFieldLookupID
	from (select surfaceItemId from pal_SurfaceFieldData group by surfaceItemId) t

END