instr filter

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: customreport — 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?

Posted By: Keikoku — 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

Posted By: customreport — 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")

Posted By: customreport — 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.

Posted By: Keikoku — 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

Posted By: customreport — 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'

Posted By: Keikoku — 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

Posted By: customreport — 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?

Posted By: Keikoku — 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.

Posted By: customreport — 28 Jan 2011 at 8:24am

Thank you