Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: instr filter Post Reply Post New Topic
Author Message
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Topic: instr filter
     Posted: 28 Jan 2011 at 3:44am

I need to filter a string that includes -E at the end of the number and ones that don't.  I've tried if then and instr, but I'm not doing it right.  I would like to have something like; if instr({field}, "-E") then "Express" else "Regular".  Then I can create a report that gives a sum of express and regular separately.  Anyone know the best way to do this?
customreport
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 28 Jan 2011 at 4:58am 

if InStr({field}, "-E") > 0 Then

   "Express"

else

   "Regular"


InStr returns the index (position in the string) of the first match, and so if it is not found, it will return 0.

Edited by Keikoku - 28 Jan 2011 at 5:01am
IP IP Logged
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Posted: 28 Jan 2011 at 5:06am
I inserted that formula and it came back a boolean is required here.
(if instr({v_txn_sales_order_line.doc_num_h}, "-E") >0 then "Express" else "Regular")
customreport
IP IP Logged
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Posted: 28 Jan 2011 at 5:18am
Even if I could do an is like filter to include numbers with -E at the end would work.  Basically I want to seperate sales orders that end with -E and ones that don't.
customreport
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 28 Jan 2011 at 6:04am
Another option is to explicitly check the last two characters of the string. 


if {field}[len({field})-1] & {field}[len({field})] = '-E' Then

   'Special'

else

   'regular'


Basically, concatenate the second-last char and the last char together and check if it is "-E"

It doesn't work if your field is only 1 character long though (or does not have anything), which requires you to do some error-checking if the data-input may result in such values.

I think the InStr is cleaner though. The code I provided works on my report.

Edited by Keikoku - 28 Jan 2011 at 6:05am
IP IP Logged
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Posted: 28 Jan 2011 at 6:25am

Same thing, says a boolean is required here when I put in that formula.  I'm entering it in in the record selection formula editor.  Here's all of my formulas;

not({v_lst_item.name} like ["Warranty - Service", "Subtotal", "REPAINT", "Remake-Warranty", "RE-MAKE-Shop error", "RE-MAKE-Shipping error", "RE-MAKE-Sales Error", "RE-MAKE-Freight Charges", "RE-MAKE-Engineer", "Packaging/Crating", "NOTES", "INSF", "Freight--Third Party", "Freight--Prepaid", "Freight--FA", "Freight--Collect", "CUSTSER", "CR-Shop Error", "CR-Sales Error", "CR-Paint Problem", "CR-Miscellaneous Clean UP", "CR-Freight Claim-Unpaid Portion", "CR-Freight Charges-Shipping", "CR-Freight Charges - Late Ship", "CR-Engineering Error", "CR-Discounts Taken", "CR-Bad Debt", "CANCEL", "988888"]) and
{v_txn_sales_order_line.transaction_date} in MonthToDate and
if {v_txn_sales_order_line.doc_num_h}[len({v_txn_sales_order_line.doc_num_h})-1] & {v_txn_sales_order_line.doc_num_h}[len({v_txn_sales_order_line.doc_num_h})]= '-E' then 'Express' else 'Regular'
customreport
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 28 Jan 2011 at 6:53am
Record selection tells crystal to only retrieve any records that satisfy the given condition.

What you want to achieve (labeling it 'express' or 'regular') can't be done (to my knowledge) using record selection (on a single report) and would be better to use a separate formula, where you would place that formula in the detail section as another column which you can summarize on.

I didn't think you would be placing that in record selection, so didn't specify that you should create a new formula for it.

Edited by Keikoku - 28 Jan 2011 at 6:54am
IP IP Logged
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Posted: 28 Jan 2011 at 7:12am
Thank you.  I thought that was the problem.  I used the first formula and added it to the details section.  It worked!  Now, how do I sum the totals for each regular and each express?
customreport
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 28 Jan 2011 at 7:41am
Create two running totals, one for Express and one for Regular.

Summary type = sum, and for evaluation write your own formula so that, for the Express total, it would be 


{@formula_name} = 'Express'


Do the same for 'Regular', then you can put it in the footer or header.
IP IP Logged
customreport
Newbie
Newbie


Joined: 26 Jan 2011
Location: United States
Online Status: Offline
Posts: 28
Quote customreport Replybullet Posted: 28 Jan 2011 at 8:24am
Thank you
customreport
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.