i have the following query which has worked fine up until now. What it does is look for a path in the
[Membership Plan] field and then it does the reverse substring thing to append it onto the domain therefore making it a URL link rather than a folder link e.g. c:\test\test.pdf.
As I say it works fine BUT I have been informed that people are getting the NoData.pdf displayed because multiple groups from the same organisation do not have a [Membership Plan] link in the table, what they have is an entry into the [group Doc.Holder No] which is the No_ of the parent record. This is how it works in the internal system. So what I need adding to the query is that if there is a [group Doc.Holder No] entry in the record use that value as the No_ for the main record and use that path!! PHEW!
So to recap a bit mkore simplistic you may have 'Company A' with a [Membership Plan] field with folders\companyA_info.pdf and then all related comanies might be called CompanyA_logistics, ComapnyA_Communications etc.. but will have no entry in [Membership Plan] but will have an entry in [group Doc.Holder No] which is the primary key for the mother company e.g. 'Company A' so if that is the case, just use 'Company A' value for [Membership Plan]
does this make sense????
SELECT No_ , Case When CHARINDEX('\', [Membership Plan]) > 0 Then REVERSE(SUBSTRING(REVERSE([Membership Plan]), 1, CHARINDEX('\', REVERSE([Membership Plan]))-1)) Else 'NoData.pdf' End AS [Membership Plan] FROM dbo.[Healthshield$Customer] WHERE No_ = ?"