rolling up a string

[Former Member]

i have a string field (bill to name)

i would like to take the following and foll it up to one value

abc industries

abc international

abc industries international

i would like them to all be grouped by ABC

i have tried

if instr({string field},"/")>0 then

split({string field},"/") [1]

i receive an error THIS ARRAY MUST BE SUBSCRIPTED for example ARRAY <i>,

im a bit confused can anyone tell me how to get this to work please, not familar with arrays.

[former_member292966 · Accepted solution]

Hi Sharon,

I'm not exactly sure what it is you are trying to do but your formula is erring out because of the subscript in the last line.

You can't subscript a function. Are you trying to get the first character from the split? It should look like:

StringVar X;

X := split({string field},"/");

X [1];

Next, what you described and what your formula are doing is confusing. Your formula is looking for "/" but I don't see any in your example.

Can you explain what you mean by rolling the records into one value? Are you trying to append them together or just group based on the "abc"?

To group them create a formula like:

If {string.field} [1 to 3] = "abc" Then 
   "abc" 
Else {string.filed};

Now you should be able to group on this formula.

Good luck,

Brian

[Former Member · Accepted solution]

I am trying to capture the companies that have the 1st or 2nd word in the string the same

i have 3 companies

ABC INT.

ABC INDUSTRIES

ABC VENTURES

DFGRH COMPANY

DFGRH INDUSTRIES

JOE SMITH VENTURES

JOE SMITH CAPITAL

i would like to be able to create a formula to capture the first and / or 2nd word of the string.

i tried

StringVar X;

X := split({string field},"/");

X [1];

it returns null values

[Former Member · Accepted solution]

instaed of trying to describe what you want, take teh easy root and provide a sample of your data and the expected outcome.

That would make life a lot easier al around.

[Former Member · Accepted solution]

the above did display a sample of my data, but here it is again

ABC INT.

ABC INDUSTRIES

ABC VENTURES

DFGRH COMPANY

DFGRH INDUSTRIES

JOE SMITH VENTURES

JOE SMITH CAPITAL

would like to see

ABC

DFGRH

JOE SMITH

[former_member292966 · Accepted solution]

Hi Sharon,

Got it. So you want to group the companies based on part of their names.

This is where it gets more complicated than you might have expected. For companies ABC and DFGRH it would be simple because their names only have 2 words. Include JOE SMITH and now you need to figure out which combination of the 3 words you want to group on.

Try this then:

If InStr ({table.FIELD}, " ") Then 
	{table.FIELD} [1 to InStr ({table.FIELD}, " ")] 
Else {table.FIELD}

Unfortunately I took the easy road out but this will search for the first space in the string then parse out everything up to the space.

Good luck,

Brian

[Former Member · Accepted solution]

this is my syntax

(If InStr ({v_SALES_DETAIL_BILLTO.BillName}, " ") Then

{v_SALES_DETAIL_BILLTO.BillName}[1 to InStr ({v_SALES_DETAIL_BILLTO.BillName}," ")]

Else ({v_SALES_DETAIL_BILLTO.BillName}))

i receive boolean required here

what am i missing???

[former_member292966 · Accepted solution]

Hi Sharon,

Sorry. That's my fault. The If requires a result from the InStr function. Try this:

(If InStr ({v_SALES_DETAIL_BILLTO.BillName}, " ") <> 0 Then 
{v_SALES_DETAIL_BILLTO.BillName}[1 to InStr ({v_SALES_DETAIL_BILLTO.BillName}," ")]  
Else ({v_SALES_DETAIL_BILLTO.BillName}))

Thanks,

Brian

[Former Member · Accepted solution]

Try This

LocalNumberVar i;
Local StringVar strTemp;

For i := 1 to Ubound(Split({v_SALES_DETAIL_BILLTO.BillName},' ')) -1
Do
    strTemp := Split({v_SALES_DETAIL_BILLTO.BillName},' ')<i> & ' ';


Left(strTemp, Len(strTemp) - 1)

[Former Member · Accepted solution]

thank you that did the trick!