Parts of a memo field 1

[stillsteam]

Hello

Crystal 9 and SQL Server

I need to pull out up to 3 parts from this field.
There could be lots of info in the field that I don´t need for this

I need length,width and height.
Here are some examples
47x47x1285mm KD-märkning = Length 47, Widht 47, Height 1285
A4 80 g laxfärgat = Length 0, Widht 0, Height 0
600x50mm = Length 600, Wwidht 50, Height 0
back:600x400x300mm ark:495x495mm tryck 1 färg röd. =Length 600, Widht 400, Height 300
570x380x380/250/180mm med handtagshål = Length 570, Widht 380, Height 380

I want them in 3 separate formulas.
If there is a "x" in the string then the numberpart before the first x is Length and after the first x is the Widht and if there is a second x then the number part after the second x is the Height.

There could be an x anywhere in the string that has nothing to do with the numberparts that I want to pull out...

I could settle with a different solution.
If the field starts with numberxnumberxnumber then produce the 3 different formulas.

Is this possible?
Please ask if you need more information
Thank you in advance


/Jonas

 

[Madawc]

Split({Your.field}, ",") ought to separate out the three elements, on the basis of the examples you show. That should yield an array of three elements, containing the lenght, width and height.

To get the numeric element you should be able to move them each into another formula field and split again, this time using space, Split(@Length, " "). The number of elements in the array would vary but the last ought to be the number.

Try displaying the data as split, to see if it works, before attempting calculations. Very few databases are without 'dirty' data that breaks the rules.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[stillsteam]

I´m sorry, I wasn´t clear on my examplas.
The part after the = sign is not part of the field. It is the info I need to pull out.

Thanks in advance

/Jonas

 

[Madawc]

Tricky. Can anyone else see a way? I could imaging checking character by character, but what about the 80g which does not count?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I can't guarantee that this will work in all cases, but it seems to work here. Create three formulas:

//{@Length}:
stringvar k := "";
numbervar b := 0;
numbervar c := len({@string});//Substitute your field name for {@string}
for b := 1 to c do(
if isnumeric({@string}) or
(
{@string} = "x" and
b <> 1 and
isnumeric({@string}[b-1])
) then
k := k + {@string} else
k := k + "#");

stringvar array w := "";
if instr(k,"x") > 0 then
w := split(k,"x") else
w := "";
stringvar x := "";
numbervar i := 0;
numbervar j := 0;

if len(w[1]) <> 0 then (
j := len(w[1]);
for i := 1 to j do(
if isnumeric(w[1]) then
x := x + w[1] else
x := ""));
if x <> "" then
"Length: " + x;

//{@Width}:
evaluateafter ({@length});
stringvar array w;
stringvar y := "";
numbervar i := 0;
numbervar j := 0;

if ubound(w) > 1 then(
if len(w[2]) <> 0 then (
j := len(w[2]);
for i := 1 to j do(
if isnumeric(w[2]) then
y := y + w[2] else
y := y)));
if y <> "" then
"Width: " + y;

//{@Height}:
evaluateafter({@Width});
stringvar array w;
stringvar h := "";
stringvar z := "";
numbervar i := 0;

if ubound(w) > 2 then(
if len(w[3]) <> 0 then (
numbervar j := len(w[3]);
for i := 1 to j do(
if isnumeric(w[3]) then
h := h + w[3] else
h := h + "#";
if isnumeric(split(h,"#")[1]) then
z := z + h )));
if z <> "" then
"Height: " + z;

-LB

 

[stillsteam]

I´m so impressed!
Like you said it´s not working all the time but enough times to get me satisfied.

Thanks LB

/Jonas

 

[lbass]

If you want to share a couple of sample instances where it is not working correctly, I could try to refine the formulas a bit.

-LB

 

[stillsteam]

Hi

It´s often when there is only length and width printed in the field for ex.
"1600x2200mm
utan tryck
artnr 93035-03 400stk/pall"

Your formula print this Length = 1600 and Width = 22009303503400
It does not know that width only is 2200
I guess it´s expecting another "x"

Kind regards



/Jonas

 

[lbass]

Change the {@width} formula to:

//{@Width}:
evaluateafter ({@length});
stringvar array w;
stringvar y := "";
numbervar i := 0;
numbervar j := 0;
stringvar k := "";

if ubound(w) > 1 then(
if len(w[2]) <> 0 then (
j := len(w[2]);
for i := 1 to j do(
if isnumeric(w[2]) then
k := k + w[2] else
k := k + "#";
if isnumeric(split(k,"#")[1]) then
y := y + k else
y := y)));
if y <> "" then
"Width: " + y;

-LB

 

[stillsteam]

Now you earned yourself another star!

Thank you LB

/Jonas