Solved

Crystal Reports Looping Help

Posted on 2011-03-22
10
602 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
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.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now