Solved

Crystal Reports Looping Help

Posted on 2011-03-22
10
607 Views
Last Modified: 2013-11-18
I have a field that has a lot of information in it and I want to parse it out and show the information in a separate field.

For Example: This is what the field currently contains 1:SALAD KIT BROCCOLI|2:SC TRAY VEGGIE PLEASER|3:SC SALAD BROCCOLI BOWL|4:SC TRAY MEDITERRANEAN SNACKER TOTE|5:TAYLOR FARMS PASTA PRIMAVERA SNACK POT|6:TAYLOR FARMS PENNE ALFREDO SNACK POT|7:SALAD PASTA ITALIAN|

I Want 2 columns, if possible as follows:

1  SALAD KIT BROCCOLI
2  SC TRAY VEGGIE PLEASER
3   SC SALAD BROCCOLI BOWL
4   SC TRAY MEDITERRANEAN SNACKER TOTE
5   TAYLOR FARMS PASTA PRIMAVERA SNACK POT
6   TAYLOR FARMS PENNE ALFREDO SNACK POT
7   SALAD PASTA ITALIAN

If I can't get the number at the front, that is ok as long as the product is in its own field.  Not each of the fields that i am working with, will have exactly 7 items some may have 1 some may have 100.  

I currently have this, but it is only giving me the first product.

Numbervar index:=1;
stringvar array array1:= {@Product};  
stringvar tempstring1;



stringvar tempstring1 := split(array1[1],"|")[index];

Please help me I am new to the looping and arrays. :)

0
Comment
Question by:alisamerrill00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 35195188
Since it's delimited, a simpler way is this:

Replace({myfield},"|",chr(13))

then you'll have a text field with carriage returns, and you just have to format it with "Can Grow".
0
 

Author Comment

by:alisamerrill00
ID: 35195198
This won't put the products into their own however, it is just 1 big field still.  I need each of the products to be in their own fields. Does that make sense?
0
 

Author Comment

by:alisamerrill00
ID: 35195202
For example, I need the SALAD KIT BROCCOLI as it own field and then as another field
SC TRAY VEGGIE PLEASER, etc...
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 12

Expert Comment

by:JayConverse
ID: 35195238
Why do you need them as their own fields?  
0
 

Author Comment

by:alisamerrill00
ID: 35195278
Because I want to report on them.  They are a part of a bigger report. I work for a grocery store chain and this is for product recalls and we need to have a list of our stores with each of these products and see who has complied to each of the products in the product recall.  
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35195373
I don't think you can do this with Crystal.  Even if you split each occurrence of this field into variables, because they're different each time, then stringvar1 will contain "Apples" on the first record, and "Oranges" on the second.  So you can't summarize or sort by it.  

I'm familiar with recall compliance, I have a client that manufactures baby food ingredients, they rely on end-to-end data for FDA audits.  And I use Crystal for the final reports.  The user puts in an ingredient, and every batch where it was used and every customer who received that batch comes out on the reports.  However, the data parsing is done with code, Crystal has records that have been split into logical parts.

What you could do is make a variable, e.g., BroccoliCount that says:

If instr({myfield},"SALAD KIT BROCCOLI") <> 0 THEN 1 ELSE 0

and sum it, then you'll know who had that product and how many occurrences there were.
0
 

Author Comment

by:alisamerrill00
ID: 35195396
Can you show me how to do that so at least I  create that field and then do a subreport that has those items below the stores?
0
 
LVL 12

Accepted Solution

by:
JayConverse earned 250 total points
ID: 35195463
1. Make a formula called SearchBrococoli, with the contents "SALAD KIT BROCCOLI"
2. Make a subreport with no links
3. Edit the subreport, and add a parameter called SearchParam
4. Change the subreport record selection to:

Instr({myfield},{?SearchParam}) <> 0

5. Go back to the main report, and change the subreport link to connect {SearchBroccoli} to {?SearchParam}

Then you'll have a report of all stores that have that item.


0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 35195620
The SPLIT function will create an array with all the values in it.

You can address each array element with ArrayName[N]

Depending on the table arrangement, there is no need for a subreport.

Assuming you can link the tables so a record has the stroe name/id and the string in the same record you can use the InStr function above to select the records you want.

mlmcc
0
 

Author Comment

by:alisamerrill00
ID: 35195737
Thanks this helped me out a lot!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question