Why not then – tie your RightFax Phonebook right into your SharePoint contact data?
To begin, you will need to identify the “List” in which you want to query. From within SharePoint, your List has a “name” which you have given it. In my case, it was called “KCH Directory”. SharePoint has an “ID” which it uses to track this data, and we need to locate this string.
Get onto the SQL server which houses your SharePoint data, and run the SQL Query Analyzer. Point the query analyzer to the correct database. Mine was called “KCH_WSS_CONTENT”. Now, enter this SQL query …
select tp_ID from Lists where tp_Title = 'KCH Directory'… where ‘KCH Directory’ is the name of your list. Then click the green arrow to process the query. You should see one row returned, with the ID we are after. Right click on that ID and “copy” it. Now paste it to Notepad for later reference.
Next, we need to create an ODBC connection for your RightFax server to use. Open Administrative Tools > Data Sources (ODBC).
Click the System DSN tab, and click “Add …”. Scroll to the bottom of the list and choose SQL Server, then “Finish”. Next you will be given a wizard. Name your source whatever you want. I named mine “passql”. For server, enter the name of the SQL server which houses the SharePoint database, then click next. Change the authentication type if you need to (I didn’t) and click next. Make sure you stop here and “Change the default database to” – and enter the name of the database that has all of your SQL data in it. Then click next, change nothing, and click Finish. Finally, click Okay to leave this window.
Now right-click the RightFax system tray icon, and choose “ODBC Configuration”. Then click “Add”.
Now, name this phone book whatever you like and select our recently created ODBC connection from the ODBC source list.
Fill in your fields “appropriately”. You may need to do some mapping here. That means going back to your SQL Query Analyzer and performing a dump of your UserData table with a query like this …
select * from UserData where tp_ListId = 'YOUR-ID-STRING'In my case, I was able to match up fields in RightFax to these:
nvarchar1 – last name
nvarchar2 – first name
nvarchar17 – fax number
nvarchar11 - location
nvarchar15 - voice
nvarchar6 – company
So entering them into the RightFax ODBC window, looked like this:
Notice that we only have one field for a name, while SharePoint divides it into a first, last, and middle initial. I have combined first and last, and skipped middle initial (creating one solid RightFax field). All of this information will end up on your Fax Cover Sheet (FCS), which is nice.
The “WHERE” box is a SQL statement “where”. What we are saying here is what we want and don’t want from the UserData SQL dump. In my case I am specifying the particular List that I had in mind, minus the blank company names, and fax-less entries. I also (optionally) told it to only show me entries where the fax number started with a left parentheses “(“. That was because someone had typed textual information into the fax fields on some of my SharePoint data.
My WHERE looked like this (photo obscured) …
(tp_ListId = ‘YOUR-ID-STRING-HERE' and UserData.nvarchar17 is not null and UserData.nvarchar17 like '(%' and UserData.nvarchar6 is not null)The “ORDER BY” allows you to sort your entries. In my case, I just let it try to sort by last name. But this could have been Company Name, or even numerical Fax Number.
Click OK, and try it out! In my case, I had to do a lot of checking and double checking of my SQL syntax. One little typo and your Phonebook will show up stone empty (there will be no error messages).
To test it – open RightFax FaxUtil, and click the yellow address book symbol. You should have a new tab with the name that you used in the ODBC configuration (mine was passql). When you click the tab, the actual query will take place so there might be a slight delay. You should get a nice list of data, and a few boxes overtop to help you filter out what you were after.
Obviously the ODBC connection, and the RightFax ODBC configuration will need to be repeated for your clients. But there is a way to cheat this as well. You can export elements from the registry, combine them into one .reg file, and import it after running through the installation. You can find the Captaris KB article: here.
I certainly hope this is useful to someone else out there. Drop me a line in the comments and let me know! ;-)
