3 Replies Latest reply on Mar 4, 2009 8:09 AM by TSGal

    allow creation of records in a related field



      allow creation of records in a related field




      I really need help trying to figure out why my join tables help me create new records in one place but not another.


      If someone is willing to look at this for me, I can email my file.  It's really small.


      I have a lot of many-to-many relationships and then to complicate it...a set of records which are created by concatenation.


      I attached my file into a POST on http://fmforums.com/forum/showtopic.php?tid/201481/


      because that FORUM allows attachments.



       Based on the attached file...

      When I create a new record on the LAYOUT "Forumlae" it adds a record to the TABLE "FormulasItems", but I need a new record (or records) added to FB Combo.

      It seems like it should happen...but it does not ?

       Attachment: herbalsolutions2-27-09__1.fp7.zip (59.48 KB) 0 View(s)

        • 1. Re: allow creation of records in a related field



          Thank you for your post.


          Looking at your Formulae layout, you have a portal into the "FormulaItems" table, and it allows the creation of new records.  There is no portal into "FB Combo", so no records will be added.  You may want to add a portal on your layout that links to the "FB Combo" table.  Be sure to turn on the ability to create records in FB Combo.


          If you want this done automatically without adding the portal, then you may want to create a script which does this for you.  If you have FileMaker Pro 10, then this can be set up through a script trigger.  For FileMaker Pro 9 and earlier, you can instead use a button to execute the script.



          FileMaker, Inc.

          • 2. Re: allow creation of records in a related field

            TSGal -- Thank you so much for your reply...

            1st question -- Can I have two different portals on one layout ?


            But...also I have had a couple of people tell me that there is no need for the TABLE: FB Combo

            and I am not exactly sure why I created it.  I am kind of afraid to pull it out though -- fearing that all my other links will be messed up.


            I think I have to back up a little bit though...and I could use your expertise to help me relate my database requirements to Filemaker's structure. 


            While waiting to solve the issue of adding/updating records, I tried to create the final 2 levels of my project.

            In a series of MANY to MANY relationships, I am linking:




            Each "Project" has many "Formulas" (each "Formula" can belong to many "Projects").

            Each "Formula" has many "Botanicals" (each "Botanical" can belong to many "Formulas")


            These parts of the database work fine.


            Now I need to record "Therapeutic Uses" to  each Botanical BUT -- and here's where "FB Combo" got created...

            The "Therapeutic Use" belongs to a "Botanical" as used in a specific "Formula".

            My thought was that if I didn't create the "FB Combo" field by calculation, then I could not get the path correct.


            Then- my FINAL step is that I need to record the "Source Data" for each "Therapeutic Use" as it relates to the specific "Botanical" as it relates to a specific "Formula".


            I laid out the following explanation for my supervisor so that we could see exactly where what we were trying to accomplish.  I hope it is clear enough to explain my question.


            I need to research and record SOURCE data (either from Books, Journals or Websites) which substantiates claims of certain THERAPEUTIC USES produced by certain BOTANICALS which are ingredients in FORMULAS created by the company.  Some of the FORMULAS are part of PROGRAMS which people follow to achieve a specified result.   Some of the FORMULAS are not a part of any PROGRAM and most any FORMULA can be used on its own...whether connected with or NOT connected with a PROGRAM.

            THE GOAL in relation to using FILEMAKER is to have a database in which to record my  SUBSTANTIATION DATA.

            A real world example (for my work) would be:

            The FORMULA "Fem+" contains BOTANICALS  "Dong Quai Root", "Lobelia Pod" and "Uva Usi Leaf"
            One THERAPEUTIC PROPERTY of "Dong Quai" is "Balancing Hormones"  
            One THERAPEUTIC PROPERTY of "Lobelia Pod" is "Anti-Spasmodic" 
            One THERAPEUTIC PROPERTY of "Uva Ursi Leaf" is "Diuretic"  

            There is a SOURCE website  "National Institutes of Health" which shows corroborative information on the "Dong Quai" claim. -- I will need to show multiple SOURCES of substantiation for each BOTANICAL.

            There is a SOURCE book "Herbal PDR" which shows corroborative information on the "Lobelia Pod" claim.

            There is SOURCE website "Department of Agriculture" which shows corroborative information on the "Uva Ursi" claim.

            So...in the end, I need my database to be able to  show a report that specifically links

            SOURCE "Department of Agriculture" to the "Diuretic" property of "Uva Ursi" as specifically used in "Fem+"

            or looking at it the other way....

            "Fem+" contains "Uva Ursi" which is a "Diuretic" and this information is corroborated by the "Department of Agriculture" website.

            And, of course...each PROGRAM can have multiple FORMULAS which can have multiple BOTANICALS which can have multiple THERAPEUTIC USES which will have multiple SOURCES of substantiation.....so MANY to MANY relationships...

            I will be able to use overlapping/repeating SOURCE data for many of my records, but I have to keep it all in its own separate records as noted above.


            I can post my latest version of the database on http://fmforums.com or I can email it to you directly.


            I AM SO STUCK RIGHT NOW...and AMAZINGLY FRUSTRATED that I am so close...


            BTW...you will see that in the current version of the database I was attempting to make it easier by linking both the SOURCE and the THERAPEUTIC USE to FM COMBO instead of the SOURCE following the THERAPEUTIC USE...but that really doesn't accomplish the path as I explained it above.

            • 3. Re: allow creation of records in a related field



              Yes, you can have two different portals on one layout.


              It appears that you have a many-to-many relationship between Project and Formulas, as well as between Formulas and Botanicals.  If you are going to specific instances for each combination, then you need a third table, and it sounds like FB Combo is one of them, which would be correct.  Here, you can enter a specific Project with a specific Formula.  If that Formula requires a specific Botanical, then you need to link that in, too.


              I assume "FB Combo" shows the different FORMULA and BOTANICALS.  From your examples, I see the three records for Fem+ containing "Dong Quai Root", "Lobelia Pod" and "Uva Usi Leaf".


              You talk about "One THERAPEUTIC PROPERTY" of "Dong Quai" is "Balancing Hormones".  If there are multiple properties, then you need a table to connect the BOTANICALS with the THERAPEUTIC PROPERTY".


              Also, it appears your SOURCE table is linked to your BOTANICALS table.  Correct?  Then again, you say, 'Herbal PDF" which shows corroborative information on the Lobelia Pod claim."  Are you trying to link this to the Therapeutic Property or to the BOTANICAL?  Or both?  That is the relationship I don't understand right now.


              Other than that, it sounds like it is set up properly.  Maybe the better answer is "What isn't displaying properly?"



              FileMaker, Inc.