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!

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

blocking items based on a word within the field.....

Status
Not open for further replies.
cmpgeekinGa

cmpgeekinGa

Technical User
Mar 15, 2004
13
US
I am sitting here with both my Crystal 8.5 and my SQL guides out and still can not find the answers I am looking for...

I know that if I want to block an item that starts with a certain phrase I can use the code
Code:
--------------------------------------------------------------------------------

not({item.itm_desc} startswith ["cement","interpore","collagraft","mesh"])
--------------------------------------------------------------------------------
... however, my problem is that the items that I want to block do not START the description... the words are within the description - just not at the beginning of it . I know it is still possible to block items based on those words, but I can not remember how to code the report to do so...

Also - just to make life a little more interesting - there is 1 item that begins with the word "mesh" that i DO want to show up on this report. is there coding that says something to the effect of "ignore all items beginning with "mesh" EXCEPT item # 54446" ?


Does anyone have any ideas? Any suggestions would be greatly appreciated...
[pc2]

I run Crystal 8.5 on an Oracle 8 database...
cmpgeek
 
Sort by date Sort by votes
Have you tried these?

mid(myfield,3,5) <> "abc"

or

not (myfield like %abc%)
 
Upvote 0 Downvote
when i try:
------------------------------------------------------------
not (["INTERPORE","CEMENT","COLLAGRAFT","MESH"] like {implant2.IMP2_TYPE})
------------------------------------------------------------
it tells me that a string is required between the [ and "INTERPORE"

when i try:
------------------------------------------------------------
not (["INTERPORE","CEMENT","COLLAGRAFT","MESH"] in {implant2.IMP2_TYPE})
------------------------------------------------------------
it tells me that the array needs to be subscripted...


I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
i just wanted to let yall know someone gave me the following suggestion in regard to the second question and it worked great:
Code:
--------------------------------------------------------------------------------

If {Item_no}="54446" then {item.itm_desc}={item.itm_desc} elsenot({item.itm_desc}startswith "mesh")
--------------------------------------------------------------------------------


i am still searching for the first answer, but at least the second one has been put to rest...

thanks



I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
It's not clear whether you want to use the formula for record selection or field or record suppression. For suppression, you could use a formula like the following either for the field or for detail section suppression:

instr({item.itm_desc},"cement") > 0 or
instr({item.itm_desc},"interpore") > 0 or
instr({item.itm_desc},"collagraft") > 0 or
(instr({item.itm_desc},"mesh") > 0 and
{item.item#} <> "54446")

For a record selection, you could wrap the whole thing in a not clause, as in:

not(
instr({item.itm_desc},"cement") > 0 or
instr({item.itm_desc},"interpore") > 0 or
instr({item.itm_desc},"collagraft") > 0 or
(instr({item.itm_desc},"mesh") > 0 and
{item.item#} <> "54446")
)

-LB
 
Upvote 0 Downvote
i want to use it for record selection... what does "instr" stand for? i just want to make sure i understand what it says and how it makes the report flow... if that makes sense...

I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
i want to use it for record selection... what does "instr" stand for? i just want to make sure i understand what it says and how it makes the report flow... if that makes sense...

thanks for your help!

I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
Instr" is the instring function which returns a number representing the position of the searched-for string within the field. If the result is greater than 0, then the searched-for string is in the field. The function works like:

instr({stringtobesearched},"searchedforstring") = position of searchedforstring within stringtobesearched (number)

-LB
 
Upvote 0 Downvote
You need to use the Like operator, try this:

not({item.itm_desc} like ["*cement*","*interpore*","*collagraft*","*mesh*"])

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.
 
Upvote 0 Downvote
Reebo99, i have already tried the LIKE statement - it tells me that there is a string required between "[" and "INTERPORE" ...

lbass - thank you so much for explaining that... i can understand how that works now... i dont know about anyone else, but the way i see it is that having someone tell me what code to use is great, but having someone explain to me the logic behind it and how it works is just as important if not more so... otherwise i dont really learn anything - i know it works, and can use it again if the exact same situation arises; but ourside of that situation the konwledge would be useless to me...

thanks again!


I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
unfortunately, lb, that code did not work... but i still appreciate your taking the time to explain it :)

I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
cmpgeek,

Previously, you tried :
not (["INTERPORE","CEMENT","COLLAGRAFT","MESH"] like {implant2.IMP2_TYPE})

Not

not ({item.itm_desc} like ["*cement*", "*interpore*", "*collagraft*", "*mesh*"])

I've double checked the syntax with a test report I have and it worked fine...




Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.
 
Upvote 0 Downvote
i am sorry Reebo, i thought not(A like B) and not(B like A) would give you the same result...

this is my record selection code as of now:
Code: 
{case_.CAS_DATE} in {?DateRange} and
( {case_.CAS_SITE} = "OR"  ) and
{ITMCAT.ITM_PROCAT} in ["3130","3155"] and
not ({implant2.IMP2_TYPE} like ["INTERPORE","CEMENT","COLLAGRAFT","MESH"] )  and
If {implant2.IMP2_ITMCODE} ="54446" then {implant2.IMP2_TYPE} = {implant2.IMP2_TYPE} else
not({implant2.IMP2_TYPE} startswith "mesh")

i am seeing the mesh item i want, but i am also still seeing the items i do not want...

I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
YOu missed off the "*" :

{case_.CAS_DATE} in {?DateRange} and
( {case_.CAS_SITE} = "OR" ) and
{ITMCAT.ITM_PROCAT} in ["3130","3155"] and
not ({implant2.IMP2_TYPE} like ["*INTERPORE*","*CEMENT*","*COLLAGRAFT*","*MESH*"] ) and
If {implant2.IMP2_ITMCODE} ="54446" then {implant2.IMP2_TYPE} = {implant2.IMP2_TYPE} else
not({implant2.IMP2_TYPE} startswith "mesh")

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.
 
Upvote 0 Downvote
i went back in and placed the *s... when i refreshed the report it had taken the mesh i wanted away and left the interpore...


I run Crystal 8.5 on an Oracle 8 database...
[pc2] cmpgeek
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Corinne30
  • Question
Running total based on beginning parameter value
Replies
0
Views
54
Corinne30
Corinne30
Corinne30
  • Question
Running total based on beginning parameter value
Replies
0
Views
57
Corinne30
Corinne30
B
  • Locked
  • Question
Difference of a field between 2 variable dates
Replies
1
Views
222
hilfy
hilfy
socalvelo
  • Locked
  • Question
Extract Phone Numbers from Text Field
Replies
0
Views
255
socalvelo
socalvelo
J
  • Locked
  • Question
New page before when a detail section last on page.
Replies
1
Views
432
hilfy
hilfy

Part and Inventory Search

Sponsor

Back
Top