1 2 Previous Next 22 Replies Latest reply on May 13, 2009 2:35 PM by philmodjunk

    Report layout with summaries

    Bauke

      Title

      Report layout with summaries

      Post

      Hello,

       

      I have a database with some related tables. In the manual I read that a summary field needs to be in the table of which it summarizes a field. But a report has to be based on one table, and from that report table I cannot reach the summary field in the other (related) table.

       

      There must be a simple solution to this but I cannot find it.

       

      Thanks for any help

       

      Bauke

       

        • 1. Re: Report layout with summaries
          philmodjunk
            

          A report does not have to be based on one table. You can place fields from any table on the same layout. The relationships you define in the relationship graph will control how the data from different tables will display on the same layout.

           

          For best results, set your layout to refer to the table that contains your summary fields, then select fields from related tables as needed.

           

          • 2. Re: Report layout with summaries
            Bauke
              

            hello Phil,

             

            I can reach all the related fields, but the not the summary fields. You know my tables. I want the sum of w_brtn (in wedstrijden) and the sum of car (in speler_wedstijden) on the same report. With a break on seizoen, ronde, and l_id.

             

            And both tables have there own summ. fields. Does that give any problems?

             

            I'll try again!

             

            Bauke

            • 3. Re: Report layout with summaries
              philmodjunk
                

              If this keeps up I'm going to be able to read and write Dutch! :smileywink:

               

              I can follow everything from your description except "Sum of w_brtn". I can see you want a summary report of all players, with their carambole totals (or other summary calculation) for each game type (ronde) broken down by season (seizoen). I'd base that report on your join table, speler_wedstijden as it has one record per player per game and put everything into subsummary parts and no body part.

               

              Is sum of W_brtn a total for the season, type of game or what?

               

              We can probably set up a calculation field that uses the sum function to compute that value.

              • 4. Re: Report layout with summaries
                Bauke
                  

                hello Phil,

                 

                it works! I've made a report with a lot of total. BTW I changed the r_wnr te reflect the total games played (this season) for every player. Along with the other totals it gives me the following report:

                 

                 

                 

                In my version I use the proper names. I even managed to sort it on one of the summary-fields. I think I slowly get to understand FMP!

                 

                Just some further problems:

                 

                In front of every row I need a ranking number (from 1 .. 13). I tried it with Get(RecordNummer) but that gave me a wrong number.

                 

                Then I would like to have this report in my 'Invoer Wedstrijden Portal' ( the one that took so long to build ). I tried this in a portal but didn't get the proper figures. I also made a new occurence from the speler_wedstr table (a self join only on seizoen) but that didn't do the job.

                It should be updated with every new committed game.

                Can I have a second portal on the existing layout without the relation to the player? 

                 

                It would be nice when a player could see almost directly after playing the game, what the new ranking list would be like. I would have to take my laptop with me, but that's what laptops are for.

                 

                Can you (or someone else) point me in the right direction, please?

                 

                Bauke

                • 5. Re: Report layout with summaries
                  philmodjunk
                    

                  Bauke,

                   

                  You can do this, but not by putting sub-summary parts into a portal. If we can set things up so that we have one record per row of your report, we can put it into a portal. Do each of these rows in your report represent totals for all players, for a specific game type (ronde) and season (seizoen)?

                   

                  I took another look at the DB file we've been passing back and forth and spotted a problem: speler_wedstr::ronde stores the name of the game type (same as ronde::r_speltype) but is matched to a number field m_rondeID in Moyenne. This will cause problems. Two changes needed to fix it:

                   

                  1. In the relationship graph, match speler_wedstr::ronde to moyenne::m_ronde
                  2. Change the value list definition of "ronde" to just specify ronde::r_SpelType

                  to get your standings list in a portal, you need yet another field.

                   

                  Define it PlayerStandings with the following key fields:

                  SpelerID (player initials)

                  Ronde (name not number)

                  Seizoen

                   

                  Now define the following relationships:

                  speler_wedstr::l_ID = PlayerStandings SpelerID AND speler_wedstr::l_Ronde = PlayerStandings::Ronde AND speler_wedstr::Seizoen = PlayerStandings::Seizoen.

                  Enable "All creation of records via this relation..." for the PlayerStandings Side of this relationship.

                   

                  Create a new Table Occurrence of this new table, PlayerStandingsPortal and set up the following relationship:

                  wedstrijden::w_seizoen = PlayerStandingsPortal::seizoen AND wedstrijden::w_ronde = PlayerStandingsPortal::ronde

                   

                  Return to field definitions and add calculation field for your summary totals to PlayerStandings:

                  Sum(speler_wedstr:: punten), for example will calculate the total score (punten) of all the games of a given type played by a specific player for a specific season.

                   

                  Use the following script to load your PlayerStandings table:

                   

                  Go to Layout [ “speler_wedstr” (speler_wedstr) ]

                  Show All Records

                  Go to Record/Request/Page [ First ]

                  Loop

                  Set Field [ PlayerStandings::SpelerID; speler_wedstr::l_id ]

                  Go to Record/Request/Page [ Next; Exit after last ]

                  End Loop

                  Go to Layout [ original layout ]

                   

                  You'll also need a script that automatically adds new players to the new PlayersStandings table. You can modify the existing script trigger script to be:

                   

                  #On Record Commit (Invoer wedstrijden Portal)

                  #Update Speler_Wedstr records to match any changes in wdstrijden

                  If [ Count(speler_wedstr::l_id) > 0 ]

                  Freeze Window

                  Go to Related Record [ From table: “speler_wedstr”; Using layout: “speler_wedstr” (speler_wedstr) ] [ Show only related records ]

                  Loop

                  Set Field [ PlayerStandings::SpelerID; speler_wedstr::l_id ]

                  Go to Record/Request/Page [ Next; Exit after last ]

                  End Loop

                  Relookup Field Contents [ speler_wedstr::SW_WedID_fk ] [ No dialog ]

                  Go to Layout [ original layout ]

                  End If

                   

                  Let me know if that works for you.

                  • 6. Re: Report layout with summaries
                    Bauke
                      

                    Phil,

                     

                    "Do each of these rows in your report represent totals for all players, for a specific game type (ronde) and season (seizoen)?"

                     

                    Yes, always for player and season. Sometimes for all types and sometimes for a specific type of game.

                     

                    I will make the mentiones changes.

                     

                    Thanks!

                     

                    Bauke

                     

                    • 7. Re: Report layout with summaries
                      philmodjunk
                        

                      "Sometimes for all types and sometimes for a specific type of game."

                      Sometimes for all types will be a problem. You'll need a separate relationship and portal to show totals for "all types."

                      • 8. Re: Report layout with summaries
                        Bauke
                          

                        Hello Phil,

                         

                        Almost there! I first did it the way you wrote. With your script the new table got loaded. Then I changed things a bit because I don't need the type (ronde) at once. So I stripped everything and threw ronde away. Made a new table speler_stand (player_standings) without the ronde field. Made all the relations you mentioned but without ronde.

                         

                        The new table didn't load, so I typed the record in. Two seasons, 13 players is easily done.

                        I now have the portal with the proper figure but one. The player_id! When I change the layout (new portal) to show the id from speler_stand_portal all is well.

                        When I change speler_id to leden::l_id I get portal rows with one player in every row but with the right numbers (the numbers of all the players).

                         

                        So there's something wrong with the relation (and that's also the reason the table didn't load with the script)

                         

                        I made another occurrence of leden, related it to speler_stand_portal and it works now.

                         

                        But I'm not sure it is as efficient as it could be.

                         

                        Here are the relations now and THANKS!

                         

                        Bauke

                         

                         

                        PS: How do I get the recordnr? and in the portal?  FOUND IT!

                         

                         

                        • 9. Re: Report layout with summaries
                          Bauke
                            

                          Hello again,

                           

                          Just found a little thing. When I enter a new game, the figures in the portal change the way they should. But the sorting does not. How and where do I add the (re)sorting for the speler_stand_portal table?

                           

                          Thanks

                           

                          Bauke 

                          • 10. Re: Report layout with summaries
                            philmodjunk
                               If you want a portal showing game stats for all players of a given season, without regards to type of game, (ronde), then you have the correct approach. My script won't work unless the "Allow creation of records via this relationship" option is enabled for the new table's side of the relationship.
                            • 11. Re: Report layout with summaries
                              Bauke
                                

                              Indeed, that's it.

                               

                              Thanks

                               

                              Bauke

                              • 12. Re: Report layout with summaries
                                Bauke
                                  

                                Hello all,

                                 

                                I'm still strugling with the mentioned second portal. The one with the stats of all players for this season.

                                After creation everything is correct. When I fill in another game then the figures are updated right, but the portal is not sorted right. I've been busy with this for a while but I cannot find the reason why the portal isn't sorted right.

                                 

                                Could surely use some help here!

                                 

                                Thanks in advance

                                 

                                Bauke

                                • 13. Re: Report layout with summaries
                                  philmodjunk
                                    

                                  How are you specifying the sort order for the portal?

                                   

                                  In the portal setup or in the relationship details of the Relationships Graph?

                                   

                                  Exactly what fields and tables are you specifying in your sort order?

                                  • 14. Re: Report layout with summaries
                                    Bauke
                                      

                                    hello Phil,

                                     

                                    It's working now! I simply read over a mistake of mine and more than once!

                                     

                                    Does it matter where I sort the portal setup?

                                     

                                    For now, just one little thing:

                                    When I leave the last field (filling in a new game), the cursor (focus) jumps to the first field again. Clicking on the layout then (outside fields) gives an error (seizoen should have a value).

                                     

                                    After filling in a game it shouldn't automaticly go to the first field again. How do I accomplish that?

                                     

                                    Thanks

                                     

                                    Bauke 

                                    1 2 Previous Next