Split part code field 1

[Andyherm]

Hi,
I'm writing an invoice in CRXI which pulls data from an excel spreadsheet (which is exported daily by an ISP) and arranges with address, company logo, order id etc.
I'm using the details section for individual part records which need to have part ID's and descriptions but the spreadsheet puts all individual part ID's into one field, each seperated with |.
This field can theoretically contain any number of part ID's split this way and the ISP has advised that this is the only format we can get the data in, how can I setup the report to split these into individual parts in the details section?
Many thanks,

 

[IdoMillet]

Split({Parts},"|") gets you an array.

You can check the number of elements in the array using UBound()

You can get each element in the array using Array where i is the element number in the array.

- Ido


view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Andyherm]

Thanks Ido,
I should explain that I'm fairly new to CR and to programming so I've been reading sample code on here and in CR help/BO support and I am now able to display seperate records in a static array (in this case 2 records) using these formula fields, how can I make a single field that displays each seperate part ID in the details section as it repeats?


@PartsSplit (in Group Header - OrderID)

Local StringVar Array PartsSplit;
Split({Sheet1_.Parts}, "|");
Ubound(PartsSplit);

@Part1

Stringvar a;
a := Split({Sheet1_.Parts}, "|") [1];

@Part2

Stringvar b;
b := Split({Sheet1_.Parts}, "|") [2];

 

[Andyherm]

Hi,
Does anyone know how to accomplish this?
I've now got a split array and can get a count of the elements in the array and can also get the individual part codes returned by single fields for each but as there can in theory be any number of elements in the array for each order, I need to get one field which repeats itself in the details section for each array element.
Can this be done?
I'm using these fields to get the results so far.

@PartsSplit (in Group Header - OrderID)

Local StringVar Array PartsSplit;
PartsSplit := Split({Sheet1_.Parts}, "|");
Ubound(PartsSplit);

@Part1 (in Details)

if {@partssplit} >0 then split({Sheet1_.Parts}, "|") [1];

@Part2 (in Details)

if {@partssplit} >=2 then split({Sheet1_.Parts}, "|") [2];

@Part3 (in Details)

if {@partssplit} >=3 then split({Sheet1_.Parts}, "|") [3];

Thanks,

 

[lbass]

It is unclear what results you are looking for. Why don't you show some sample data before splitting, and then show how you want it to display. It sounds like you want other fields to repeat with each array element?

-LB

 

[Andyherm]

Sorry, the data I'm working with apart from the address, phone num, company etc is in the format,
OrderID Part# PartDesc
001 A12|B18|G99 40w BUC|LNB|1/4" Feedhorn

And for the invoice I'm trying to get something along the lines of,

Company
Address1
Address2
Postcode

OrderID - 001

Part# PartDesc
A12 40w BUC
B18 LNB
G99 1/4" Feedhorn

I'm grouping by OrderID and have that field in the group header and I'm looking to have the Part# and PartDesc fields in the details section so if there were 6 orders, there'd only be 6 lines in the details section to keep space down on the invoice.
Currently the nearest I can get is to have the part1, part2, part3 etc fields I mentioned in my last post setup in the details section,
details - Part1 PartDesc1
details - Part2 PartDesc2
details - Part3 PartDesc3
While this works to a point, I can only really have a maximum of 20 lines on the report without making it cluttered and risking having orders which exceed 20 and don't work properly.
Can I get something like,
details - Part PartDesc
Which repeat for each element in the array so I can have the report grow to fit the order?
Thanks very much for assisance.

 

[lbass]

Here's where you lost me:

While this works to a point, I can only really have a maximum of 20 lines on the report without making it cluttered and risking having orders which exceed 20 and don't work properly.
Can I get something like,
details - Part PartDesc
Which repeat for each element in the array so I can have the report grow to fit the order?

All you can really do is set up multiple detail sections a,b,c,etc., each containing one array element, up to the maximum number of array elements. Then using conditional formulas, some sections will be blank if there are fewer elements. Format all detail sections to "suppress blank section". Then you will only have the sections that contain elements.

-LB