lookup one-to-many

[KHaber]

Hi all...I have not worked with Filemaker for about 12 years now...and I am trying to build a new database which seems simple on the surface...but I've already run into my first problem.

I have a File (PHARM) which contains (2) fields:

PHARMACOLOGICAL ACTION (text)

HERB NAMES (text, repeating 15)

Please note: one ACTION can be performed by many HERBS

I have another File (HERB) which contains (2) fields:

HERB NAME (text)

PHARMACOLOGICAL ACTIONS (text, repeating 15)

Please note: one HERB can perform many ACTIONS

I intent to populate PHARM and then want HERB to be able to lookup all of the possible PHARMACOLOGICAL ACTIONS matching from the PHARM file...

LOOKUP seems limited in that it will not bring in ALL of the ACTIONS linked to each HERB if I use repeating fields.

Any suggestions ??

Thanks much.

[T-Square]

Welcome.

In the many years since you've worked with FM, the program has made the huge transition to become a relational database. As a result, the use of repeating fields is only advised for VERY specific needs.

You should restructure your database so that:

a) each table has a unique serial number ID field,

neither table has the opposite repeating field, and

3) you use a "join table" to allow the many-to-many relationship you describe. I recommend you read up on join tables, either here, or in one of those big fat books on database design that you can buy.

HTH,

David

[Søren Dyhr]

if I use repeating fields.

...and the reason for this is? Have you been on version 2.1 all these years?

--sd

[KHaber]

OH WELL...I haven't been on version 2.1 all these years...just been away from FM altogether. I know there are sooooo many new possiblities with the relational db.

Here is my situation...

I have to keep track of:

PROGRAMS

FORMULAS

HERBS

ACTIONS

A Program can have multiple Formulas

A Formula can belong to multiple Programs

A Formula can have multiple Herbs

An Herb can belong to multiple Formulas (and Programs)

An Herb can have multiple Actions

An Action can belong to multiple Herbs (and Programs and

Formulas)

I want to be able to find and sort by any of the Fields...

e.g. - Program "A" contains which Formulas

or Program "A" contains which Herbs

or Program "A" has what Actions

or Formula "1" belongs in which Programs

or Formula "1" contains which Herbs

or Formula "1" has what Actions

or Action "a" belongs to which Herbs

or Action "a" belongs to which Formulas

or Action "a" belongs to which Herbs

So how do I set it up

4 different files ??

THANK YOU !

[T-Square]

Table Programs

ProgID, Autoenter serial

ProgramName

...

Table FORMULAS

FormulaID, Autoenter serial

FormulaName

...

Table HERBS

HerbID, Autoenter serial

HerbName

...

Table ACTIONS

ActionID, Autoenter serial

ActionName

...

Join tables:

Table ProgramFormulas

ProgramID, number

FormulaID, number

...

Table FormulaHerbs

FormulaID, number

HerbID, number

...

Table HerbActions

HerbID, number

ActionID, number

...

Relationships connect the fields whose names match. Set the relationships to allow creation of related records. Also check the delete related records box for the appropriate table (e.g., you'll want to delete records in the join tables when you delete either one of the parent records).

With this structure, a layout built on Programs will allow a portal built on ProgramFormulas that shows all the related formulas. A layout built on Formulas will allow a portal built on ProgramFormulas that shows all related programs and a portal built on FormulaHerbs that shows all the Herbs related to the formula.

And so on.

David

[Søren Dyhr]

So how do I set it up

4 different files ??

Why files, when tables could facilitate the same?

--sd

[KHaber]

Hi all...I have not used Filemaker in many years (about 13) but I loved what it was capable of back then. I am trying to set up a LIBRARY database that has many-to-many relationships...and I keep running into problems.

Here is my criteria...and what I tried to do so far:

btw...I set up fields for serial ID#s but I'm not really using them

FILE #1

Table: PROGRAMS

Fields: Program ID, Program Name, Formula Name

-- There are currently 8 programs possible, each of which contains multiple FORMULAE (currently set up with a checkbox list from the FORMULAE Table see below)

Table: FORMULAE

Fields: Formula ID, Formula Name, Herbs

-- There are currently 45 FORMULAE, each of which contains multiple HERBS (currently set up with a checkbox list from the HERBS Table see below)

Table: HERBS

Fields: Herb ID, Herb Name

File #2 (not sure why I've made a separate File...it could just be additional Tables ?)

Table: HERBS

Fields: Herb ID, Herb Name, Therapeutic PropertiesActions, Reference Source, Reference Specific, Reference Data (container field)

--There are currently 160 HERBS, each of which has multiple THERAPEUTIC PROPERTIES (currently set up with a checkbox list from the THERAPEUTIC PROPERTIES Table see below)

Table: THERAPEUTIC PROPERTIES

Fields: Therapeutic Property, Therapeutic Action, Therapeutic PropsActions (calc. field of above two)

I think I need to put INFORMATION REFERENCES in its own Table, because I must track multiple INFORMATION REFERENCES for each HERB.

Each INFORMATION REFERENCE will have:

1. REFERENCE SOURCE (a book or website) this is a finite list which could expand

2. REFERENCE SPECIFIC (page # or url)

3. REFERENCE DATA (a picture- jpeg or pdf)

Is it possible to relate these fields when they are in a checkbox lists or does every PROGRAM need to have multiple records to show each separate FORMULA...and every FORMULA have multiple records to show each separate INGREDIENT and every INGREDIENT have multiple records to show each THERAPEUTIC PROPERTY ?

If so...is there a calculation or export command to produce separate records from one showing multiple checkbox choices?

Hope this makes some sense to someone...I am going in circles trying to set this up...HELP !!

Thanks, Karen

Edited February 9, 2009 by Guest

[KHaber]

HELP...Newish user trying to set up many-to-many database...I'm going in circles ! [ NEW ]

I know that Filemaker is the right solution for my database, but I have not used FM since 1996 and am having some trouble figuring out the concept of JOIN TABLES and PORTALS

I need to track the following in regards to HERBAL SOLUTIONS

PROGRAMS -- Each Program consists of several FORMULAS

FORMULAS -- Each FORMULA consists of several BOTANICALS

BOTANICALS -- Each BOTANICAL has several THERAPEUTIC USES

THERAPEUTIC USES -- Each THERAPEUTIC USE has several SOURCES

SOURCES -- For each SOURCE I need to track: (A) SOURCE NAME (a book, organization, or website)

(: SOURCE REFERENCE (page #s or specific URL)

© SOURCE DATA (a jpeg or pdf)

I believe I set up:

Table

PROGRAMS: Fields -- Program ID and Program Name

Table

FORMULAS: Fields -- Formula ID and Formula Name

Table

BOTANICALS: Fields -- Botanical ID and Botanical Name

Table

THERAPEUTIC USES: Fields -- Therapeutic ID and Therapeutic Description

Table

SOURCES: Fields -- Source ID and Source Name and Source Reference and Source Data

I don't know how to set up the JOIN TABLES or how to use PORTALS to link the information.

Any help would be soooooooooo appreciated.

Edited February 12, 2009 by Guest

[comment]

This seems to be the fourth thread you have opened with the same issue:

http://fmforums.com/forum/showtopic.php?tid/200977

http://fmforums.com/forum/showtopic.php?tid/201111/

http://fmforums.com/forum/showtopic.php?tid/201190/

[RalphL]

The attached file may be useful. It was made to be tutorial on many to many relationships.

ManyToMany.fp7.zip

[LaRetta]

Hey, Karen, if you don't understand someone's suggestion on a post, it works better to post back on THAT thread and work through it with them. This also allows others the benefit from what has already been discussed so we don't repeat the same suggestions. I am going to merge your topics into one; no problem this time.

LaRetta