ActPresentation.com HOME FAQ FORUM info@ActPresentation.com
Phorum: speed & power.

ActPresentation.com


Add-ons for Sage ACT! CRM

ACT! addons by ActPresentation.com : Abacus Calculated Fields, PicturePlus, Quick Navigation List, Employment History, Quick Search, Presentation for ACT!

Break field value into separate fields

Posted by: Tom

Date: April 11, 2009 07:55PM

One of my fields contains data according to the following pattern :

ID#, Firstname, Lastname, Title all seperated by commas.

Examples....

4,John,Doe,President
51,Jane,Mason,Assistant
102,Tom,Steele,Project Manager

I would like to use Abacus to parse out the four parts into seperate
fields. Is this possible?

For example if my [Field] contains "4,John,Doe,President" I would like to break it into the following parts :

[Field 1] : 4
[Field 2] : John
[Field 3] : Doe
[Field 4] : President

Re: Break field value into separate fields

Posted by: Admin

Date: April 11, 2009 08:04PM

Yes, this is possible, but a little bit tricky. It requires recursive using CHARINDEX
function.


First of all let's find all commas positions in source field value :


-- this function returns 1st comma position

CHARINDEX ( ',' , [Field] )



-- this function returns 2nd comma position

CHARINDEX ( ',' , [Field], CHARINDEX (',',[Field])+1 )




-- this function returns 3rd comma position

CHARINDEX ( ',' , [Field], CHARINDEX (',',[Field],CHARINDEX (',',[Field])+1)+1 )



Now we can use SUBSTRING ( or LEFT ) function to extract substrings separated by commas.

-- this function extract substring from the beginning till 1-st comma

LEFT( [Field], CHARINDEX (',',[Field])-1 )


-- this function extract substring from the 1-st comma till 2-nd comma

SUBSTRING(
[Field],
CHARINDEX ( ',' , [Field] ) + 1, /* this parameter is starting position to extract */
CHARINDEX ( ',' , [Field], CHARINDEX ( ',', [Field])+1 ) - CHARINDEX ( ',' , [Field] )-1 /*
this parameter is how many symbols to extract */
)


-- this function extract substring from the 2-nd comma till 3-rd comma

substring(
[Field],
CHARINDEX ( ',' , [Field], CHARINDEX ( ',', [Field])+1 ) + 1, /* this parameter is how
many symbols to extract */
CHARINDEX ( ',' , [Field], CHARINDEX ( ',' , [Field], CHARINDEX ( ',', [Field])+1 ) + 1) -
CHARINDEX ( ',' , [Field], CHARINDEX ( ',', [Field])+1 ) -1
)


-- this function extract substring from the 3-rd comma till the end

substring(
[Field],
CHARINDEX ( ',' , [Field], CHARINDEX ( ',' , [Field], CHARINDEX ( ',', [Field])+1 ) + 1) + 1,
100
)


Last parameter 100 is how many symbols to extract.
Since we extract till the end we can pass here just big enougth value ( otherwise we have
to calculate how many symbols from last comma till the end of the string )

------

Alternative is to use both STUFF and CHARINDEX functions.

-- this function extract substring from the 1-st comma till 2-nd comma
LEFT(
STUFF( [Field], 1, CHARINDEX ( ',' , [Field] ) , ''),
CHARINDEX ( ',' , STUFF( [Field], 1, CHARINDEX ( ',' , [Field] ) , '') )-1 )

ACT! software CRM addons - best plugins for ACT contact management software ( Act! by Sage software : ACT!2005, ACT!2006, ACT!2007, ACT!2008, Act by Sage Premium for Workgroups ).
Custom addon solutions ( ACT! add ons ) development and CRM (Customer Relationship Management) software training and consulting.