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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John GobertEnterprise Systems ConsultantCommented:
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.

John GobertEnterprise Systems ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.