This was one of those “I can’t believe it works this way” moments in SharePoint; if you’ve spent anytime architecting or building applications on SP, you know the feeling I’m talking about.
I had a simple scenario of wanting to allow users to easily link items in a List to a file in a Document Library; however, rather than requiring they copy-paste the URL to the file (manually or otherwise) within the List item, I wanted to use a Lookup. Worked great at the UI layer for end users; however, as soon as I tried to build a display that leveraged the JOIN ability within the CorasWorks API to pull the file URL based on the Lookup value, I hit a wall – any & all fields that contained some or all of the file name or path cannot be queried via the SPQuery.Joins property. I tried them all, including the most obscure of internal system fields like “LinkFilename2″ but none worked.
A quick search of the web revealed I was not alone; even worse, all the solutions I could find required custom workflows or even custom code and all followed the same pattern – create some event handler to grab the URL of a file after upload & write it to another field. Sure it would work, but getting any custom code installed on this client’s server is not easy and requires a multi-week CCB process. All that just to get the URL of an item I already have a Lookup relationship for?!
Then I had an idea: I knew our CorasWorks Actions provided direct and easy access to any field on a List or Library item, including all hidden & internal system fields. So I took the equally direct and easy approach to solving the problem – I created a Set File URL Action (a CorasWorks Modify Item Action) that wrote the “Server Relative URL” for a document into a field I can access through the JOIN on my query.
I then created a List Activation that automatically runs said Action every time a file is added to the Document Library and I’m set – no custom coded event handlers or workflows to have to write & deploy. Another streamlined solution thanks to CorasWorks!