4 Replies Latest reply on Oct 19, 2012 8:59 AM by JimMac

    Repeating field or portal?

    lemmeadam

      Title

      Repeating field or portal?

      Post

           I've been away from FileMaker for a while and am having a tough time figuring out a solution to my issue:

           I have two databases in my table, Students and Sponsors.  For the students, I want to capture their interests (e.g. medicine, engineering) and for the sponsors I want to have industry keywords (e.g. nursing, medicine).  The goal is to be able to match student interests with sponsor keywords through a script.

           What I can't figure out is whether to have Students::Interests be a repeating field OR set up Student Interests as portal rows via a separate table (and the same for Sponsors/Keywords).  The key issue is that I want to maintain the possibility of having one interest of many match one keyword of many.

           What is the best way to handle?

        • 1. Re: Repeating field or portal?
          JimMac

               I would suggest a separate table for keywords.  That way no spelling errors and commonality is achieved.

               But...

               You may want to do a simple List in one table and share it with the other table.

               Separate keyword table  allows sorting and easy of change.

               Jim...

          • 2. Re: Repeating field or portal?
            lemmeadam

                 Thanks Jim--I was leaning towards a separate table as my solution.  However, I'm unsure how to construct the script.  

                 It would have to create a multiple-request search of the Sponsor::MasterSearch field* based on all the records of Student Interests::Interests that are related by StudentID.  Is there a more straightforward solution than to have it loop through using "Go to Portal Row?"

                 *Sponsor::MasterSearch is a calculation field that includes the sponsor name, a brief description from another field, and an aggregate of all the records from Sponsor Keywords::Keyword

            • 3. Re: Repeating field or portal?
              philmodjunk

                   And a keyword list describes a many to many relationship. One keywork links to many persons and one person links to many keywords. This will require a join table to link keywords to people (whether they be students or faculty--which could possibly be listed in the same table.)

              • 4. Re: Repeating field or portal?
                JimMac

                     I digress a bit to point out the power of FMP that I learned from following PhilModJunk and other robust helpers [too many to mentionangel] in this forum.  

                     I grew up using loops and matrix models and thus the first thought of using Repetive fields to find a match.  Now I allow FMP to do the "matching",  You would think Matrix... Students on rows and Sponsers on columns (spread sheet style) and look for where the row matches the columnssmiley, right?

                     Wrong...enlightened

                     You build Tables of [in your case words] for eachStudent and Table of [in your case words] for each Sponser and let FMP do the looping and "If statements" to Match them with "Find's".  It is much faster since FMP is machine coded and not scripted.

                     This doesn't mean you won't Loop or use Repetive fields, in fact sometimes PhilModJunk and others "forget" the power of Repetive fields in favor of Tables, because there is always a "work around" using Tables.

                     Back to the point...

                     The intial setup of Tables and Relationships is the key for you before diving into a script.  Phil, is so fast and patient, he will guid you to set up your Tables.  This is a very interesting problem I will follow with great interest.yes

                     Jim...