Jump to content
Sign in to follow this  

lookup one-to-many

Recommended Posts

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:


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.

Share this post

Link to post
Share on other sites


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.



Share this post

Link to post
Share on other sites

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:





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


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 ??


Share this post

Link to post
Share on other sites

Table Programs

ProgID, Autoenter serial




FormulaID, Autoenter serial




HerbID, Autoenter serial




ActionID, Autoenter serial



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.


Share this post

Link to post
Share on other sites

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



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)


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)


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.


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 by Guest

Share this post

Link to post
Share on other sites

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



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:


PROGRAMS: Fields -- Program ID and Program Name


FORMULAS: Fields -- Formula ID and Formula Name


BOTANICALS: Fields -- Botanical ID and Botanical Name


THERAPEUTIC USES: Fields -- Therapeutic ID and Therapeutic Description


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 by Guest

Share this post

Link to post
Share on other sites

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




Share this post

Link to post
Share on other sites

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. :wink2:


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

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