ASPX templates
Version 5.x > ASPX templates > Filtering document type instances by listbox value View modes: 
User avatar
Member
Member
ovidiu.lazar-evozon - 10/18/2011 5:18:42 AM
   
Filtering document type instances by listbox value
Hi,

OK, I've posted this here because I haven't yet migrated from 5.5r2 to 6.0 (waiting for the upgrade package).

I have a list of dealerships where I search by means of the SearchHelper.Search method.

I then feed a basic repeater with the search results (works fine):

repSearchResults.DataSource = results;
repSearchResults.PagerForceNumberOfResults = numberOfResults;
repSearchResults.DataBind();

The repeater calls a transformation that gets the Dealerships fields (works fine):

[...] ><%# GetSearchValue("StreetNumber") %> <%# GetSearchValue("StreetName") %>, <%# GetSearchValue("City") %>, [...]

Now, there is an extra thing that I want to do. The Dealership doc type has a Listbox with some selected fields which are basically car manufacturers. When the user searches, he has the option to FILTER those searches by field.

So if i search for the dealership "A & B FORD SALES LTD." by entering "ford" in the search box, I get that dealership. But if I select "BMW" from teh combo next to the search field, I shouldn't get any results because A & B FORD SALES LTD. does not have BMW cars.

The only thing is that I can't do any further filtering on the search results dataset because I can't find the doc type fields. I couldn't get a working GetSearchValue method (the one used by the transformation) to find the values of those fields... I can't think of a way to pass this filtering directly to the Search method either.

Any ideas?

The search results returned contain only 8 columns of data but nothing related to the custom fields of the Dealership doc type, so I can't do any further filtering.

Thanks in advance.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 10/25/2011 5:55:39 AM
   
RE:Filtering document type instances by listbox value
Hi,

could you please check if the manufacturer field is configured as Searchable in the Site Manager - Development - Document types - <document_type> - Search fields.

Then you should be able filter values using syntax:

searchWord +manufacturerColumnCodeName:ford

You could add the + part to the searchFor parameter of the Search method.

Best regards,
Ivana Tomanickova

User avatar
Member
Member
ovidiu.lazar-evozon - 11/1/2011 8:39:56 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

I'm not sure this is going to work.

I took a closer look to the database, particularly at my custom_Dealership table. This table has a column named ManufacturerList of type nvarchar(300) where the list IDS are stored.

So, if a dealership has Ford, Acura and Bentley selected from a list of 10 car manufacturers, the ManufacturerList column of that row has values something like this: "1|3|7"

So what the CMS does is it concatenates the IDs of the selected listbox values and separates them using the "|" character.

Now, back to your suggestion: I don't see how this is going to work, I don't know what column name should I put instead of the manufacturerColumnCodeName...

My initial column name is ManufacturerList..

Also, here's my current search query. As you can see, I just add the manufacturer Acura like any other term but this isn't the way to go.

+classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])+(\"Acura\")

^^ This doesn't really work so I'm wondering how to tweak it based on your suggestion.

Instead of

+(\"Acura\")

should I have...

+(\"manufacturerColumnCodeName:Acura\")


or...

+(\"manufacturerListColumnCodeName:Acura\")

or...

+(\"manufacturerListColumnCodeName:Acura\")

or...

+(manufacturerListColumnCodeName:Acura)

?

I tried all of them and none work.

Again, I need to filter a document type Dealership by a Filed which is of type "multiple selection list box" and I need to filter by the selected values of that list.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 11/7/2011 8:20:17 AM
   
RE:Filtering document type instances by listbox value
Hi,

to sum up the issue.

You have a custom document type with a multiple check box where the user can select manufacturer (Ford, BMW, ...), values of this check box are stored in the format 1|2

Now you can use a smart search filter web part to filter manufacturers. It allows you to create a multiple check box so user can select one or more manufacturers.

The values of this filter should be defined in the format:
+manufacturerColumnCodeName;1;Ford
+manufacturerColumnCodeName;2;BMW

In other words values and names should be the same as you used in the field definition. You can use a query to fill values of filter automatically. Please find the documentation below.

Now to make it work you need to set manufacturerColumnCodeName field in definition of your documuent type in the Search fields tab as Searchable and Tokenized.

Another requirement to use in your smart smart search definition analyzer type: Standard

With above setting will be value 1|2 processed as 1 2 (pipe will be removed).

The result of this behavior is that if user selects BMW and Ford, manufacturers providing these cars will be selected. It will create a search text like:

searchWord +manufacturerColumnCodeName:1 +manufacturerColumnCodeName:2

The manufacturerColumnCodeName field contain indexed values (1 2), so that documents are found. The empty space is like OR.

I hope that description makes sense.

Here is related documentation:
Settings for particular object type
Smart search filters

Best regards,
Ivana Tomanickova


User avatar
Member
Member
ovidiu-e-cubed - 11/10/2011 8:42:38 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

I made the problematic field Tokenizable and I rebuilt the index. I tried what you said but it doesn't' seem to work.

Just to clarify:

I am not using a series of checkboxes for this. The user can select only one manufacturer from a combo like so:

http://tinypic.com/r/23ra8hw/5

I have a dealership object with a selected manufacturer:

http://tinypic.com/r/8wcx3d/5

This is my search function:

protected void Search()
{
if (StopProcessing) return;

// Get query strings
var dealersearch = QueryHelper.GetString("dealersearch", "").Trim();
var postalCode = QueryHelper.GetString("postalCode", "").Trim();
var searchMode = QueryHelper.GetString("searchMode", "");
var manufacturer = QueryHelper.GetString("manufacturer", "").Trim();
var numberOfResults = 0;

// Get current culture
var culture = CultureCode;
if (string.IsNullOrEmpty(culture))
{
culture = ValidationHelper.GetString(ViewState["CultureCode"], CMSContext.PreferredCultureCode);
}

// Get default culture
var defaultCulture = CultureHelper.GetDefaultCulture(CMSContext.CurrentSiteName);

// Prepare search text
var combinedSearch = SearchHelper.CombineSearchCondition(dealersearch, SearchCondition + FilterSearchCondition, CMS.ISearchEngine.SearchModeEnum.AnyWord, SearchOptions, DocumentTypes, culture, defaultCulture, CombineWithDefaultCulture);

if (!string.IsNullOrEmpty(postalCode))
{
combinedSearch += " +(\"" + postalCode + "\")";
}

if (!string.IsNullOrEmpty(manufacturer))
{
combinedSearch += "+manufacturerListColumnCodeName:" + manufacturer;
}

// Resolve path
var path = Path;
if (!string.IsNullOrEmpty(path))
{
path = CMSContext.ResolveCurrentPath(Path);
}

// Get positions and ranges for search method
int startPosition = 0;
int numberOfProceeded = 100;
int displayResults = 100;
if (pgrSearch.PageSize != 0 && pgrSearch.GroupSize != 0)
{
startPosition = (pgrSearch.CurrentPage - 1) * pgrSearch.PageSize;
numberOfProceeded = (((pgrSearch.CurrentPage / pgrSearch.GroupSize) + 1) * pgrSearch.PageSize * pgrSearch.GroupSize) + pgrSearch.PageSize;
displayResults = pgrSearch.PageSize;
}

// Combine regular search sort with filter sort
string srt = ValidationHelper.GetString(SearchSort, String.Empty).Trim();
string filterSrt = ValidationHelper.GetString(FilterSearchSort, String.Empty).Trim();

if (!String.IsNullOrEmpty(filterSrt))
{
if (!String.IsNullOrEmpty(srt))
{
srt += ", ";
}

srt += filterSrt;
}

// Search
DataSet results = SearchHelper.Search(combinedSearch, SearchHelper.GetSort(srt), path, DocumentTypes, culture, defaultCulture, CombineWithDefaultCulture, CheckPermissions, SearchInAttachments, Indexes, displayResults, startPosition, numberOfProceeded, (UserInfo)CMSContext.CurrentUser, out numberOfResults, AttachmentsWhere, AttachmentsOrderBy);

results = FilterByManufacturer(manufacturer, results);

// Fill repeater with results
repSearchResults.DataSource = results;
repSearchResults.PagerForceNumberOfResults = numberOfResults;
repSearchResults.DataBind();

// Show now results found ?
if (numberOfResults == 0)
{
lblNoResults.Text = NoResultsText;
lblNoResults.Visible = true;
}

// Invoke search completed event
if (OnSearchCompleted != null)
{
OnSearchCompleted(Visible);
}
}


The combined search variable looks like this:

" +classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])+manufacturerListColumnCodeName:1"


But the result dataset is null...

User avatar
Member
Member
ovidiu-e-cubed - 11/10/2011 8:46:13 AM
   
RE:Filtering document type instances by listbox value
Please ignore this line from the Search function, it's not useful and it may confuse you:

results = FilterByManufacturer(manufacturer, results);

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 11/21/2011 7:17:30 AM
   
RE:Filtering document type instances by listbox value
Hi,

I tested behavior with news documents.

1. I added a new field to this document type with code name listbox (allowed multiple selection) and defined its values to:
1;Audi
2;BMW
3;Skoda

In the Search fields tab checked Searchable and Tokenized.

Then I rebuild the index with a standard analyzer.

2. I created two documents/news A and B

A - News title: BMW and Audi, Listbox: selected options BMW and Audi
B - News title: BMW and Skoda, Listbox: selected options Audi and Skoda

3. I configured Search dialog, Search result and search filter web parts, so that if for examle Audi was checked in the filter only BMW and Audi document was found when I search for word Bmw.

The first attribute (searchText) in the Search method looks like this:

"+(bmw) +classname:[cms.news TO cms.news] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei]) +listbox:1"

It seems that in your condition is missing the first part of searchText, i.e. the search word.

Then you can try to do a quick test to make sure that your documents are indexed correctly. You can configure these three web parts as well. The filter values can be defined as following:

+listBox;1;audi
+listBox;2;BMW
+listBox;3;Skoda
+;;All


Best regards,
Ivana Tomanickova



User avatar
Member
Member
ovidiu-e-cubed - 11/24/2011 6:59:10 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

Two issues:

1. I shouldn't be required to have a search term. The manufacturers combo can be sued to filter all dealerships that have a certain manufacturer selected.

2. I don't know if this makes any difference but I'm using a datasource instead of an options list.

My select query looks like this:

SELECT * FROM [dbo].[custom_Manufacturer]

This returns the following:

ManufacturerID Name
--------------------------------
1 Acura
2 Audi
3 BMW

User avatar
Member
Member
ovidiu-e-cubed - 11/24/2011 7:46:59 AM
   
RE:Filtering document type instances by listbox value
I should also add that the ManufacturerList field from the dealerships table is of type nvarchar(300) , is nullable and has values such as these:

12
1
7
40
10|39
24
24
2

Notice the | separator on some rows.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 11/25/2011 6:36:40 AM
   
RE:Filtering document type instances by listbox value
Hi,

in case you need to select all cars from requested manufacturer the searchText should look like:

"+(yourField bmw) +classname:[cms.news TO cms.news] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])"

Using above query, all bmw cars will be selected.

If you need audi and bmw you can use:

"+(combobox audi bmw) +classname:[cms.news TO cms.news] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])"

To use above syntax you need have following key in your web.config file:
<add key="CMSSearchOnlyWhenContentPresent" value="false" />

This example works with settings described in the previous post.

Best regards,
Ivana Tomanickova





User avatar
Member
Member
ovidiu-e-cubed - 11/28/2011 6:33:07 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

Thanks, I finally managed to solve it. I made one small change to your solution - I had to put the actual manufacturer id in the search query (e.g. number 2 in the query below):

"+(combobox 2) +classname:[cms.news TO cms.news] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])"

Thanks again!

User avatar
Member
Member
ovidiu-e-cubed - 11/28/2011 9:20:25 AM
   
RE:Filtering document type instances by listbox value
Hi,

Back with another question, pertaining to the same search we discussed above.

I'm actually having a different challenge now. Let me explain.

I have the dealership doc type with fields such as:

- dealershipid
- dealershipname
- latitude
- longitude

The user enters a postal code and selects a distance. This distance represents the maximum distance form which the search results dealerships should be from the inputted postal code.

I'm using google maps to transform the code from postal to latitude/longitude and it works fine. What I don't know is how to pass the distance and the center lat/long to kentico.

Example:
User inputs the postal code "V2T 5M1". I use Google Maps to transform this into the coordinates 53.9204448 latitude and -122.7693646 longitude.
User also inputs 5km, meaning he wants to find all dealerships that are within a 5km radius form the original postal code.

This means I need to search the dealer doc type, make some calculations and filter out dealerships that are not within the 5km range.

In SQL, the query looks something like this:

declare @centerlat float
set @centerlat = 53.9204448

declare @centerlong float
set @centerlong = -122.7693646

SELECT DealershipName, Latitude, Longitude,
(3959 * acos( cos( radians(@centerlat) ) * cos( radians( Latitude ) )
* cos( radians( Longitude ) - radians(@centerlong) ) + sin( radians(@centerlat) )
* sin( radians( Latitude ) ) ) )
AS distance
from custom_Dealership


My question is: how do I specify this to the Kentico SearchHelper.Search method, inside the combinedSearch variable?

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 11/29/2011 3:45:55 AM
   
RE:Filtering document type instances by listbox value
Hi,

I think it would not be performance friendly to count distance for each record.

Instead you can count the longitude and latitude interval you search for.

For example if the selected place has latitude 54.51111 you can define interval +/- 0.5 and search for results with latitude <54.01111,55.01111>

Analogically, you can define longitude interval

in the Lucene syntax it is:
+doubleFieldCodeName:[(double)54.01111 TO (double)55.01111]

Best regards,
Ivana Tomanickova

User avatar
Member
Member
ovidiu-e-cubed - 12/5/2011 10:45:38 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

I tried to make this work today but to no avail. I think I'm missing something really obvious. I simplified things to make it easier.

Works if I don't search by latitude:

"+(ford) +classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei])"



Doesn't work if I add filtering by latitude:

"+(ford) +classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei]) +Latitude:(double)49.1590247"


The first search result form the first query has a latitude = 49,1590247. The latitude field is...

- of type float(null) in the db
- of type double in the CMS site manager
- searchable
- NOT marked as content
- NOT marked as tokenized

I also tried to put a comma instead of the dot (49,1590247 instead of 49.1590247) but that didn't work either.

I skipped the interval/radius search and I just want to search for a specific latitude for now. Any suggestions?

Thanks.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 12/12/2011 3:23:06 AM
   
RE:Filtering document type instances by listbox value
Hi,

this is an information for readers of this thread.

The smart search filter was not working with double numbers correctly. They was not transferred to the string in the correct form and filter did not return correct result.

It was fixed in the hotfix 6.0.9. After applying the hotfix following search syntax should work:


1. +field:[(double) 41.999997 TO (double) 41.999999]
2. +field:[(double)-41.999999 TO (double)-41.999997]
3. +field:[(double)-41.999999 TO (double) 41.999999]

Best regards,
Ivana Tomanickova

User avatar
Member
Member
ovidiu-e-cubed - 12/12/2011 6:27:18 AM
   
RE:Filtering document type instances by listbox value
Hi Ivana,

Was the hotfix released? I thought this was scheduled for tomorrow, Dec 13th.

User avatar
Member
Member
ovidiu-e-cubed - 12/12/2011 6:30:52 AM
   
RE:Filtering document type instances by listbox value
Can you post a link here when the hotfix is live, please? I'm sure others are waiting for this as well. Thanks!

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 12/12/2011 8:06:38 AM
   
RE:Filtering document type instances by listbox value
Hi,

it should be released tomorrow and will be downloadable here:
http://devnet.kentico.com/Bugtracker/Hotfixes.aspx

I am sorry for confusion. I just wanted to confirm readers that it was a bug and in which hotfix it is fixed.

Best regards,
Ivana Tomanickova

User avatar
Member
Member
ovidiu-e-cubed - 12/12/2011 8:15:21 AM
   
RE:Filtering document type instances by listbox value
Cool, thanks.

User avatar
Member
Member
ovidiu-e-cubed - 12/14/2011 6:20:35 AM
   
RE:Filtering document type instances by listbox value
Hi,

I applied the patch but I still can't solve my problem:

The following sql returns 1 row in Management Studio:

SELECT *
FROM [ncda6].[dbo].[custom_Dealership]
where Latitude = 49.1590247


and the following search query returns null:

+classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei]) +Latitude:(double)49.1590247


Note that I'm not sending a search term per se, I'm just searching by Latitude.

Any ideas?

User avatar
Member
Member
ovidiu-e-cubed - 12/14/2011 6:23:57 AM
   
RE:Filtering document type instances by listbox value
I tried searchign by interval but still no luck.

This returns 39 results when run in SQL Management Studio

SELECT *
FROM [ncda6].[dbo].[custom_Dealership]
where Latitude >= 48.9707534504982 and Latitude <= 49.1502705495018


While this returns null when passed to the SearchLeper.Search method:

" +classname:[custom.dealership TO custom.dealership] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei]) +Latitude:[(double) 48.9707534504982 TO (double) 49.1502705495018]"


Again, I applied the 6.0.9. patch.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 12/14/2011 7:27:56 AM
   
RE:Filtering document type instances by listbox value
Hi,

the good news is that for me it was working correctly so hopefully we will be able to debug your search.

I configured the filter webpart following way:

+longitude;[(double)48.9707534504982 TO (double)49.1502705495018];48.9707534504982 TO 49.1502705495018

The value of longitude field was set to 49.

I search for word consulting, which was a title of news document, where I added a custom field longitude (type double).

Then the seachText was following:
"+(consulting) +classname:[cms.news TO cms.news] +_culture:([en-us TO en-us] [invariantifieldivaluei TO invariantifieldivaluei]) +longitude:[10000000048.9707534505 TO 10000000049.1502705495] "

Please take a look at the numbers - they are in the normalized form - in your code they was not. Could you please test the search according to the above case?

Best regards,
Ivana Tomanickova

User avatar
Member
Member
ovidiu-e-cubed - 12/14/2011 6:46:29 AM
   
RE:Filtering document type instances by listbox value
I should also mention that I...

- rebuilt the entire solution
- rebuild the index
- restated the app form the Site Manager

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 12/14/2011 7:30:27 AM
   
RE:Filtering document type instances by listbox value
Hi,

please take a look at the answer above.

Best regards,
Ivana Tomanickova