Jump to content

newbie: many-to-many w/ join and fmp complexity


This topic is 7386 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

Hello,

I'm new to this forum and new to fmp7 (or any filemaker for that matter) and

have been called in to a project using fmp7 that is fairly involved. I have a

reasonably extensive background w/ database programming using sql and

java.

two questions to the fmp community:

1) one of the main difficulties I've come across can be expressed by

the question "how does one manipulate data in a many-to-many

relationship, even in the presence of a join table?" For instance,

consider a "quotes" table that links to a "company" table (the company

for whom the quote is being developed), along with a "contact" table

linked to the "company" table (the people working at said company),

and finally a "phones" table linked to the "contact" table. This gives

an N:1 quotes->companies, a 1:N company->contacts, and a 1:N

contacts->phones. With "quotes" being the "main" page for layouts

(every fmp advice I've seen says to make "company" the main page based

on these relationships, but that would only move the same problem

elsewhere), is it possible to display and modify the relevent phone

numbers on the same page?

With sql queries this is no problem, but as I understand fmp7, I don't

have access to sql...

2) The overall project is rather complex. It has, at present, 23

tables in the normalized schema (and I'm sure there will be more).

Since tables such as "company" and "contact" are used repeatedly, the

number of table occurances has grown very quickly. If one wishes to

use a value list for selection on the same base table, but for

different relationships, it seems as though one must create different

value lists. It is starting to seem as though the complexity imposed

by creating so many different value lists and table occurances will

quickly grow out of control. My question then is this: is fmp up to

the task? Should I instead try to lead the project down a

vb.net/sqlserver (for example) path? The project is in its infancy so

there's no solid tie yet to using fmp.

any/all input welcome.

thanx,

-don.

Posted

May I susgest that you look at Separation Model Many to Many Relationship in the Separation Model Forumn. Look at the last set of attachments.

A TO can have more than relationship a new TO is required when a new relationship would make a closed path.

All Value lists do not require a relationship.

I think FMP7 can do the job, but you are not going to be confortable with it for a while.

Posted

...is it possible to display and modify the relevent phone

numbers on the same page?

Welcome don!

I assume you mean edit contact's phone numbers on a Quotes layout. Yes, you could have a Phone Number portal on the Quotes table, but you will see all phone numbers for all contacts. If you want to filter the phone numbers you see by Contact, then add a global gContactID to the Quotes table and relate to a separate table occurance of Phone Number by

Quote::gContactID = Phone Number::ContactID

You will probably need a value list to allow users to just pull up the related ContactIDs for the given Company.

This topic is 7386 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.