Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INSTR, PARAMETER QUESTION 2

Status
Not open for further replies.

Kim296

MIS
Aug 24, 2012
98
US
My problem is trifold. Hopefully this will make sense. Thanks for your time in advance.

I want to search for a key word in my notes field using a parameter (my goal).

My notes field {inmain.notes}is a memo not a string. So, I used a formula {@NOTES} to convert the memo field into a string field. The new formula field works on my report.

So now (i think), I can use the select expert to filter my new notes section (string) using my parameter (?SEARCH). This should filter out and search for words in my notes section. - THIS DOES NOT WORK.

So I back up, I try fixing an INSTR formula to filter out as True or False:
IF INSTR({@NOTES},{?SEARCH})>0 THEN TRUE ELSE FALSE - THIS DOES NOT WORK
IF INSTR({inmain.notes},{?SEARCH})>0 THEN TRUE ELSE FALSE - THIS DOES NOT WORK

If my delima makes sense to you and you know how I can search-a-word in my notes field, can you please help?

Thank You.
 
Kim,

I *think*, though can't say for certain, but if you are looking to limit your report to those Records where your new {@NOTES} formula contains the sought string, you should only need to add the following to the selection criteria:

Code:
[green](the rest of your criteria here)[/green] [blue]AND
INSTR[/blue]({@NOTES},{?SEARCH})>0

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Thanks Mike,

Okay, I figured out how to search the top word in my multiple search parameter:

IF INSTR({@NOTES},{?SEARCH}[1])>0 THEN TRUE ELSE FALSE - This works perfectly searching the first word.

BUT

If I try to add to my formula to make it check the following search words (mutiple value parameter) as follows

IF INSTR({@NOTES},{?SEARCH}[1])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[2])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[3])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[4])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[5])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[6])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[7])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[8])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[9])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[10])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[11])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[12])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[13])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[14])>0 THEN TRUE ELSE IF
INSTR ({@NOTES},{?SEARCH}[15])>0 THEN TRUE ELSE FALSE

It says that there are no errors in my formula, but when I try to update or change the search words it prompts the following error:

"A Subscript must be between 1 and the size of the array."

Do you know how I can fix this new error?

Thanks,
Kim
 
Kim,

I am afraid I haven't used Array's much, and don't know specifically what the fix is. By memory on seeing posts on TT about it, you need to "loop" through the Array conditionally based on the length of the Array.

Beyond that, I am afraid I am not too much help.

Hopefully this points you in the right direction. [smile]

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Kim,

I knew it was recently I read an example, I found it - posted 2 days ago by user briangriffin. This example is used for a rather different result, but I beleive could be modified to suit your needs. I have not dealt with multiple-selection parameters before, so not entirely sure how to create the array... but the rest should be a matter of swapping out what this example does, with what you are intenting.

thread767-1713951

Hope this helps,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Thank you Mike,

I appreciate you helping me figure out a solution. I did figure out a way to accomplish what I was looking for (but probably in a ROUND THE WORLD WAY).

I was able to give 15 default values (the same) then just insert different search words as needed, so that my above formula would work. Probably not the ideal way of doing it.

I will look at the thread you posted and HOPEFULLY be able to learn a better way of doing this.

Thanks,
Kim
 
A multi value parameter is really already an array - this formula will return a value that is the number of parameter values that appear in the Notes field:


whileprintingrecords;
numbervar x := 1;
numbervar y := 0;

while x <= ubound({?Search Term})

do
(if instr({@NOTES},{?Search Term}[x],1) > 0
then y := y + 1;
x := x + 1);

y

So your select expert would look for any record where the value of this formula is > 0.

Hats off to Mike's short term memory, since I didn't make the connection between the threads.
 
Thanks briangriffin!
Have yourself a TT
tipmasterstar.png
!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top