Advanced Search Capabilities

pbrad · Post by **pbrad** » Wed May 02, 2007 11:13 pm

Hi,
I have a scenario very similar to the 'Ordering' example where the user needs to search for inventory items. I have defined a non-persistant form that has a text field for entering in a part number and several reference fields which provide dropdown lists for such things as 'Category' and 'Vendor'.

The issue that I am having is that if I had just used the default search form that is created when you define the query parameters as 'Ask At Runtime', each text field is optional and the form acts as a filter. Whatever you enter into the field limits the query return, if you leave them all blank you return all products. However, in the custom form with dropdowns, leaving a field blank such as the 'part number' field returns a 'Null' result.

Is there a way to have a form with dropdown lists where any or all fields can be left blank and the query still has a non-null return?
Thanks,
Pete

aware_support2 · Post by **aware_support2** » Thu May 03, 2007 11:14 am

Hi Pete,

To exclude empty values in attributes of the search object from the search, you can define a query like this:

FIND AnObject WHERE (AnObject.Attr1 = SearchObject.Attr1 OR SearchObject.Attr1 IS UNDEFINED) AND (AnObject.Attr2 = SearchObject.Attr2 OR SearchObject.Attr2 IS UNDEFINED) AND ...

pbrad · Post by **pbrad** » Fri May 04, 2007 1:29 am

Hi,
Could I please impose upon you to double check your syntax and confirm that it definitely should work. I have been unsuccessful in obtaining anything other than a null result.

I could be wrong but it seems to me that "FIND AnObject WHERE SearchObject.Attr1 IS UNDEFINED" is a problem because it will always return a null result.
Thanks for your help,
Pete

pbrad · Post by **pbrad** » Fri May 04, 2007 11:42 am

Hi,
Further to my post of last night, I am still unsuccessful. I am using the following modified query paramter to keep it simple:

FIND InventoryItem WHERE InventoryItem.ItemCategory='ROUTER BITS' AND (InventoryItem.PartNumber=ProductSearch.PartNumber OR ProductSearch.PartNumber IS UNDEFINED)

When I enter an existing part number within the 'Router Bits' category, it is returned in the presentation. If I leave the PartNumber blank, I am presented with a standard query return with nothing in it.

Any ideas?
Pete

aware_support2 · Post by **aware_support2** » Fri May 04, 2007 2:27 pm

Hi Pete,

It turns out that "IS UNDEFINED" cannot be used in queries for attributes of objects other than the one being searched. In other words, "ProductSearch.PartNumber IS UNDEFINED" does not work in "FIND InventoryItem".

The task can be accomplished with a little workaround:

1) Add attribute PartNumberExclude of type Yes/No to object ProductSearch. Make sure the attribute is not shown on the ProductSearch form.

2) Add the following rule to ProductSearch:

If ProductSearch.PartNumber IS UNDEFINED Then ProductSearch.PartNumberExclude = 'Yes'
Else ProductSearch.PartNumberExclude = 'No'

3) Change your query like this:

FIND InventoryItem WHERE InventoryItem.ItemCategory='ROUTER BITS' AND
(InventoryItem.PartNumber=ProductSearch.PartNumber OR ProductSearch.PartNumberExclude = 'Yes')

pbrad · Post by **pbrad** » Fri May 04, 2007 5:58 pm

Fantastic, it works like a charm!
Thanks, as always.
Pete

pbrad · Post by **pbrad** » Fri May 04, 2007 9:45 pm

Hi,
One last problem now. On the first page of the presentation everything is fine and the navigation arrows show at the bottom of the screen: "Showing items 1 to 99 of 802 " however when you click on any of the arrows for next or last page you get: "Showing items 100 to 0 of 0" or "Showing items 793 to 0 of 0" and the presentation is blank.
Any ideas?
Pete

p.s. Are there any custom commands that I can apply to processes attached to graphical buttons placed at the top of each page that serve the same functions as the bottom navigation block?

pbrad · Post by **pbrad** » Sun May 06, 2007 12:18 pm

Hi,
I am afraid that I am stumped on this one. When I use your suggested method above the query does return the right records but the functionality of both the standard query and custom presentation based on the query don't work properly.

The Standard query returns the records but the print icon at the top of the screen only returns the column headings with none of the data. The same is true of the CSV Export.

The Presentation also returns the right records but the navigational buttons don't work (as per my explanation above).

I was wondering if there was another way to go about this? Is there some kind of wildcard character that I could set as the value of the ProductSearch fields if they are undefined that the query will treat as a FIND ALL for that attribute?

Thanks for your help,
Pete

pbrad · Post by **pbrad** » Sun May 06, 2007 12:20 pm

Sorry, I meant to include a copy of the actual conditions that I am using in the query:

FIND InventoryItemMaster WHERE (InventoryItemMaster.PartNumber CONTAINS ProductSearch.PartNumber OR ProductSearch.PartNumberExclude='Yes') AND (InventoryItemMaster.InventoryCategory=ProductSearch.InventoryCategory OR ProductSearch.InventoryCategoryExclude='Yes') AND (InventoryItemMaster.Vendor=ProductSearch.Vendor OR ProductSearch.VendorExclude='Yes') AND (InventoryItemMaster.Description CONTAINS ProductSearch.Description OR ProductSearch.DescriptionExclude='Yes') AND (InventoryItemMaster.Brand CONTAINS ProductSearch.Brand OR ProductSearch.BrandExclude='Yes')

pbrad · Post by **pbrad** » Wed May 09, 2007 8:56 pm

Any ideas? I am still stuck on this one.
Pete

aware_support · Post by **aware_support** » Thu May 10, 2007 12:37 am

We will come back to you on this one - we need to investigate.

aware_support · Post by **aware_support** » Mon May 14, 2007 11:21 am

This is now fixed in build 966

pbrad · Post by **pbrad** » Mon May 14, 2007 4:41 pm

Hi,
Thank you very much for all of your work in solving this problem. I have fully tested the new patch and it is working perfectly.

In a small attempt to give back to the community, I have uploaded a sample application to my server that shows the functionality of this new functionality.

You can access it at: http://www.iportcentral.com
Just login using the following info with the form on the home page:
iPort Name: test
User: testuser
Pass: testuser

You can check out the functionality of the form and if you wish to see under the hood, you can download a copy of the bsv file by clicking on the download menu item.

Thanks again!
Pete