The dbo.tabs.IconFile field issue

The case is fairly simple: In a DNN installation, we needed the user to use the IconFile field (i.e. the "Icon" field in the Advanced Settings section of the Page Properties page to add a custom icon to each page they made. We would then use a query created in Bi4Ce's ListX module to present a list of pages in the form of an in-page navigation menu for a specific portion of the portal.

To our horror, we discovered (using DNN version 4.8.2), that the IconFile field in the Tabs table takes two types of values: Either a value like "FileID=xxx", where xxx is the id of a record in the Files table, or the full path of the image file (Portals/0 is implied, so the file path is starting with the next sub folder).

That is, if you've set the icon to be in portals/0/myimages/myimage.jpg, you either get an entry in the Tabs table like "FileId=2312", or an entry like "myimages/myimage.jpg". At this point we are not sure why this is happening, but the truth is we don't care. We just want the path to the actual file either way.

In the case of the fileid thing, you can get the path by joining the tabs table with the files table and then the files table with the folders table (the folder path itself is in the folders table) and voila. In the case where the full path exists in the IconFile field, you obviously don't need to do anything else.

We created a query which decides on what to do and produces a result set with two fields: tabid and img where img ALWAYS contains the full path to the image file (provided that one is set for the page), regardless of whether it's in the form "FileID=xxx" or in the full path form, and I'd like to share it with you.

You can easily make a view out of it and use it where appropriate. It hasn't been tested on multi-portal installations, maybe things will differ a bit there. Comments are always welcome. Here goes:

select 
 tabid
 , case 
  when ltrim(rtrim(iconfilepath))='' 
  then folderpath+filename
  else iconfilepath
  end 
 as img

from 
(
select 

   tabid
 , taborder
 , case
  when patindex ('FileID=%', iconfile) >0
  then convert(int, replace(iconfile, 'fileid=', '')) 
  else 0
  end 
 as iconfileid
 , case
  when patindex ('FileID=%', iconfile) =0
  then iconfile
  else ''
  end 
 as iconfilepath
 
from 
 tabs 
where 
 isdeleted=0 


) mytabs

left outer join files
on files.fileid = iconfileid
left outer join folders
on files.folderid = folders.folderid

order by 
 taborder asc