do you need to "track" those substitutions in some way for a game? if so, perhaps a join table of some kind (who went in for whom). And maybe that is what you meant by "script trigger" (to create the swap)?
I think it may be a matter of what you need to display. A couple "filtered" portals (on's and off's) may be ok. Or a single row with a highlight (green = on) of the entire roster, perhaps only sorting the list as the game progresses?
Whatever, the display, the trigger to track the swaps should be needed!
I think you know this, but just in case ... A portal is a (very) nice way FMP lets you show the "Many" side of a One-To-Many relationship.
So, if you can say that "one of these has one or more of those", then the "those" could be in a portal if you want that many-side display.
CUSTOMERS-->ORDERS (Orders would be in a portal, for example, since one customer could have one or more orders)
Portals are really nice and easy to work with, but if you have lots and lots of data (millions of rows or maybe less) a portal, for me is too slow. In that case, you can use other methods like the native Find.
Others will have ideas, but the FMP portal is extremely cool and useful for me. I have them everywhere.
If you post an ERD of your database, it will be easier to understand the data you're describing above.
HOPE THIS HELPS.
Thanks for both of your replies. For some reason the file didn't attach so I've just attached it. Each time the player subs on or off, I want to know the time of the event, and at the end of each game have a log or a list of event records, either of which can be controlled by scripts.
My main question is related to my fundamental problem organising myself around where the portals should reside - at the moment they are in the table with the roster but the roster of players will be the same all season so I will need a new set of data for every game, so maybe they should be on a layout in the game table. That's where my lack of knowledge on properly using portals and database design in general leaves me stuck.
I should point out that Im not a developer, just a dad mucking around making a scoring solution for his daughter's basketball games.
Thanks for helping. Cheers, muzz
ok, you only need one relationship and filter the portal(s) based on the status (on/off).
but you are correct! each game would have different substitutions. Your sample appears to be incomplete, but I'm sure you just posted part of your solution for us.
Thanks Beverly. I'm just trying to work this part out as a standalone and once I understand it I will try to get it to work with the rest of the solution.
I see you also made a self-join (status to status) and my suggestion of one relationship here with filters would be different based on other tables/relationships.
Yes, that's where I got confused. I had a separate table for the team roster and had a Status field in that table and related the Substitutions table via the status fields but then I thought that wasn't right and haven't got any further.
Make a list of rules that apply, something like:
teams have rosters (players)
games have teams for each game
the status of the players during each game changes
if you put this on paper, I'm sure you can come up with a list of rules that will help you design what to do with the database (tables and fields)
then there are tutorials and articles to help:
come up with a list for us to help you better!
Thanks rgordon, I am leaning that way. beverly also suggested this so I will give it another go tomorrow.
I'm only keeping records for one team, but would like these to be kept for each game so I can go back and compare from game to game.
Last but but not least, tracking an event (in this case a substitution) in an additional table with preferably also a "Why" (comment) field will help you in the long run to better focus on the problems of each individual player. You will be able to see on each player's record the substitutions that were made with him going out and the reason why.
I hope I have understood everyone's suggestions and now have a single TO for the portal, with 2 portals on the page with filtering for player on and player off respectively. Scripting for substitution on/off creates an event in a GamesSubsEvents table with timestamp, player name/number and time on the game clock that the player was subbed on or subbed off (the SubOnTime and SubOffTime are separate fields in separate records).
Just to be clear, the games are fully timed so the game clock stops when play stops and restarts when play resumes so timestamps in real time are not part of calculations for player "on court" time. The details in the GameSubsEvent use data from a GameTime field which is manually adjusted to match the scoreboard clock at the time of substitutions.
My next major step is to figure out how to have a "live" running total of each player's on/off court time, which means calculating the difference between their SubOn time and their SubOff time. As these are not really true times, I have used 2 fields - one for minutes, one for seconds. I'm hoping the maths will be straightforward. I am thinking calculating as part of a report at the end of the game will be relatively straight forward however I'm not sure how to get the information running live during ongoing play. I'm not even sure having separate events with SubOn and SubOff is correct, as I haven't tied the two times to the separate events in any way - perhaps an incremental number in a global variable or field relating to the player ID/number/name ?
Appreciation in advance for any further advice that can be given. I have posted the file so far if it helps - please forgive me for the mess, unfixed errors and untidiness within though, I still have a lot of learning and therefore tidying up to do.
Cheers, muzz !
Subs 050517.fmp12.zip 83.1 K
I did something similar to what you are trying to do and I can tell you it is hectic to keep track of the time. The fewer the interactions with the layout the better. Here are a few recommendations.
1. Before the game all player are on the bench so your bench portal needs to be able to scroll to see all player.
2. Get rid of the popover for the clock. Believe me you will be thankful later on if you do this. Put the clock directly on the layout. There is no need for the popover to open every time you sub for each player.
3. Turn the clock into one global text field. The pop up menu for minutes is very inefficient. I would set the field up with an auto enter calc that will insert a colon into the time. So if you type 123 it will auto enter to 1:23
I'm traveling through airports today so my access to the forum is limited.
Sent from my iPhone
Thanks rgordon. I was thinking the same thing so earlier today I did make the bench portal scrollable for that reason. I was also thinking that the same applies at the end of each period.
The only reason I have the clock in a popup is that it forces the user to remember to change the clock from the last entry. I was thinking that it would otherwise be easy to hit the subs button before changing the clock which would mean implementing a method to delete/undo/edit mistaken entries. I was thinking of nevertheless having a clock directly on the layout with buttons (up/down) which would increment the time more efficiently but still be reflected in the popup. I suspect i won't know which is best until I've used it for a while.
I think you are right about entry efficiency. I originally had keypad entry for the clock but one for mins and one for secs, so thanks for the suggestion which I have just had a go at implementing and is definitely a better option.
I appreciate your advice. Thanks again, muzz.