Print Page | Close Window

Need Help with Record Selection Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7284
Printed Date: 01 May 2024 at 6:34am


Topic: Need Help with Record Selection Formula
Posted By: JFKjr
Subject: Need Help with Record Selection Formula
Date Posted: 06 Aug 2009 at 8:40am
Hello Everyone,
 
I am trying to create a record selection formula which loops through all the selected values in the parameter list and checks whether the parameter values starts with "P-" string.
 
But, it seems the record selection does not support for loop.
 
//Not Working with for loop
 
NumberVar Limit := UBound({?Enter Vendor});
Numbervar Indx ;
 
For Indx := 1 To Limit Do
(
  local StringVar Txt := ToText({?Enter Vendor}[Indx]);
  if Txt startswith "P-" Then Txt := Mid (Txt, 3) ;
  {vendor.name} = Txt
);
 
//Working if I select a particular parameter value
 
if {?Enter Vendor}[1] startswith "P-" Then {vendor.name} = Mid ({?Enter Vendor}[1], 3)
 
Not sure how to check the string against all selected parameter values. Your help will be greatly appreciated. Thanks a million in advance.


Replies:
Posted By: gavsmith
Date Posted: 06 Aug 2009 at 8:55am
Not something I've tried but will it allow you to change the parameter values in the formula i.e.

NumberVar Limit := UBound({?Enter Vendor});
Numbervar Indx ;
 
For Indx := 1 To Limit Do
(
  local StringVar Txt := ToText({?Enter Vendor}[Indx]);
  if Txt startswith "P-" Then {?Enter Vendor}[Indx] := Mid (Txt, 3) ;
);
{vendor.name} in [{?Enter Vendor}]

Just an idea sorry if it doesn't help

Posted By: DBlank
Date Posted: 06 Aug 2009 at 8:59am

So you have a param that allows for multiple values to be selected and if a selection begins with "P" then you want it return when matching first 3 characters otherwise where it is an exact match?

If left({?Enter Vendor},1)="P" then left({?Enter Vendor},3)=left(vendor.name,3) else {?Enter Vendor}={vendor.name}
 
Place it in your select statement and it will run through the array of selected items in the param

Posted By: JFKjr
Date Posted: 06 Aug 2009 at 9:10am

Thanks for the reply, gavsmith.

Yes, I am able to change the parameter value and pass it to the database if I use a single parameter value in record selection formula editor. 

for ex:

//Working if I select a particular parameter value
if {?Enter Vendor}[1] startswith "P-" Then {vendor.name} = Mid ({?Enter Vendor}[1], 3)

But unable to loop through all the selected parameter values.
 
I have tested your code, it is generating the following error:
"The array must be subscripted"

Any suggestions?

Posted By: gavsmith
Date Posted: 06 Aug 2009 at 9:11am
Originally posted by DBlank

So you have a param that allows for multiple values to be selected and if a selection begins with "P" then you want it return when matching first 3 characters otherwise where it is an exact match?

If left({?Enter Vendor},1)="P" then left({?Enter Vendor},3)=left(vendor.name,3) else {?Enter Vendor}={vendor.name}
 
Place it in your select statement and it will run through the array of selected items in the param


This looks much easier... only I think it should be more like:

If left({?Enter Vendor},2)="P-" then Mid({?Enter Vendor},3)={vendor.name} else {?Enter Vendor}={vendor.name}

hope that helps

Posted By: JFKjr
Date Posted: 06 Aug 2009 at 9:21am
Hello DBlank, thanks for the reply.
 
I am newbie to crystal reports. Sorry, if I haven't followed your reply correctly.
 
I placed the following code in record selection formula and it is throwing "The array must be subscripted" error.
 
code:
 
If left({?Enter Vendor},2)="P-" then Mid({?Enter Vendor},3)={vendor.name} else {?Enter Vendor}={vendor.name
 
What do you mean by place it in select statement? How can I do this?
 
Sorry for your inconvenience. Your help will be greatly appreciated. Thanks.
 
 

Posted By: DBlank
Date Posted: 06 Aug 2009 at 9:43am
Sorry, my mistake. I have been testing this out and my suggested process is not working...
I could swear I have done that before without having to handle the array differently.
YOu could use a command to trim that data out and then create your param dynamically from teh command field and select from that.
MIght be easier but not sure about performance

Posted By: JFKjr
Date Posted: 06 Aug 2009 at 10:14am

Hello DBlank,

let me briefly explain my situation.

I want to allow users to select the vendor names that start with "P-" in the parameter screen.

But, when they click OK button, I would like to get the vendor data from the database without "P-" in the selected vendor names.
 
Any ideas on how to implement the above scenario?  Let me know if I confused you.
 
Thanks.

Posted By: DBlank
Date Posted: 06 Aug 2009 at 10:47am

Where are these values coming from? In the DB or did you just create a list of vendors and toss "P-" in front of the exsiting data?

If not how is that field linked to Vendors list in the DB structure?

Posted By: JFKjr
Date Posted: 06 Aug 2009 at 11:10am

FYI, I created a command and added "P-" in front of the existing data.

Anyhow, I solved the issue :). Here is the solution:
 
1. I created the following formula:
 
//GetVendors formula
 
NumberVar Limit := UBound({?Enter Vendor});
Numbervar Indx ;
StringVar Msg;
For Indx := 1 To Limit Do
(
  local StringVar Txt := ToText({?Enter Vendor}[Indx]);
 
  if Txt startswith "P-" Then Txt := Mid (Txt, 3) ;
  Msg := Msg + Txt + ","
);
Msg
 
2. Added the following code to record selection formula:
 
Stringvar array myVendor:=split( mailto:%7b@GetVendors - {@GetVendors },",");
{vendor.name} = myVendor
 
Boom! I am able to :
 
1. get the vendor data from the database for all the selected parameter values.
 
2. trim the vendor name without "P-" and pass it to the database.
 
Anyway, thanks for all your help :)
 

Posted By: DBlank
Date Posted: 06 Aug 2009 at 11:15am
Glad you got it working and thanks for posting the final viable solution.


Print Page | Close Window