14 Replies Latest reply on May 5, 2017 7:12 AM by muzz

    Do I need a portal for this?


      I have a basketball scoring solution and I want to update it to include a record(s) of substitutions on and off during each game.  So far I have a table for the 10 player roster and have become confused over the best way to proceed. I have a layout with two portals, one for the players who are on and the other for the players who are off, with buttons to change the status and "move" them to the other portal. I will organise a script trigger to either create a record with the time on/off or add a line to a text log for each game (see attached).


      I like the look of this but am not convinced it is the best way to go. I also can't get my head around portals easily and so far this resides in the roster table but probably (I think) really needs to be in a separate table with records generated related to each game. I'm pretty sure there is a simpler way.


      I'd appreciate any input. The more I think about it the more variations I can think of but would like the simplest I guess.



        • 1. Re: Do I need a portal for this?

          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!


          • 2. Re: Do I need a portal for this?

            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.

            • 3. Re: Do I need a portal for this?

              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

              • 4. Re: Do I need a portal for this?

                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.


                • 5. Re: Do I need a portal for this?

                  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.

                  • 6. Re: Do I need a portal for this?

                    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.

                    • 7. Re: Do I need a portal for this?

                      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.

                      • 8. Re: Do I need a portal for this?

                        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!


                        • 9. Re: Do I need a portal for this?

                          Are you trying to keep track of how much time a player play? Are you trying to keep track of multiple teams across multiple game. Each of these issues adds complexity to your design.  Another option to consider is to use only one portal were the active players sort to the top.



                          • 10. Re: Do I need a portal for this?

                            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.


                            Regards, Murray

                            • 11. Re: Do I need a portal for this?

                              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.

                              • 12. Re: Do I need a portal for this?

                                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 !

                                • 13. Re: Do I need a portal for this?

                                  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.



                                  Roy Gordon


                                  Sent from my iPhone

                                  • 14. Re: Do I need a portal for this?

                                    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.