Solved

Crystal Reports Looping Help

Posted on 2011-03-22
10
605 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
Industry Leaders: 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!

 
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 100

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

735 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