Crystal report - how to split a field into more fields

[Former Member]

Hello,

I`m new to Crystal reports and I`ve got a trouble. I have field which contains an address - street, city, zip code. The example is:

STEHLIKOVA 977 165 00 PRAHA 620 - SUCHDOL 165 00.

What I need to achieve is to split this string into three separated fields. I`ve trouhg a couple of forums but haven`t been able to find a proper answer. The problem is that the addresses differ so I can`t use an absolute defining of a start position. Looking at the DB (HEXA code) the parts in the string are divided by two dots:

STEHLIKOVA 977..165 00 PRAHA 620 - SUCHDOL..165 00

I`ve been able to work out this solution:

stringVar array x := split({cparty.STREET_ADD},"..");

Local numberVar i;

Local stringVar outputString := "";

For i:=1 to Count(x) do

(

outputString := outputString + x[i] + Chr(10)

);

outputString;

It splits the string into three rows:

STEHLIKOVA 977

165 00 PRAHA 620 - SUCHDOL

165 00

And I don`t know how to find the end of each row so to be able to separate the strings and report them as three different fields.

Would be anyone so kind and help me out with this?

Thank you.

Petr

[Former Member]

Hi Petr

If you can bulid your own query you should use data from database (like Eric suggested).

If you need to process address from document you should take a look at [xxx12] table - for instance Invoice - INV12.

For BP master data - CRD1.

Kind regards,

Radek

[Former Member]

Hello Radek,

Well I can`t do changes on DB level. I have to adjust data in Crystal report. What`s the example you`re referring to?

Thanks.

Petr

[Former Member]

Hi Petr

In these tables you can find atomized data, i. e. street, city and so on separately - you don't need to do any split in CR editor.

Kind regards,

Radek

[Former Member]

Hi Radek,

Well that`s the problem. I don`t have the data separately but as a whole string in one DB field. If I don`t use split I have to somehow tell Crystal to count characters from a specific point in the string. I`ve tried to use something like instr, mid (e.g. here Crystal Reports Extract 2 characters from string ) but it doesn`t work.

Petr

[Former Member]

Hi Radek,

This is not true anymore, when in the document you change the address: in which case , the only place where the addresses are correct are the fields Address and Address2.

Regards,

Eric

[Former Member]

Hi Petr,

As you stated and a general matteer of fact, addresses are one of the biggest nightmare in computing: no fixed lenght, no fixed component, 2 users are giong to write (even without mistakes the same address in fully different ways...

Back to B1: in a document, an address is stored in two places (Address - Address2 and 'DOC'12) and the only place which is filled whatever the user is doing is (are) Address - Address2.

The other one, is filled only if the address is taken from scratch from the BP or is modified in the extended address box.

In these fields, components are separated (by memory) with the 0x0D ASCII character and all components, from the address format (dépendant of the country) are in.

I guess you know all now

Regards,

Eric

[Former Member]

Hi Petr

I decided to check it and here is what I came up with.

I attached OINV table as a data source for my report. I got [Address2] field in page header. I created two formula fields in my report: one in page header section [table1] and second in page footer section [f1]. In [table1] formula I got:

global stringVar array x := split({OINV.Address2},Chr(13));
x[2]; //just to prove that split works ok

in [f1] I got:

WhilePrintingRecords;
stringVar array x;

x[1];

The result is that I have a second part of [Address2] placed in [table1] and the first part in [f1].

As far as I understood your question this is what you wanted to achieve. Maybe in your case it is necessary to adjust delimiter but you still should be able to get correct value in [f1]. There is another problem: how many [f(x)] do you need to create but I think that you can just prepare 10 of them and you'll be fine. I think that you also need to check if your field [f(x)] needs to be populated (i.e. if there is such index in [table1]) using something like this: (example for [f2]):

WhilePrintingRecords;
stringVar array x;

numbervar c;
c := count(x);
if 2 <= c then
x[c];

Hope this helps.

Kind regards,

Radek

[Former Member]

Hi Eric
Are you sure about it? Right now I have only v 9.0 and demo databases upgraded from 8.8x but I still have some values in INV12 and when I change something in BillTo I can see the changes reflected both in Address2 and in INV12.

Kind regards,

Radek

[Former Member]

Hi Radoslaw,

IT dépends on how you do the changes.

If you modify directly the Address - Address2 fields, changes are not reflected into the INV12 table (or B1 has changed dramatically and I haven't see it).

Regards,

Eric

Message was edited by: ERIC LAPOUGE

[Former Member]

Hi Eric

Nothing's changed and you are right - my bad. I completely overlooked the possibility of in-field editing.

Kind regards,

Radek

[Former Member]

Hi Radoslaw,

Not a big deal...

But to think about it reminds me that you can call SQL queries from Inside the report and query can be a stored procedure (so taking parameters, and strangely the DocEntry of the document ).

In this query (stored-procedure, you can check the INV12 table and if filled use it and if not, split the Address field directly in SQL

Regards,

Eric

[Former Member]

Hello Radek,

Well I`ve tried but it doesn`t work properly. So you`re saying I shouldn`t have any field in Details section? Why is there Chr(13) in your fist formula? This formula seems to work but when I used the second one I only get the same records (I get the street address instead of city and only one value for all the records - value for the last record in the table is shown).

Thank you.

Petr

[Former Member]

Hello Eric,

I still don`t understand some of your statements. Why do you refer to two places (address-address2) or doc12? We only have address in one DB field (please see the screenshot attached). Not sure about the abbreviations used - B1, BP?

Thank you.

Regards,

Petr

[Former Member]

Hi Petr,

I'm using too often abbreviations

B1: Business One

BP: Business Partner

In the BP master data, the address fields are separated, so you can only fill each component.

In all documents (i.e. A/R invoice):

• You have two addresses: delivery and invoice named Address and Address2.
• Addresses are stored in two places: INV12 for all of the parts of the address AND the fields named previously.

If you modify by hand, on the logistic tab the content of the address, then the INV12 table is not filled.

In fields Address and Address2, fields are joined using the address Template for the country of the address and the line separator used is (by memory) the ASCII value 13 (0x0D).

My assumption on the begining was that you wanted to split the components of the address for a marketing document.

Regards,

Eric

[Former Member]

Hi Petr

Chr(13) is a delimiter that I used. Take a look at Eric's from 28.07 9:52 AM. He says:

In fields Address and Address2, fields are joined using the address Template for the country of the address and the line separator used is (by memory) the ASCII value 13 (0x0D)

That's why I used carriage return as a delimiter.

As for details section - I don't know your exact requirement. My formula was built just as a proof of concept and you need to analyse it and adjust for your purpose. You may want to use similar formula in details section. If you'd like to present parts of address in rows of your report you probably don't need to put anything in header or footer. Instead you need to do split for every row, i.e. in details section. Then you'll have several fields in details: city, street and so on as in data table in a database. In each of these fields you will have a formula for split. Let's assume that your data always have street in a first place. Then when you'd like to show street in a field in row of your report you should use formula similar to this:

stringVar array x := split({OINV.Address2},Chr(13));
x[1]; 

The number in square brackets is a value for index of field in an array that you get as an outcome of split function. In the next formula field in your report, for example for city, you'll copy formula and adjust index. You need to experiment a little to learn if split working ok for every particular address that you can get from database.

That's a pity that you cannot change data from database. As Eric proposed earlier you should be able to create a procedure and prepare your data at the database level.

Kind regards,

Radek

[Former Member]

Hi Eric,

Ok, thanks for the explanation. It`s clearer now;-)

Well yeah I want to split an address into 3 separated fields for a purpose of a report for our business department.

Petr

[Former Member]

Hello Radek,

I`ve tried to play with it a bit and I still can`t get it working properly. The address field, in most cases, starts with street. So I`m able to show the street only. However, if I want to show only city or zip code which aren`t at the beginning of a string not sure how to achieve this. The problem is how to say to the field to start at a specific position and just use the part of the field which is from this start position to the end of this section. Would it be possible with your formula mentioned above?

Thank you.

Best regards,

Petr

[Former Member]

Hi Petr

Have you managed to successfully split your 'cparty.STREET_ADD' field? If so do you experience troubles with assigning different parts of the outcome of split to different formula fields? Or maybe something else?

Kind regards,

Radek

[Former Member]

Hi Radek,

Using this formula

stringVar array x := split({cparty.STREET_ADD},Chr(13));

Local numberVar i;

Local stringVar outputString := "";

For i:=1 to Count(x) do

(

outputString := outputString + x[i] + Chr(13)

);

outputString;

I`ve able to achieve this (one field in crystal report):

STEHLIKOVA 977

165 00 PRAHA 620 - SUCHDOL

165 00

But I`m facing another problem now - I`d like to separate the address above into 3 separated crystal reports fields, e.g.:

[street] - STEHLIKOVA 977

[city] - 165 00 PRAHA 620 - SUCHDOL

[zip code] - 165 00

Thank you.

Petr

[Former Member]

Hi

Actually using a for loop is not necessary here. All you need to do is to add several formula fields: one for street, one for city and one for zipcode. In @street field you add formula:

stringVar array x := split({cparty.STREET_ADD},Chr(13));

x[1];

Then you drag such formula field to details section of your report and watch preview to check if everything looks alright. Repeat for every formula field that you'd like see in your report.

In @city you add almost identical formula but you change index, i. e. instead of x[1] you need to use x[2]. Then for @zip x[3].

You may need to check if your address has all three parts - for example if you want to use formula in the second part of your address field you may need check first if there are at least two parts after split:

1. stringVar array := split({cparty.STREET_ADD},Chr(13));
2. numbervar c; 
3. c := count(x); 
4. if 2 <= c then 
5. x[c]; 

Var 'c' is used to store the number of elements in array after split. Then I'd like to check if the part (second) actually exists. So I try to check if number of part that I want to refer to is not bigger than the number of elements in array after split (here stored in var 'c').

Actually the code presented above is not enough since you have no guarantee that you'll always get address structured in the very same way. For example in demo PL database I have zip code and city in the same row after split with Chr(13) as delimiter.

You'll need to experiment or ask someone to prepare correctly structured data coming from B1 to your report.

Kind regards,

Radek

[Former Member]

Radek,

Thansk a lot. However, when I try to change the index for city or zip code I`m getting this failure "A subscript must be between 1 and the size of the array". Any idea?

Thanks.

Petr

[Former Member]

Hi Petr

I think that split function might not be working as expected. Such error indicates that there is nothing at index > 1, i. e. split function didn't return anything more then first row.

You need to check the count of array that you get from split and your delimiter.

Kind regards,

Radek

[edy_simon]

Hi Petr,

Just showing a simple way out.

First formula :

stringVar array x := split({cparty.STREET_ADD},Chr(13));

Second formula will show first line :

stringVar array x

x[0]

Third formula will show second line :

stringVar array x

x[1]

Fourth formula will show third line :

stringVar array x

x[2]

Above is just a very simplified version. You will need at least check for the length of array x first before showing the x[i].

Regards

Edy

[Former Member]

Hi Petr,

If the address is coming from B1's tables, so one of the fields Address ad Address2 separator (in the DB) is 0x0D. SO read the addresses fields from the DB and split with this characteer.

Regards,

Eric

[Former Member]

Hi Eric,

Not sure if I understand what you mean. Could you please be more specific or to show me an example? I didn`t come up with that code, I found it in one of the forums and just adjusted it to our needs.

Thank you.

Petr