Array Problem

[Former Member]

Post Author: simon82

CA Forum: Formula

Hello,

I am currently having a problem splitting a field into other "fields"

i have been using the split function so

split() [1] - This is ok up to this point Split () [2] - This is ok but falls over if there is not 2 elements.

Some names have 1 element, some may have several.

how can i write a formula to extract all the elements using " " as the delimeter?

Thanks

[Former Member]

Post Author: Sateluco

CA Forum: Formula

Kal, good morning. Hopefully you are still available in the forum.

I have a field also that want to split and was able to get up to 2 with the formulae you show above. Can I give you a sample of my field (record) so you could help me extracting all 5 elements? Just as the original message in this chain, my field may contain 0 or up to 5 elements (Sales Reps for a Sales Order).

The field would be something like this: JRM_SCOTTPREP2~ROBO

Thanks for your time.

[Former Member]

Post Author: simon82

CA Forum: Formula

Thank you so much, have just used this and it works a treat.

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Yeah, I usually don't test, here's the corrected formulas:

//Middle Name:whileprintingrecords;stringvar Names := trim({table.field});if instr(Names,",") > 0 andubound(split(split(names,",")[2]," ")) > 2 thensplit(split(names,",")[2]," ")[ubound(split(split(names,",")[2]," "))]elseif instr(Names,",") = 0 thenif ubound(split(names," ")) > 2 thensplit(names," ")[2]else""

//Last Name:whileprintingrecords;stringvar Names := trim({table.field});if instr(Names,",") > 0 thensplit(names,",")[1]elseif instr(Names,",") = 0 thensplit(names," ")[ubound(split(names," "))]else""

-k

[Former Member]

Post Author: simon82

CA Forum: Formula

excellent, thats K.

i have copied these in and the first one works, the other two dont at the mo so ill have a little play around and hopefully get these working.

Thanks for taking the time to reply all

[Former Member]

Post Author: SKodidine

CA Forum: Formula

Long way home isnt half of it.. LOL

The temperature up here finally hit 70degrees and 22+ hrs of daylight. Sunshine at midnight is awesome... no excuses for the sloppy solution though!

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Jagan: Sorry if I came across as unchilled, I wasn't and am not. It just appeared that you were suggesting loops in lieu of a Join.

Simon:

Yeah, you're in deep kim chee. What you need to do is fire the coder that designed this application, they're incompetent. Name fields should at minimum be captured as Prefix, first, middle, last and suffix as seperate fields. At some point you may want to email them as Mr./Mrs./Ms/Dr./etc, hence the prefix, and some prefer that you include Jr./III/Esq/etc, hence the suffix. And note that some people have two last names, for instance try coding for Oscar De La Hoya, and meeting all other rules, it just can't be done.

Anyway, you already have them seperated, your problem is constructing logic, which I've had to do far too many times:

whileprintingrecords;stringvar Names := split(trim({table.field})," ")

But you have NO way of knowing how to display them, you can guess, or run the database through a name cleansing program.

So this wil get you kinda close:

First name:whileprintingrecords;stringvar Names := {table.field};if instr(Names,",") > 0 thensplit(split(names,",")[2]," ")[1]elsesplit({table.field}," ")[1]

Last name:whileprintingrecords;stringvar Names := {table.field};if instr(Names,",") > 0 thensplit(names,",")[1]elsesplit({table.field}," ")[ubound(Names)]

Middle Name:whileprintingrecords;stringvar Names := {table.field};if instr(Names,",") > 0 thenif ubound(split(split(names,",")[2]," ")) > 1 thensplit(split(names,",")[2]," ")[2]elseif ubound(split(names," ") > 2 thensplit({table.field}," ")[2]

I didn't test so there may be some syntax buglets, but the theory looks sound.

You'll find plenty of exceptions to these rules, good luck.

-k

[Former Member]

Post Author: simon82

CA Forum: Formula

thanks for the options guys. I will give them a try.

I do not know how to code loops so will try and avoid this if possible. i just need to split a field and so that i have all the sections of a field (using the " " do determine the split) then place these seperate bits onto a report.

Basically our customer name field is one field. we dont have Title, forname, surname we just have name... so someone can input what they want.

this is going to cause problems as when i split, one person might have put surname, forename, another person put forname surname title etc. this is another problem I will deal with when i get to it.

for now i just need to split the name field into as many sections as it has whether that me john smith (so 2 bits), smith john james MR (4 bits) etc

Thanks

[Former Member]

Post Author: Jagan

CA Forum: Formula

Mate, you need to chill.

Simon82's original post didn't mention anything about joining; that was just an example that Skodidine added to the end of his code.

As you are so fond of reminding people, we have no idea what Simon wants to do with the field once it is split, so I was merely informing him of the UBound function as it may be useful to him. For example, Simon may just want the first and last element, so my post may have allowed him to realise that MyValues[UBound(MyValues)] would yield the last element.

I even tested it

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Jagan: Why would you want a loop? JOIN does the same thing as looping and storing into a variable, try testing before you post.

You're just slowing down the report and adding to the amount of code to maintain.

Makes no sense to me.

But it's true that the ubound() function will tell you the size of an array...

-k

[Former Member]

Post Author: Jagan

CA Forum: Formula

Also, having donestringvar array MyValues:= split(trim({table.field})," ");you could use UBound to get how many elements are in the array, so you can code a for loop from 1 to UBound(MyArray) etc.

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Skodidine: Long way home, there...

The equivalent of all of your code is:

whileprintingrecordsstringvar array MyValues:= split(trim({table.field})," ");join(MyValues,chr(13))

Note that the JOIN function will accept whatever delimiter is required. You know about the SPLIT function, now leverage the JOIN() and eliminate the looping.

But you are correct in that the poster doesn't show examples or state their requirements, they just say that what they have isn't what they want and share code that doesn't work.

-k

[Former Member]

Post Author: SKodidine

CA Forum: Formula

I am not sure how you intend to use all elements of a sting delimited by a space but here is what you have to do.

1. Trim() // to eliminate any leading or trailing spaces

2. Code a FOR loop to determine how many spaces exist in the string (which will give you the number of "splits").

3. Code a second FOR loop from 1 to the result from 2. to give you the elements of the string.

For example:

STRINGVAR TEMP := ' JOHN DOE IS A TEST DUMMY ';

STRINGVAR X := TRIM(TEMP); // ELIMINATE LEADING AND TRAILING SPACES IF ANY

//

NUMBERVAR I;

NUMBERVAR Z;

NUMBERVAR STRLEN := LENGTH(X);

STRINGVAR ARRAY Y;

//

// DETERMINE THE NUMBER OF ELEMENTS IN A STRING

// BY COUNTING THE NUMBER OF SPACES

//

FOR I := 1 TO STRLEN DO

( IF X[I] = ' ' THEN Z := Z + 1;

);

//

// LOAD THE ELEMENTS OF THE STRING INTO AN ARRAY

//

IF Z = 0 THEN Z := 1;

REDIM Y[Z];

//

FOR I := 1 TO Z DO

(

Y[I] := SPLIT(X)[I];

);

//

// YOU CAN USE THE ARRAY OR ITS ELEMENTS AS REQUIRED

JOIN(Y,CHRW(13)); // JUST AN EXAMPLE OF THE FINAL ELEMENTS OF THE STRING

// IF YOU DO USE THE JOIN THEN MAKE SURE THE FIELD HAS 'CAN GROW' CHECKED

[Former Member]

Post Author: simon82

CA Forum: Formula

i have tried this already,

this works fine for the ones that have two parts of an arrray for the ones that dont, the report falls over.

i need a formula split the field into the required number of bits where required.

cant just use split [1] and split [2]

[Former Member]

Post Author: V361

CA Forum: Formula

Try this.

split(," ")[2]

This will split your string at the first " " and gives you the second result.

For example, if the string were "Doe John" then you will get "John" by using the above split function. If you want the last name then use the subscript of [1].