4 Replies Latest reply on Jan 29, 2013 9:51 PM by SoftailDeLuxeGirl

    I have an old Excel formula that I need to convert or replace to work with Filemaker

    SoftailDeLuxeGirl

      Title

      I have an old Excel formula that I need to convert or replace to work with Filemaker

      Post

           I have an old Excel formula: =IF((ISNUMBER(SEARCH("1",REGISTRATIONS!W5)))+(ISNUMBER(SEARCH("2",REGISTRATIONS!W5))),REGISTRATIONS!N5,"")

           Basically what it did was take a look at my Registration page, and when the Competitor signed up for Round 1, a "1" (online signup) or "2" (walk-up signup) was entered into the "W5" field, and it automatically copied the "N5" (Competitors Name) field contents from the Registrations page info over to "Competitors Name" field on the the "Round 1" page. 

           The "Round 1" page turns into a running order of competitors participating in Round 1, after all of the registrations are in.

           The function or formula was in every field that I needed copied over, i.e. competitor name, age, sex, from city, from state, etc.

           My Filemaker database is setup where the Registrations table is a join table between Competitors and Events. The fields in it are: Round 1, Round 2, Round 3, etc. up to Round 25, total, amount paid, balance.  Each field contains the value list of "Signup Type" (Online, Walkup, Special), and then it calculates their total $.

           I have a "Competitor Scores" table child to the "Competitors" table that has fields for the scores for each Round.  Each Round has 2 races, and it also has fields that calculates the best score of the 2 in the round, fields that calculates and keeps a running "best score" of the whole day.

           I have a portal to Competitors Scores on the Events table. I setup Tabs so that each round is on it's own tab. At an event, we'll use the portal to record scores because it shows all of the competitors in the round, and should match what we print out for the official to hand write the scores as they come in.

           Currently, if I go to the Registrations table, select a Competitor, then select an Event (CompetitorID and EventID is recorded to Registrations table), I have set it up so that they automatically get put on my portal on the Events table, but they don't show up on the Scores table until I enter scores in the portal on Events... My question being is there a validation calculation that could look at my Registration table, and if any competitor has "Online", "Walkup", or "Special" selected from the drop down custom value list, that it's the only way a field is available to enter a score on the portal in Events?  In other words, if they don't register for Round 1, they won't show up on the portal for Round 1 on the Events table.

           I'm not sure if this is a pipe dream or not, but Excel had that one thing going for it... if the Competitor wasn't checked off on a Round in the Registration page, their name didn't show up on the running order... which made entering final scores so much easier.

           My second thought is to just keep using Excel at the actual events, but I was really hoping to have an all-around solution.

           If anyone has any thoughts, I'm totally open... Maybe I need to re-work my whole schema?

           SO CONFUSED!!!!!!!!!!!

           Thanks!!!

        • 1. Re: I have an old Excel formula that I need to convert or replace to work with Filemaker
          philmodjunk

               The fields in it are: Round 1, Round 2, Round 3, etc. up to Round 25

               That suggests changing to a more flexible design where you have 25 records with one such field instead of 25 individual fields in one record.

               

                    if the Competitor wasn't checked off on a Round in the Registration page, their name didn't show up on the running order.

               This is entirely possible in FileMaker, but the implementation details will be quite different in FileMaker. As a relational data base, data such as a competitor's name should only be stored in one place, the competitors table, but the relationships you set up will enable you to display that name whereever you need to on your layouts without physically copying the name from one field to another.

               Your basic "backbone" of your relationships seems sound:

               Competitors----<Registration>----Events

               But if each event is comprised of multiple rounds, you may want a separate, related table for each round. Depending on what this round represents, the same record might record scoring for that round or you may need a related table to record the scores for that one round.

          • 2. Re: I have an old Excel formula that I need to convert or replace to work with Filemaker
            SoftailDeLuxeGirl

                 All of the events happen in different city.  Right now there are about 60 different events per year, at 60 different Venues, but it's ever increasing.  The events have any where from 3 to 25 Rounds.  Some competitors show up at 10 events or more per year.  A Round typically has an average of 25 Competitors.  They run twice in each Round, and even though both scores get stored, we take the best score of the 2 from each round, and keep a running tally of who's in what place for each event.

                 That said, a couple more questions, if you would be so kind...

                 So if I make a table for each Round, do all "Round 1's" from across the USA fall under the Round 1 table? Or do you think I should have a seperate table for each event as well as a seperate table for each Round? 

                 And then that's again where I get corn-fused (HAHA), I can't wrap my head around that, and then where to go with Scores.  Are they still in their own table?  Or would it be better if they were stored in each Round. 

                 I'm thinking that putting the Scores in the with the Rounds would make sense, but please correct me if I'm wrong because I'm super green!

                 Essentially, in the table called "Round 1", the record fields would show be EventID, CompetitorID, First Run Score, Second Run Score, Best Run Score, Best of Event Score.  Oh and based on the calculation (?) that places the right Competitor in the right Round, right?

                 PHILMODJUNK FOR PRESIDENT!!!

                  

            • 3. Re: I have an old Excel formula that I need to convert or replace to work with Filemaker
              philmodjunk

                   So if I make a table for each Round, do all "Round 1's" from across the USA fall under the Round 1 table?

                   All Rounds, 1-25 for all venues would reside in this table.. Do not use a different table for the Rounds from different venues as this will be very, difficult to work with in your database.

                   If scores for a given round are always two scores to a round, you can have two fields for each score in the round table. A related table could be used--with two related records for every score, but I doubt it would be worth it given only two scores to a round.

                   The fields in Rounds (NOT round 1) would be:

                   CompetitorEventID, firstRunScoe, secondRunScore, Round, BestRunScore ( defined as max ( firstRunScoe ;secondRunScore ) and BestOfEventScore defined as a summary field that takes the maximum of BestRunScore.

                   There will be no calculation that places the right competitor in the right round. Your relationships will do that for you:

                   Competitors---<Registration>------Event
                                                 |
                                                 ^
                                           Rounds

                   Competitors::__pkCompetitorID = Registration::_fkCompetitorID
                   Event::__pkEventID = Registrtion::_fkEventID
                   Registration::__pkRegistrationEventID = Rounds::_fkRegistrationEventID

                   If this notation is unfamiliar, see: Common Forum Relationship and Field Notations Explained

                   Additional relationships using additional occurrences of these tables could be used, but just from the above, you can use a portal to Rounds from a Registration based layout to record scores for all rounds in which a competitor participates.

              • 4. Re: I have an old Excel formula that I need to convert or replace to work with Filemaker
                SoftailDeLuxeGirl

                     Once again, you are the man!!!

                     THANKS!!!!