10 Replies Latest reply on May 5, 2011 12:55 PM by philmodjunk

    Newbie scripting/relationship questions

    MatthewRomberg

      Title

      Newbie scripting/relationship questions

      Post

      Hopefully there is an easy fix to my question.

      I'm building a database to record finishes for sailboat racing.  A lot of our racing is based on a handicap system where each boat has a performance rating based roughly on how fast it can sail a nautical mile.  I've created a table called "Boat" with fields like sail number, boat class, owner name, and the rating mentioned previously.  

      So, when a boat finishes a race, the trick is to identify the correct boat with the correct rating.  The first identifier used to record a finish is the boat's sail number.  I set up a "Finisher" table with fields including finish time, sail number, boat class, etc.  The problem is that there can be several boats with the same sail number, each from a different class.  What I was hoping to do was enable the user to enter a finisher's sail number, e.g. "123" which would then trigger a script to find all the boats in the "Boat" table with sail number "123".  I then would need some way for the user to select the correct boat with sail number "123" from the found set.  That selected "Boat" record would then populate the "Finisher" fields.

      I'm trying to keep the "Boat" table as a reference that cannot be modified by the user.

      This is making my brain hurt just trying to ask you guys the question!  Thanks in advance

        • 1. Re: Newbie scripting/relationship questions
          philmodjunk

          This sounds like something that can be solved with a conditional value list.

          You select the sail number in field 1 and then a drop down in field 2 lists only vessels in your boat table with that sail number. Once you've selected a vessel in field 2, you can display data from the Boat table by adding fields directly to the finisher layout. That sound like what you want here?

          Here are some links that may help:

          Forum tutorial:  Custom Value List?

          KnowledgeBase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          • 2. Re: Newbie scripting/relationship questions
            MatthewRomberg

            Thanks for the reply!  That helped a bunch.  However, I'm still stuck a bit.  I was able to follow the steps in the knowledgebase article and set up the relationship.  I'm able to select the sail number in field 1, then field 2 is correctly listing all the vessels with the sail number from field 1.  The problem arrises from getting another field value from the Boat table after selecting the correct class.  I'm trying to place the boat's performance rating in the finisher layout along with the sail number and boat class.  If the sail number is unique, the correct rating is displayed in the layout.  However, if, for example, there are 2 types of boats with the same sail number, the rating displayed in my finisher layout doesn't necessarily correspond to the boat class and sail number.  It seems as though the rating that is showing up is simply the first rating of the boats identified by the sail number entry.

            • 3. Re: Newbie scripting/relationship questions
              MatthewRomberg

              I think I answered my own question.  I was able to modify the relationship between the Boat and Finisher table to ensure that both the Finisher Sail Number and Finisher Rating were related to the Boat Sail Number and Boat Rating.  

              Thanks again for the help!

              • 4. Re: Newbie scripting/relationship questions
                philmodjunk

                You need a separate relationship so that you have one for your conditional value list and one on which to use so that you can add yoru rating.

                The first thing you need is a way to reliably identify the boat record you want to select.

                From your conditional value list setup, it would seem that Field 2 lists values from a field in the boat table that unqiuely identifies each boat. If so, you need to add a table occurrence of Boats so that you can add another relationship to it based on field 2:

                Finshers::Field2 = SelectedBoat::Field2

                To create SelectedBoat, you select Boat in Manage | Database | Relationships, then click the button with two green plus signs to create a new table occurrence of it. You can then double click this new occurrence box to open up a dialog box where you can rename Boat 2 to SelectedBoat if you want.

                Then add the rating field from SelectedBoat to your layout.

                • 5. Re: Newbie scripting/relationship questions
                  MatthewRomberg

                  Well, that didn't work.  When I added the 2nd relationship between the tables, the boats' rating corrected themselves.  But when I then try to add another finisher, I can select the sail number but "no values defined" comes up in the Class field.  

                  • 6. Re: Newbie scripting/relationship questions
                    MatthewRomberg

                    I'll try your previous suggestion.  Thanks for your input.

                    • 7. Re: Newbie scripting/relationship questions
                      philmodjunk

                      You need two relationships, one for the conditional value list and one to display the related data from the boats table after you have made your selections.

                      If "table occurrence" is a new term, you may want to read this thread: 

                      Tutorial: What are Table Occurrences?

                      • 8. Re: Newbie scripting/relationship questions
                        MatthewRomberg

                        You are awesome!  That did the trick.  Thanks again!

                        • 9. Re: Newbie scripting/relationship questions
                          MatthewRomberg

                          I'm back with another scenario.  Some of the scoring I'm trying to do is simpler than the above -- recording finishes in an order only by sail number and class.  I have the race table (race number, class, date, etc.), then a related finisher table (class, sail number, position) and finally the Boat table (class, sail number, email, etc.).  I was trying to place a portal in the Finisher layout that showed all the records from the Boat table, and was hoping to set up triggers so that the user could select the portal row with a mouse click (or touch on FM Go) of the finishers in the order they finished.  Selecting the portal row would perhaps automatically populate the "Finish position" field and trigger some conditional formatting to indicated the boat has been scored.  

                          What I can't figure out is how to populate the portal rows automatically.  I have a field in the Race table called "Class", and I'm trying to populate the portal with records from the Boat table based upon the Class designation.

                          Any help is much appreciated.

                          • 10. Re: Newbie scripting/relationship questions
                            philmodjunk

                            Don't know what table your "finisher" layout is based on, but you can use X instead of = in your relationship if you want all records in the boat table to appear in the portal.

                            Finisher::anyfield X Boats::AnyField