CODEWITHSUNDEEP
lotus-noteslotus-formula
Jean
Jean

Reputation: 42

Trying to create a query for a web search in formula language in Lotus Notes - don't get what is expected

We have a web form that allows users to select the selection criteria from the dialog list for the search. The problem we are having is if the user selects a functionalArea that has a value with a dash, the search doesn't return the proper values.

As an example, the functionalArea selected may be "Engineering - Electrical" but the values returned are "Engineering - Manufacturing". Below is the detail for the form. Can anyone tell me how to resolve this issue?

Thank you in advance for your assistance and comments.

Jean

Detail below:

We have a web form that has three fields for user input.

locationInterest field - User can select a location value from the dialog list.

functionalArea field - User can select a function value from the dialog list. ref field - User can enter the reference number.

The user can enter a value in one of the fields for the query search, two values for a query search or three values for the query search. After they have selected the values, the user presses the Search button.

Here is the formula we have in place for the search button:

functionalAreaValue := @If(functionalArea = "Maintenance - Lift Truck"; "Service - Technicians"; functionalArea);
locationOnly :=  "FIELD location=" + locationInterest;
functionOnly := @If(functionalArea = "Maintenance - Lift Truck"; "FIELD functionalArea2=" + "Service - Technicians"; "FIELD  functionalArea=" + functionalArea);
positionOnly := "FIELD  ref=" + positionValue + "*";
locPosOnly := "FIELD  location="+ locationInterest + " AND  FIELD  ref="+positionValue +"*";
funPosOnly := @If(functionalArea = "Maintenance - Lift Truck"; "FIELD  functionalArea2=" + "Service - Technicians" + "  AND  FIELD  ref=" + positionValue + "*"; "FIELD functionalArea=" + functionalArea + "  AND  FIELD  ref=" + positionValue + "*");
funlocOnly := "FIELD  location=" + locationInterest + "  AND  FIELD  functionalArea=" + functionalAreaValue;
both := "FIELD  location=" + locationInterest + " AND  FIELD  functionalArea=" + functionalAreaValue + " AND  FIELD  ref=" +positionValue + "*" ;

Query := @If(
locationInterest != "" & functionalAreaValue = "---Please Select Functional Area---" & positionValue = "" ; locationOnly;
functionalAreaValue != "---Please Select Functional Area---" &  locationInterest = "---Please Select Location---" & positionValue = ""; functionOnly;
locationInterest ="---Please Select Location---" & functionalAreaValue = "---Please Select Functional Area---" & positionValue != "";positionOnly;
locationInterest !="---Please Select Location---" & functionalAreaValue = "---Please Select Functional Area---" & positionValue != "";locPosOnly;
locationInterest ="---Please Select Location---" & functionalAreaValue != "---Please Select Functional Area---" & positionValue != "";funPosOnly;
locationInterest !="---Please Select Location---" & functionalAreaValue != "---Please Select Functional Area---" & positionValue = "";funlocOnly;
both);

@URLOpen ("https://server.com/dbname/v.webopen?searchview&searchorder=4&query=" + Query)

Upvotes: 0

Views: 80

Answers (1)

Knut Herrmann
Knut Herrmann

Reputation: 30960

Don't search for 

FIELD functionalArea2=Engineering - Electrical

but for

FIELD functionalArea2="Engineering - Electrical"

You have to include the search string into quotation marks. Otherwise search would work for every word separately and "-" means NOT.

The first code version would look for documents which have word "Engineering" in field functionalArea2 but not the word "Electrical".

The second version (with quotation marks) returns only documents which have the whole string "Engineering - Electrical" in field functionalArea2.

Your formula would look like this then

... "FIELD functionalArea2=\"" + "Engineering - Electrical" + "\"" ...

or

... {FIELD functionalArea2="} + {Engineering - Electrical} + {"} ...

Upvotes: 1

Related Questions