f



Noob question - parse web form/email data into fields

New to FMP - and LOVE it so far! Playing with receiving email - and that
seems to work good - except I'd like to parse data into individual fields
from a web form - for contact management.
(i.e. 

The results of your request are below:
FirstName: John 
LastName: Doe
Street: 123 Main St
City: MyTown
State: MYState
Zip: 12345
Phone: 123-456-7890
Email: jdoe@aol.com

)


Have worked with DB's quite a bit - but have never had to parse data like
this before.

Any help/guidance/book suggestion/plugin/etc would be appreciated!

TIA!
-Jeff
Comp.db.fmpro @ brightenyourcorner.com

0
6/2/2005 4:38:57 AM
comp.databases.filemaker 11053 articles. 0 followers. amosw01 (46) is leader. Post Follow

8 Replies
915 Views

Similar Articles

[PageSpeed] 5


On 6/2/2005, Jeff Conley wrote:

> New to FMP - and LOVE it so far! Playing with receiving email - and that
> seems to work good - except I'd like to parse data into individual fields
> from a web form - for contact management.
> (i.e.
>
> The results of your request are below:
> FirstName: John
> LastName: Doe
> Street: 123 Main St
> City: MyTown
> State: MYState
> Zip: 12345
> Phone: 123-456-7890
> Email: jdoe@aol.com
>
> )
>
>
> Have worked with DB's quite a bit - but have never had to parse data like
> this before.
>
> Any help/guidance/book suggestion/plugin/etc would be appreciated!
>
> TIA!
> -Jeff
> Comp.db.fmpro @ brightenyourcorner.com

You'll want to take a look at the various text functions.

If, for instance, you use the Position function to find the location of 
the entry element in the string, you can build a calc that takes the two 
characters following it and puts them in a certain field.

For instance, in a string YourField of any length, you have "State: VT" 
(the word "State" followed by a colon and a space then the state code), 
you want the two characters starting at the seventh character after the 
start of "State: ", like so:

xxxxxx
xxxxx
xxxx
xxxx
State = VT

This calculation

Middle ( YourField ; Position ( YourField ; "State: "; 1; 1) + 7; 2)

will return "VT"

Matt
0
Matt
6/2/2005 11:05:40 AM
Jeff Conley <comp.db.fmp@brightenyourcorner.com> wrote:

> New to FMP - and LOVE it so far! Playing with receiving email - and that
> seems to work good - except I'd like to parse data into individual fields
> from a web form - for contact management.
> (i.e. 
> 
> The results of your request are below:
> FirstName: John 
> LastName: Doe
> Street: 123 Main St
> City: MyTown
> State: MYState
> Zip: 12345
> Phone: 123-456-7890
> Email: jdoe@aol.com
> 
> )
> 
> 
> Have worked with DB's quite a bit - but have never had to parse data like
> this before.

In versions of FM before 7, I would have used the Set Field, Middle, and
Position functions to parse out this text. 

7 gives more options, and I might take the easy way and use Set Field
and Middle Values, which would capture an entire line, and then
Substitute to get rid of the "FirstName: " strings.  

Lynn Allen
--
Allen & Allen Semiotics        www.semiotics.com
FSA Associate       Filemaker Design & Consulting 
0
lynn
6/2/2005 4:00:44 PM
In article <1gxitzu.ti6lek1ljx7e4N%lynn@NOT-semiotics.com>, Lynn allen
<lynn@NOT-semiotics.com> wrote:

> Jeff Conley <comp.db.fmp@brightenyourcorner.com> wrote:
> 
> > New to FMP - and LOVE it so far! Playing with receiving email - and that
> > seems to work good - except I'd like to parse data into individual fields
> > from a web form - for contact management.
> > (i.e. 
> > 
> > The results of your request are below:
> > FirstName: John 
> > LastName: Doe
> > Street: 123 Main St
> > City: MyTown
> > State: MYState
> > Zip: 12345
> > Phone: 123-456-7890
> > Email: jdoe@aol.com
> > 
> > )
> > 
> > 
> > Have worked with DB's quite a bit - but have never had to parse data like
> > this before.
> 
> In versions of FM before 7, I would have used the Set Field, Middle, and
> Position functions to parse out this text. 
> 
> 7 gives more options, and I might take the easy way and use Set Field
> and Middle Values, which would capture an entire line, and then
> Substitute to get rid of the "FirstName: " strings.  

An alternative is to reconfigure the web form to dump the output into
a CSV file and import that.  Use the first line for the field headings,
and you could even import multiple responses from an aggregate CSV file.

(This is how I imported student enrolment data into an FM6 database)
0
David
6/2/2005 6:03:25 PM
In article <BEC405A0.E72%comp.db.fmp@brightenyourcorner.com>, Jeff
Conley <comp.db.fmp@brightenyourcorner.com> wrote:

> New to FMP - and LOVE it so far! Playing with receiving email - and that
> seems to work good - except I'd like to parse data into individual fields
> from a web form - for contact management.
> (i.e. 
> 
> The results of your request are below:
> FirstName: John 
> LastName: Doe
> Street: 123 Main St
> City: MyTown
> State: MYState
> Zip: 12345
> Phone: 123-456-7890
> Email: jdoe@aol.com
> 
> )
> 
> 
> Have worked with DB's quite a bit - but have never had to parse data like
> this before.
> 
> Any help/guidance/book suggestion/plugin/etc would be appreciated!
> 
> TIA!

It would depend on how you're getting the data into FileMaker in the
first place. You can't simply import something like that since the
return characters would put each line into a new record.

If all the replies are within one file, then it will also depend on how
individual contact replies are separated from each other.

Once that information is known it's really becomes just a matter of
processing power.   :o)



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
Helpful
6/4/2005 6:20:48 AM
Hi Matt,

I always do this sort of thing creating a filemaker database with one text 
field in it.  Call it Import Field or something like that.  I then import 
the file, which results in one line of text per record.  I then write a 
script with a loop in it that skips through the lines looking for things I 
know should be there.  Your example is a really good candidate for this, 
because you have predictable text, like "Firstname: "

I delete all records that are junk, like the one that says "The results of 
your request are below:"

I then create a bunch of calculation fields that use text parsing functions 
to fill the info I'm looking for.

Here's a quick example in FM 7.  I've named the field "FirstName" and it's a 
calculation with the following formula:

If ( Left ( Import Line; 9) = "FirstName"; Trim ( Middle ( Import Line; 
Position ( Import Line; ":"; 1; 1)+1;  100)); "")

That would yield "John" in your example below

I might also use the "replace" command to fill a field like "Customer_ID" or 
something so I could create the relationship between my customers file and 
this record.

Maybe that jumpstarts your thinking on this.  It's somewhat tedious to make 
a formula like this for every field, but I have done it a lot when 
converting unusually formatted text files into databases, and once you get 
it all scripted, it's worth the effort, because you then just run a script 
and the data shows up where it's supposed to be.

Some functions to look at are

Left
LeftWords
Middle
MiddleWords
Position
Replace
Right
RightWords
Substitute
Trim
Wordcount

I hope this helps,
Ron


"Matt Wills" <I'm@Witz.end> wrote in message 
news:8KBne.6286$zb.5730@trndny06...
>
>
> On 6/2/2005, Jeff Conley wrote:
>
>> New to FMP - and LOVE it so far! Playing with receiving email - and that
>> seems to work good - except I'd like to parse data into individual fields
>> from a web form - for contact management.
>> (i.e.
>>
>> The results of your request are below:
>> FirstName: John
>> LastName: Doe
>> Street: 123 Main St
>> City: MyTown
>> State: MYState
>> Zip: 12345
>> Phone: 123-456-7890
>> Email: jdoe@aol.com
>>
>> )
>>
>>
>> Have worked with DB's quite a bit - but have never had to parse data like
>> this before.
>>
>> Any help/guidance/book suggestion/plugin/etc would be appreciated!
>>
>> TIA!
>> -Jeff
>> Comp.db.fmpro @ brightenyourcorner.com
>
> You'll want to take a look at the various text functions.
>
> If, for instance, you use the Position function to find the location of 
> the entry element in the string, you can build a calc that takes the two 
> characters following it and puts them in a certain field.
>
> For instance, in a string YourField of any length, you have "State: VT" 
> (the word "State" followed by a colon and a space then the state code), 
> you want the two characters starting at the seventh character after the 
> start of "State: ", like so:
>
> xxxxxx
> xxxxx
> xxxx
> xxxx
> State = VT
>
> This calculation
>
> Middle ( YourField ; Position ( YourField ; "State: "; 1; 1) + 7; 2)
>
> will return "VT"
>
> Matt 


0
Ron
6/4/2005 5:19:22 PM
Ron's method is a great way of doing it.  Another way that I have used 
is to provide users with a global field on a layout in which they paste 
their email.  Then I have a script that runs through the parameters and 
parses them into the correct fields.

Since you say you're new to FileMaker, I'll assume in the code that I 
use that you are using FM7.  So my script might look something like this:

New Record/Request
Set Field [First_Name; (See Calc Below)]
Set Field [Last_Name; (See calc below, with minor change to the TagName)]
etc.

The calc would be something like this ("something like" because I just 
wrote this in the emailand so it has not been tested or debugged):

Let( [
   TagName = "FirstName";
   TagLength = Length(TagName)+3;
   StartPos = Position(gPastedText, TagName, 1, 1) + TagLength;
   EndPos = Position(gPastedText & "�", "�", StartPos, 1)-1;
   TextLength = EndPos - StartPos  ];
Middle(gPastedText, StartPos, TextLength) )



Ron Hofius wrote:
> Hi Matt,
> 
> I always do this sort of thing creating a filemaker database with one text 
> field in it.  Call it Import Field or something like that.  I then import 
> the file, which results in one line of text per record.  I then write a 
> script with a loop in it that skips through the lines looking for things I 
> know should be there.  Your example is a really good candidate for this, 
> because you have predictable text, like "Firstname: "
> 
> I delete all records that are junk, like the one that says "The results of 
> your request are below:"
> 
> I then create a bunch of calculation fields that use text parsing functions 
> to fill the info I'm looking for.
> 
> Here's a quick example in FM 7.  I've named the field "FirstName" and it's a 
> calculation with the following formula:
> 
> If ( Left ( Import Line; 9) = "FirstName"; Trim ( Middle ( Import Line; 
> Position ( Import Line; ":"; 1; 1)+1;  100)); "")
> 
> That would yield "John" in your example below
> 
> I might also use the "replace" command to fill a field like "Customer_ID" or 
> something so I could create the relationship between my customers file and 
> this record.
> 
> Maybe that jumpstarts your thinking on this.  It's somewhat tedious to make 
> a formula like this for every field, but I have done it a lot when 
> converting unusually formatted text files into databases, and once you get 
> it all scripted, it's worth the effort, because you then just run a script 
> and the data shows up where it's supposed to be.
> 
> Some functions to look at are
> 
> Left
> LeftWords
> Middle
> MiddleWords
> Position
> Replace
> Right
> RightWords
> Substitute
> Trim
> Wordcount
> 
> I hope this helps,
> Ron
> 
> 
> "Matt Wills" <I'm@Witz.end> wrote in message 
> news:8KBne.6286$zb.5730@trndny06...
> 
>>
>>On 6/2/2005, Jeff Conley wrote:
>>
>>
>>>New to FMP - and LOVE it so far! Playing with receiving email - and that
>>>seems to work good - except I'd like to parse data into individual fields
>>>from a web form - for contact management.
>>>(i.e.
>>>
>>>The results of your request are below:
>>>FirstName: John
>>>LastName: Doe
>>>Street: 123 Main St
>>>City: MyTown
>>>State: MYState
>>>Zip: 12345
>>>Phone: 123-456-7890
>>>Email: jdoe@aol.com
>>>
>>>)
>>>
>>>
>>>Have worked with DB's quite a bit - but have never had to parse data like
>>>this before.
>>>
>>>Any help/guidance/book suggestion/plugin/etc would be appreciated!
>>>
>>>TIA!
>>>-Jeff
>>>Comp.db.fmpro @ brightenyourcorner.com
>>
>>You'll want to take a look at the various text functions.
>>
>>If, for instance, you use the Position function to find the location of 
>>the entry element in the string, you can build a calc that takes the two 
>>characters following it and puts them in a certain field.
>>
>>For instance, in a string YourField of any length, you have "State: VT" 
>>(the word "State" followed by a colon and a space then the state code), 
>>you want the two characters starting at the seventh character after the 
>>start of "State: ", like so:
>>
>>xxxxxx
>>xxxxx
>>xxxx
>>xxxx
>>State = VT
>>
>>This calculation
>>
>>Middle ( YourField ; Position ( YourField ; "State: "; 1; 1) + 7; 2)
>>
>>will return "VT"
>>
>>Matt 
> 
> 
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
6/4/2005 5:49:10 PM
Howard Schlossberg wrote:

> New Record/Request
> Set Field [First_Name; (See Calc Below)]
> Set Field [Last_Name; (See calc below, with minor change to the TagName)]
> etc.
> 
> The calc would be something like this ("something like" because I just 
> wrote this in the emailand so it has not been tested or debugged):
> 
> Let( [
>   TagName = "FirstName";
>   TagLength = Length(TagName)+3;
>   StartPos = Position(gPastedText, TagName, 1, 1) + TagLength;
>   EndPos = Position(gPastedText & "�", "�", StartPos, 1)-1;
>   TextLength = EndPos - StartPos  ];
> Middle(gPastedText, StartPos, TextLength) )

One more note -- not a neccessity -- just a cool trick that might help 
you simplify your code.

If you have FM Developer, you can create a custom function:

--------------------------------------------------------
Extract ( BucketField, TagName ) =
--------------------------------------------------------
Let( [
   TagLength = Length(TagName)+3;
   StartPos = Position(BucketField, TagName, 1, 1) + TagLength;
   EndPos = Position(BucketField & "�", "�", StartPos, 1)-1;
   TextLength = EndPos - StartPos  ];
Middle(BucketField, StartPos, TextLength) )
--------------------------------------------------------

Now your script would look like:

New Record/Request
Set Field [First_Name; Extract(gPastedText, "FirstName") ]
Set Field [Last_Name; Extract(gPastedText, "LastName") ]
Set Field [Address; Extract(gPastedText, "Street") ]
....
etc.


-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance
0
Howard
6/4/2005 5:58:00 PM
I'm a little late to the discussion, but I am working on a
'regular expression' plug-in (RE's) to parse documents
(mainly legal) which is particularly useful as you can
set up many circumstances  to parse with.  This is the
way a compiler tells the difference between keywords
(often with a product called 'Bison') and how to use their
assign their function.

If you are not familiar with them,

This example matches dates:

 (19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])

 	(  	begins a group
	19	matches '19'
	|	is an 'or'
	29 	matches '20
	)	closes group
	\d	Shorthand character classes matching digits 0-9
	\d	first for decade, second for year
	[- /.]	Matches a single character out of the list: one
		of the characters "- /."

 And so on...

This matches ANY email address:

 \b[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}\b

They are supported in Java so you could parse them via FM-JDBC.

My first implementation simply parsed them with a recursive
..fp7 function, which was fine for interpretting things like
23.4.5.1 (section headers) but ran a little slow as I expanded
its function. That still may be practical in your case.

I expect to be beta testing in a couple of weeks and would be
happy to send any of you a beta copy. If that sounds interesting,
send me an e-mail at jp@psol.biz

Here is a good online reference:

  http://www.regular-expressions.info/reference.html

 - jeanpaul

0
jeanpaulsc
6/5/2005 2:41:18 PM
Reply: