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.

Who is Participating?
jpgobertConnect With a Mentor Commented:
What type of control interface are you using?  Are you doing some type of grid view that should accept updates?  Are you handling the potential updates on a one by one basis or are you doing mass updates?

I typically use stored procedures for stuff like this but I'd need more info on how you're structuring this and how the updates and changes should be handled.
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?
CloudAppsOwnerAuthor Commented:
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.

CloudAppsOwnerAuthor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.