Multiple Text Search on Memo Field

[h20vrrq]

Hi,

I've been asked to write a report that looks in a memo field called {t_Invoice_Items.Description} and searches for any specified text string.

The aim of the report is for the user to type in any number of different words/phrases they want to search the description field for, they will also specify a start and end date that will reference the ItemDate field.

How would I go about creating a multi-text search formula?

I'm using CRXI.

Any help is very much appreciated.

 

[Madawc]

Try either LIKE orIN. e.g.

{address.town} LIKE "Alice"

or

"Alice" IN {address.town}

Either command would give you "Alice Springs" and also "Alicegruf", if there was such a place. LIKE also allows fancy selections, 'wild cards'.

You can develop the formulas by placing them next to the data and confirming you get the expected output - they will say 'True' or 'False', or be blank if they encounter a null.

You can then do a wider test referring to them by name, e.g. if Alice1 or Alice2 then ...



Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

What is the goal of the search--just to return that particular record if at least one of the text items is found in the field?

-LB

 

[h20vrrq]

Yes, the report needs to return the record(s) where at least one of the text items is found in the field. It should not return any records that do not contain any of the text items.

 

[lbass]

I think you would have to set this up like this:
(
(
ubound({?Parm}) >= 1 and
{table.memo} like "*"+{?Parm}[1]+"*"
) or
(
ubound({?Parm}) >= 2 and
{table.memo} like "*"+{?Parm}[2]+"*"
) or
(
ubound({?Parm}) >= 3 and
{table.memo} like "*"+{?Parm}[3]+"*" //add clauses up to N
)
)and
{table.itemdate} >= {?Start} and
{table.Itemdate} < {?End} + 1

You can add as many clauses as you like, but then in the prompt text you should say: Enter up to N words/phrases, since the formula will only be written for a maximum number of clauses.

-LB