Solved

Crystal Reports Looping Help

Posted on 2011-03-22
10
603 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
How to increase the row limit in Jasper Server.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

803 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