(UTC)
  Lantica User Forum › Archived Topics › Archived Topics › Importing from FileMaker Pro
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Importing from FileMaker Pro (Read 2762 times)
jondolar
Junior Member
**
Offline


Keep Trying

Posts: 57
Location: Montreal Canada
Joined: Dec 25th, 2003
Importing from FileMaker Pro
Jan 3rd, 2004 at 2:19pm
Print Post Print Post  
Hi Everybody
I have been a FIleMaker Pro developper for several years now but I was always frustrated by the lack of a real programming language and the necessity to buy plugins to do trivial things such as field events. I'm therefore very exited about Sesame and downloaded it immediately the day it was available. Now for my question....
How do I import a Master file and its related items file. These are 2 files with Matching Field Values (relational links). There is no such a thing as Automatic (Natural Link) in FMP yet, this is what I would like to use in Sesame. Is this import possible in Sesame? How? ???

TIA

Serge
  
When all you have is a hammer, everything starts to look like nail!
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing from FileMaker Pro
Reply #1 - Jan 3rd, 2004 at 2:35pm
Print Post Print Post  
We're glad you asked! Grin

Sesame has the ability to perform a hierarchical import, automatically creating natually linked parent/subrecords. You just need to structure your import file properly.

The instructions for doing this begin on page 322 of the User Guide. There is also a Library article on the subject.
8)
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
jondolar
Junior Member
**
Offline


Keep Trying

Posts: 57
Location: Montreal Canada
Joined: Dec 25th, 2003
Re: Importing from FileMaker Pro
Reply #2 - Jan 3rd, 2004 at 3:51pm
Print Post Print Post  
Hi Hammer

Thanks you for your prompt response. I read the Import Section (page 322) of the User Manual as well as the library article. However I think, while the approach is valid, it is somewhat oversimplified. In my real life situation I have the following:

1- A database (Order) witn a form. The top of the form contains the Order Number and the Customer Id field (primary key). Once the Customer Id is filled, the remaining fields on the first half of the form (around 12 fields such as name, adresse etc) get populated with data from the linked database Customers, based on Customer Id (foreign key)from the Customer Database. The bottom part of the form contains a Subform with the line items.

2- A database (Customer) Containing all the details pertaining to each customer.

3- A database (Line Items) Linked to the Order database by the Order number. It also has a Product Id field. When this field is filled, details about this product such as description, price etc get filled automatically from the database Products. This Database is linked to the database Products with the field Product Id.

4- A database (Products). Contains all the products for sale

I'm sure you are familiar with all this. This is a classical "Order Desk" application. There could be some very large orders with say 200 products. How do you then create a single CSV file (from 4 files as I just described) suitable to import in Sesame?

Thanks again for your time

Regards

Serge
  
When all you have is a hammer, everything starts to look like nail!
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing from FileMaker Pro
Reply #3 - Jan 3rd, 2004 at 4:30pm
Print Post Print Post  
I would not recommend using Natural Linking for all levels of such a complicated application. Order->LineItems is a good candidate for natural linking. The other relationships are probably better off using relational linking. Many-to-Many to relationships, such as the defacto between Customers and Products are not good candidates for natural linking. Natural linking works best in a one-to-many relationship where you would rarely want to see the parent without the children, and vice-versa.

You can do a hierarchical import on your Orders and Line Items (creating natural links), then separate imports for the other databases, retaining keys as needed.
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
jondolar
Junior Member
**
Offline


Keep Trying

Posts: 57
Location: Montreal Canada
Joined: Dec 25th, 2003
Re: Importing from FileMaker Pro
Reply #4 - Jan 3rd, 2004 at 5:16pm
Print Post Print Post  
Thanks again for the reply.

Keeping only Order and Line Items for natural link is ok but still it is a problem to create a unique CSV file with say, one Order record containing 200 Line Items.
On top of my head, I would have to create 200 additionnal fields in the Order Database then loop through the line items database and place line items data in the additionnal fields then exporte the file as CSV type. Just creating the fields would be quite a job! Don't you Agree?

Regards

Serge
  
When all you have is a hammer, everything starts to look like nail!
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing from FileMaker Pro
Reply #5 - Jan 3rd, 2004 at 5:25pm
Print Post Print Post  
Quote:
On top of my head, I would have to create 200 additionnal fields in the Order Database then loop through the line items database and place line items data in the additionnal fields then exporte the file as CSV type. Just creating the fields would be quite a job! Don't you Agree?

I'm not sure how FileMaker does things. In Sesame and in MS Access, if you do an export that includes fields from both the parent and child records, it creates an export file with one line for each child, where the parent information is repeated on each line. This is exactly what Sesame wants to import. I don't know FileMaker well enough to know what you need to do to produce this type of output. 

For example, if you have an Invoice #1005, with 5 related product line items, both Sesame and Access will easily produce the following export file:

"1005","ProductA","5"
"1005","ProductB","10"
"1005","ProductC","2"
"1005","ProductD","1"
"1005","ProductE","8"

Note how the Invoice number is repeated on each record. Can FileMaker produce output from related records in this way?
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
jondolar
Junior Member
**
Offline


Keep Trying

Posts: 57
Location: Montreal Canada
Joined: Dec 25th, 2003
Re: Importing from FileMaker Pro
Reply #6 - Jan 3rd, 2004 at 6:43pm
Print Post Print Post  
Hi Erika

You know after using/developping in FMP for 7 years, I somehow, never had to export data. Looking at the FMP help file, what you suggested is perfectly feasible i.e merge line item records with order records in a unique CSV file. Thank you for insisting.

May I suggest that someone from your team have a close look at FMP. I understand that your target group is Q&A users, but I estimate the user base of FMP an order of magnitude larger tham Q&A users and represent a tremendous potential for Lantica. Many FMP developpers are frustrated by the lack of a proper programming language to work around FMP deficiencies.

The only weakness I spotted so far in Sesame, is the HTML format used for reports..."Page header and page break may not behave predictably" (page 342-343). This could be a problem with invoices and monthly statesments.

Thanks again and good luck Wink

Regards

Serge
  
When all you have is a hammer, everything starts to look like nail!
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing from FileMaker Pro
Reply #7 - Jan 3rd, 2004 at 6:57pm
Print Post Print Post  
Quote:
May I suggest that someone from your team have a close look at FMP. I understand that your target group is Q&A users, but I estimate the user base of FMP an order of magnitude larger tham Q&A users and represent a tremendous potential for Lantica. Many FMP developpers are frustrated by the lack of a proper programming language to work around FMP deficiencies.

Thanks for the advice. You make a good point. Fortunately, several members of the team have done work with FileMaker. I'm just not one of them. Wink  My expertise is in other programs and areas.

Quote:
The only weakness I spotted so far in Sesame, is the HTML format used for reports..."Page header and page break may not behave predictably" (page 342-343). This could be a problem with invoices and monthly statesments.

We're waiting to see how this one shakes out. HTML is weak when it comes to the concept of a "page", but provides some great strengths in other areas. Considering the wealth of options available with merge documents and the SBasic print commands (if pages are a priority), we think the benefits, in the area of reports, far outweigh the cost. We'll see how it goes.  Grin Grin Grin
  
- Hammer
The plural of anecdote is not data.
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge