Using a formula to pull from a field with XML as the source

[bobpv]

Greetings all.

Working with Crystal 10 and a progress database (ODBC Connection). There are 2 fields which hold the saleman code number and another with commission rate. Problem is the field can contain more than one salesrep and the 2nd can hold matching commission rates. Example:
SalesReplist-- Char field contains each sales rep who is getting paid on the job in this format-- 100~200~~~
the tildes seperate each code, and 3 of them end the string of data.

Commission rate is numeric with 2 decimals.
Data looks like this 1.00;0.50;2.00
The semicolons are delimiters.

How might I extract the sales code into a field like SM1,SM2,SM3 and the commission into Com1,Com2,Com3? I need to calculate each as a seperate item to show dollars earned on split commission orders.

I tried a numer of things, but seem to be missing the right way to do it. Thanks in advance!

 

[lbass]

For the salesrep field, use a formula like:

if ubound(split({table.salesrep},"~"))>=2 then
split({table.salesrep},"~")[2] //for the second rep

For the commission field, use a formula like:

if ubound(split({table.comm},";"))>= 2 then
split({table.comm},";")[2]//for the second commission

-LB