Splitting Record in 3 groups 1

[PacinoQS]

Hello,
I am trying to make a pricelist in Crystal Reports.
In my report I want to show the article name - packaging - brand. Unfortunately I have all those information in 1 record so I need to find a way to split it.

At the moment my record looks like this:

<Item name> - <Item packacing> / <Brand>

I tried making a formula with the split function but it gives me the error that it cant result an array in the report.

Anybody has some advice for me?

 

[lbass]

I guess you mean they are all in the same field. You can reference the three different components by using formulas like these:

split({table.field}," - ")[1] //1st component

split(split({table.field}," - ")[2]," / ")[1] //2nd

split(split({table.field}," - ")[2]," / ")[2] //3rd

You could alternatively have used:

left({table.field}, instr({table.field}," - ")-1) //1st

extractstring({table.field}," - ", " / ")//2nd

mid({table.field}, instr({table.field}," / ")+4)

However, note that these results will still be in the same record (row). Your post title refers to three groups--and you haven't explained what you meant by that. You should probably show a sample of the results you want to achieve.

-LB

 

[PacinoQS]

Thanks for your help.

What I want to achieve is I want to split that field into 3 seperate fields. I have all the information in 1 field, but I want to split it so I can sort for example on brand name.

Lets say I have this record:

Toilet paper - 10x3pc - Lotus

Now I am making a pricelist but I want my pricelist to look like this:

Lotus (group sort)
Toilet paper 10x3pc

In order to do this I need to split that field. If that doesnt work I have to add an extra column in my database with brand and have to report it seperately but that is alot of work.

I hope you understand me

 

[lbass]

You can use either set of formulas in my first post. Insert a group on the formula for the third component.

-LB

 

[dgillz]

Showing us one record is a pretty poor way to help us help you.

Please show multiple records within the group "lotus" and also show multiple groups and how you want them to display.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

Example:
(how my report looks like now)
note: it only got 1 field/string wich is Items.Description

Toilet paper - 10x3pc / Lotus
Toilet paper - 5x6pc / Page
Dish paper 33cm - 5x3pc / Lotus
Dish paper 40cm - 8x3pc / Lotus

-I want to make a pricelist sorted on brand.
-I want to make a pricelist where the packacing is on the end of the page

Problem: everything is in 1 column

Now I can do 2 things:
1. I add 2 columns with packacing and brand, and insert all data. That way I know how to fix it but it is alot of work cause there are about 1500 articles and editing it takes a lot of time

2. I can split the field 3 times, in item name - packacing and brand. That way I can sort on Brand and I can put the packacing further on the report.

Desired Final result is this:

Page
Toilet paper 5x6pc

Lotus
Dish paper 33cm 5x3pc
Dish paper 40cm 8x3pc

Thanks in Advance

 

[dgillz]

OK split the field as LB suggested on his original reply, and place formulas 1 and 2 in the details section. Then group on the 3rd formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

When I inserted:

split({Items.Description}," - ")[1] //1st component
split(split({Items.Description}," - ")[2]," / ")[1] //2nd

in Record Selection in the Formula workshop it gives me this error:

The remaining text does not appear to be a formula

Is there something I am doing wrong?

 

[lbass]

These are two separate formulas. You should be creating three formulas, one for each component. Insert the group on the one identified as the third.

-LB

 

[dgillz]

Create 3 formula fields as LB suggested. Nothing you have posted indicates the need for a record selection formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

I created 3 seperate formulas in Formula Fields.
Then at Group Sort I added the 3rd formula.

Atm it gives me this error when i add formula nr 3 to group sort:

A subscript must be between 1 and the size of the array

I have no idea what I am doing wrong.

Atm I made a record selection of only 1 article where the - and the / are in that article so its not that there arent any records where the formula cant do its job.

Anyone?

 

[dgillz]

Why are you adding a formula to a group sort?

Just group by the third formula. In crystal XI click on Insert, summary,click on insert group button and browse to the 3rd formula field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

If I do that it gives me the same message.

I give all the details now:

this is the record I have :

uit/Grissini Fagolosi cipolla - GrissinBon

I have 3 formulas:
1=split({Items.Description},"/")[1] //1st component
2=split(split({Items.Description},"/")[2],"-")[1] //2nd
3=split(split({Items.Description},"/")[2],"-")[2] //3rd


I go to Insert -> Summary -> Insert Group -> 3

I am also using this record formula to show only the record with both the - and the / : {Items.ItemCode} = "132002"

Is it maybe because I need to refresh my data? Atm I am doing this with Saved Data..

 

[dgillz]

OK delete the group and place all 3 formula fields in the details section, and see if you are getting the same message. Also post your crystal version.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

If i put all 3 fields in Details section i get the right information x formula.

But if I try to group formula 3 it doesnt work and it gives me that error message. It also highlights this formula:

split(split({Items.Description},"/")[2],"-")[2]

I am using Crystal Reports v11

 

[dgillz]

What do you mean by information X formula?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[dgillz]

Also please uncheck saved data and refresh, then navigate to the bottom of the report report to make sure all data is read.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

Ok I am not at the office but tomorrow I try and let you know. Thanks for all the help so far.

 

[dgillz]

Why do you have to be in the office? Do you not have a remote connection to the data?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[PacinoQS]

There is the MS SQL server. Atm I am working with Saved Data.