asked on

SQL Server 2008 R2 Related Function

I have a table named WellDocuments with a WellID, DocumentTypeID and WellDocument fields.

I have another table named DocumentTypes with a DocumentTypeID, DocumentType and SortOrder fields.

The tables are related using the DocumentTypeID field.

I am trying to add a calculated field, SortOrder, to the WellDocuments table to lookup the SortOrder from the DocumentTypes table.

My formula is: =Related('DocumentTypes'[SortOrder])

I get the message: Error validating the formula for column 'SortOrder'.

Please help.

Thanks,

John Gobert

I may not be understanding you right but if the SortOrder field is in the DocumentTypes table and DocumentTypeID is a common field in both tables then you should be able to use a Join.

Select WellDocuments.*, DocumentTypes.SortOrder
From WellDocuments
Inner Join DocumentTypes on WellDocuments.DocumentTypeID = DocumentTypes.DocumentTypeID

That should give you the WellDocuments table along with the SortOrder field from DocumentTypes.

Is this not what you're trying to do?

CloudApps

ASKER

I need to use the modified WellDocuments table as the datasource for webpages that allows records to be added, edited and deleted. I did try using a view, but it was not updateable, unless I missed something in that regard.

Thanks,

ASKER CERTIFIED SOLUTION

John Gobert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CloudApps

ASKER

I was on the wrong track with the "Related" function.

I built a view with a subquery as the SortOrder. The view is now updatable.

SELECT WellID, WellDocumentID, DocumentTypeID, WellDocument,
(SELECT SortOrder
FROM dbo.DocumentTypes AS T2
WHERE (DocumentTypeID = T1.DocumentTypeID)) AS SortOrder
FROM dbo.WellDocuments AS T1

Thanks,