1 Reply Latest reply on Feb 13, 2009 6:14 AM by swj

    NEWISH USER needs HELP  many to many relationships, checkboxes, portals and join tables ???

    makita

      Title

      NEWISH USER needs HELP  many to many relationships, checkboxes, portals and join tables ???

      Post

      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?

       

      Also I am very confused as to how to set up JOIN tables and PORTALS.

       

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

      Thanks, Karen 


















        • 1. Re: NEWISH USER needs HELP  many to many relationships, checkboxes, portals and join tables ???
          swj
            

          Karen,

          I didn't see this before I replied to your other post. There I show how to set up portals. I'm glad that I found this post, as you detail more of where you are at. I think that this is how the relationships would look at the moment:

           

          Alternate relationship 

          Then I think that you are using check boxes to choose the items:

           

          Check boxes 

          Download sample from Alternate Method.fp7

           

          You say "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...etc?" The answer is YES, you can do it either way, but you would have discovered that when you have dozens or even hundreds of itmes, check boxes become impossible to use. Also, the other "join table" method can make it easier to print out reports, if that is what you eventually want to do.