THE ISSUES And How to RETRIEVE THE USER PROFILE WE NEED
Previous Post
Welcome to the second part of my series! I want to talk about the issues I faced and my plan to fight against them.
First issue I should talk about is about the fields I was requiring. The goal was alway to filter on the Hire date and display the 10 most receant. The field is originally filled out in Active directory(AD) by AD. I had no idea what format it was. The issue was really behind the fact that when I created a datetime field in Shared Service Provider(SSP) it did not import the mapped property, which was a date time field of some type. After some trial and error, I surrendered and imported it as a text field. You're probably thinking, "Well, that's not a big deal, just convert the string to Date Time and sort on that!". And I like your thinking but you will see how that is not quite the solution.
Second issue that I will address is the formatting of the date time column. When the gridview renders the column it also adds the timestamp. We do not want that so I had to format that. Not a big deal but I should mention it becuase it's something I dealt with.
So let's just dive into it!!
First we need to get setup. We need to access the FullTextSqlQuery object. remeber this is a MOSS object so you need the following DLLs: Microsoft.Office.Server, Microsoft.Office.Server.Search
Lets also add some of the other dll's we will need now that we're at it, you will need: System.Data.Linq, System.Core(if you don't have that already)
Lets access the object:
So you see it's quite easy to create the object and give it a the site collection parameter by accessing the SPContext. I also gave the rowlimit to be 5000, I know that there are only 2000 users in my environment but you might want to specify another value if you're scenario has more than 5000 users. The reason I specified this field is because it defaults to some value, I think it might be 1000. I can't remember.
The next thing I want to talk about is the BuildQuery() method. It is just a method that returns the query string and it looks like this:
Now let me explain some things here. I wrote in my first post, that this query although it is like SQL it is NOT SQL. Just by looking at the SQL statement you will see that it is not SQL. It has an un-SQL statement in line 123. YOu will also notice that I'm not ordering by WhenCreated which is what we want. In SQL we would simply order by that and "Select Top 10". The reason why I did not do that is because the WhenCreated Field was the one that was not able to import from AD into SSP properly remember that? So to sort by datetime fields that were strings, would not really produce the results I was after. Some of you DB guys are thinking, well, no problem let's just add a Convert or Cast function to turn that string into Date time, and then we'll be able to sort by that field. Well, this is where the NOT SQL statement hits us over the head. Functions ARE NOT SUPPORTED BY FULLTEXTSQLQUERY.
You with me so far??
Let me explain one more thing before I move on, You see the fields I'm selecting by in line 122? well, these are the field names that are mapped in SSP. It's worth pointing out because when you create a new field property in SSP you also have to apply a Metadata Property Mapping. This is found in Shared Service Provider under search.
In the next screen you will see all your managed properties. Create a new one if needed and in this case mapp it to the PEOPLE properites. Just follow the wizard! The name you see in these Property name listing is the name of the field you add in the select statement. Not the field you see in AD or in SSP.
I just felt the need to tell you how to get that.
I will take a break and write How to Filter through the users we have now retrieved and how to data bind them!
Off to the next post!!
Next Post
Juan