Value Separation

[Salman Maidan-3]

Hello Folks,

WinXp, FMPA10

I have a table with 1000s records, one of the fields holds text values like
so:
1.1.1 - 3.12.15 - 5.3.18 - 4.11.29 ... and so on.

I want to separate the numbers int three separate fields dropping the point
"." between the numbers.

Any help please

--
Salman Maidan
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Lorne & Joan Walton]

Create 3 new fields, and use auto-enter by calculation:

"First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
"¶" ) ; 1 )

"Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
"¶" ) ; 2 )

"Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
"¶" ) ; 3 )

The "¶" is the paragraph character. Let me know if you have trouble  
with this.

On Oct 29, 2009, at 10:06 PM, Salman Maidan wrote:

> I have a table with 1000s records, one of the fields holds text  
> values like
> so:
> 1.1.1 - 3.12.15 - 5.3.18 - 4.11.29 ... and so on.
>
> I want to separate the numbers int three separate fields dropping  
> the point
> "." between the numbers.

--
The great advantage about telling the truth is that nobody ever  
believes it. [Dorothy Sayers]
Lorne & Joan Walton, Maple Ridge, BC, Canada




_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Lee Smith-5]

or,

Let(  [    y = Text;    L = LeftWords (y; 1)  ];  Filter( l;  
123456789 ))
Let(  [    y = Text;    m = MiddleWords( Text; 2; 1 )  ];  Filter( m;  
123456789 ))
Let(  [    y = Text;    m = MiddleWords( Text; 3; 1 )  ];  Filter( m;  
123456789 ))
Let(  [    y = Text;    m = MiddleWords( Text; 4; 1 )  ];  Filter( m;  
123456789 ))

Since you stated that the field was Text already.
Text = your current field that the string is in now.

HTH

Lee
On Oct 29, 2009, at 10:56 PM, Lorne & Joan Walton wrote:

> Create 3 new fields, and use auto-enter by calculation:
>
> "First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 1 )
>
> "Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 2 )
>
> "Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 3 )
>
> The "¶" is the paragraph character. Let me know if you have trouble  
> with this.
>
> On Oct 29, 2009, at 10:06 PM, Salman Maidan wrote:
>
>> I have a table with 1000s records, one of the fields holds text  
>> values like
>> so:
>> 1.1.1 - 3.12.15 - 5.3.18 - 4.11.29 ... and so on.
>>
>> I want to separate the numbers int three separate fields dropping  
>> the point
>> "." between the numbers.
>
> --
> The great advantage about telling the truth is that nobody ever  
> believes it. [Dorothy Sayers]
> Lorne & Joan Walton, Maple Ridge, BC, Canada
>
>
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Salman Maidan-3]

Thank you Lee, I will look at that too, it looks complicated but I am sure
it will cut it.

Regards
Salman

On Fri, Oct 30, 2009 at 9:29 AM, Lee Smith <[hidden email]> wrote:

> or,
>
> Let(  [    y = Text;    L = LeftWords (y; 1)  ];  Filter( l; 123456789 ))
> Let(  [    y = Text;    m = MiddleWords( Text; 2; 1 )  ];  Filter( m;
> 123456789 ))
> Let(  [    y = Text;    m = MiddleWords( Text; 3; 1 )  ];  Filter( m;
> 123456789 ))
> Let(  [    y = Text;    m = MiddleWords( Text; 4; 1 )  ];  Filter( m;
> 123456789 ))
>
> Since you stated that the field was Text already.
> Text = your current field that the string is in now.
>
> HTH
>
> Lee
>
> On Oct 29, 2009, at 10:56 PM, Lorne & Joan Walton wrote:
>
>  Create 3 new fields, and use auto-enter by calculation:
>>
>> "First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ; 1
>> )
>>
>> "Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ;
>> 2 )
>>
>> "Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ; 3
>> )
>>
>> The "¶" is the paragraph character. Let me know if you have trouble with
>> this.
>>
>> On Oct 29, 2009, at 10:06 PM, Salman Maidan wrote:
>>
>>  I have a table with 1000s records, one of the fields holds text values
>>> like
>>> so:
>>> 1.1.1 - 3.12.15 - 5.3.18 - 4.11.29 ... and so on.
>>>
>>> I want to separate the numbers int three separate fields dropping the
>>> point
>>> "." between the numbers.
>>>
>>
>> --
>> The great advantage about telling the truth is that nobody ever believes
>> it. [Dorothy Sayers]
>> Lorne & Joan Walton, Maple Ridge, BC, Canada
>>
>>
>>
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>

--
Salman Maidan
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Mark Rubenstein]

> "First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 1 )
> "Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 2 )
> "Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
> "¶" ) ; 3 )

Lorne, that's nice and simple. But I think you meant for the period to  
be a hyphen.

Salman, if the hyphen has spaces on either side of it, you will end up  
with spaces in the new fields.
You can remove them with Trim, like this:

Trim ( GetValue ( Substitute ( GetAsText ( Text ) ; "-" ; "¶" ) ; 1 ) )


Mark Rubenstein
[hidden email]
www.easyaspi.com

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Lorne & Joan Walton]

Mark, my reading of Salman's quest was that each record contains data  
of the form "x.y.z" and that he was using the " - " to delineate  
individual records. He wanted to remove the periods, and separate the  
x, the y, and the z into 3 fields.

On Oct 30, 2009, at 7:54 AM, Mark Rubenstein wrote:

>> "First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
>> "¶" ) ; 1 )
>> "Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
>> "¶" ) ; 2 )
>> "Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ;  
>> "¶" ) ; 3 )
>
> Lorne, that's nice and simple. But I think you meant for the period  
> to be a hyphen.

--
This information is top security. When you have read it, destroy  
yourself. [Marshall McLuhan]
Lorne Walton, Maple Ridge, BC, Canada



_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Mark Rubenstein]

On Oct 30, 2009, at 10:28 AM, Lorne & Joan Walton wrote:

> Mark, my reading of Salman's quest was that each record contains  
> data of the form "x.y.z" and that he was using the " - " to  
> delineate individual records. He wanted to remove the periods, and  
> separate the x, the y, and the z into 3 fields.

Ah, I see.

I thought he meant that each record contains data of the form "a.b.c -  
d.e.f - g.h.i", and that he wants the values between the hyphens to be  
separated into three fields, with the periods removed.

Only he knows!


Mark Rubenstein
[hidden email]
www.easyaspi.com

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Salman Maidan-3]

Thats Correct, I basically wanted to get rid of the periods between the
x.y.z as Lorne very neatly put it. Thank you all for your input.

Salman


On Fri, Oct 30, 2009 at 6:28 PM, Lorne & Joan Walton <[hidden email]>wrote:

> Mark, my reading of Salman's quest was that each record contains data of
> the form "x.y.z" and that he was using the " - " to delineate individual
> records. He wanted to remove the periods, and separate the x, the y, and the
> z into 3 fields.
>
>
> On Oct 30, 2009, at 7:54 AM, Mark Rubenstein wrote:
>
>  "First" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ; 1
>>> )
>>> "Second" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ;
>>> 2 )
>>> "Third" = GetValue ( Substitute ( GetAsText ( Oldfield ) ; "." ; "¶" ) ;
>>> 3 )
>>>
>>
>> Lorne, that's nice and simple. But I think you meant for the period to be
>> a hyphen.
>>
>
> --
> This information is top security. When you have read it, destroy yourself.
> [Marshall McLuhan]
> Lorne Walton, Maple Ridge, BC, Canada
>
>
>
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>

--
Salman Maidan
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au