Importing the excel data is not a problem. What I did was I created a FM8 table that has fields matching the columns of the excel file. I named this table Import.

Now I need to do some parsing of this raw data. There are a few ocasions where one column contains data to many fields. Example, in excel I have a column called Address2 which has City, Province and Postal code. And in another occassion, I have many fields (in Imports table) that needs to be merged into one field (comments) for my working table. In the old access database, the developer had created 4 fields to create 4 lines of comment, instead of showing 4 lines of 1 field.

Naturally the working table has separate fields for city, province and postal code. And 1 field for comments.

I can't seem to find a way to create new records in the working table (Prospect) using the data in the Import table.

I've created a script where I managed to extract the city, province and postal code into three variables. But I can't seem to put them into a new record in the Prospect table.

Here's my script:

Go to Layout [ “Import” (Import) ]

Enter Browse Mode

Allow User Abort [ On ]

Loop

Set Variable [ $Commentaires; Value:Import::COMM1 & " " & Import::COMM2 & " " & Import::Texte579 & " " & Import::Texte582 ]

Set Variable [ $city; Value:LeftWords ( Import::address2 ; WordCount ( Import::address2 ) - 3 ) ]

Set Variable [ $postalcode; Value:RightWords ( Import::address2 ; 2) ]

Set Variable [ $province; Value:Filter ( LeftWords( RightWords ( Import::address2; 3); 1) ;

"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ") ]

Set Variable [ $Words; Value:WordCount ( Import::address2 ) ]

#set the fields with the values of the variables.

Go to Layout [ “_Prospect” (_Prospect) ]

New Record/Request

Set Field [ _Prospect::zk_prospectID_pt; Import::zk_prospect_pt ]

Set Field [ _Prospect::company_name; Import::companie ]

Set Field [ _Prospect::status; Import::Modifiable105 ]

Set Field [ _Prospect::address_postal_code; $postalcode ]

Set Field [ _Prospect::address_province; $province ]

Set Field [ _Prospect::number_general; Import::telephone ]

Set Field [ _Prospect::number_fax; Import::fax ]

Set Field [ _Prospect::date_creation; Import::date_inscription ]

Set Field [ _Prospect::sales[import::vente] ]

Set Field [ _Prospect::employee_number; Import::NMBREEMPLO ]

Set Field [ _Prospect::anticipated_revenue; Import::REV_ANTICI ]

Go to Layout [ “Import” (Import) ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

What I would do is create calculated fields for City, Province, Postal Code & Notes In the Import table. Then you can export these to the working table.

It looks like you have the calculations in your script.

I think you script is failing because you do not a valid relationship. You might try:

Set Variable [ $ID; Value:Import::zk_prospect_pt ] before

Go to Layout [ “_Prospect” (_Prospect) ]

and change

Set Field [ _Prospect::zk_prospectID_pt; Import::zk_prospect_pt ]

to

Set Field [ _Prospect::zk_prospectID_pt; $ID ]

Thank you for the prompt reply RalphL, I just found free time to work on my database again. You seem to know alot about filemaker, could you tell me what is the difference between a function and a script? And why can't I find loop functions when I'm creating a function? Hmmm I think I kind of answered my own question just now.

Edit: I know that you can call functions in the calculation field but not a script. I've been having a real hard time creating a function that is equivalent my scripts.

why can't I find loop functions when I'm creating a function?

You can. Custom Functions allow for iteration. You can have custom function call itself (recursiveness) to produce the result you want. Truly powerful.