I have an old Excel formula that I need to convert or replace to work with Filemaker
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?