1 2 Previous Next 17 Replies Latest reply on Jun 5, 2013 9:35 AM by philmodjunk

    Need help to report related cumulative figures

    DanielPinder

      Title

      Need help to report related cumulative figures

      Post

           Hi Everybody,

           I have made myself a little proof-of-concept fmp12 file to try to work out something I don't totally understand how to do. The db is based on a simple relationship between two tables ("Work List" and "Contributors") using an IDkey field that generates a serial number and I use a portal in the main data entry table ("Work List") to add multiple related records for each local record, which is called a "work". The purpose is to be able to enter contributors of a work (let's say, a piece of music) and to enter what percentage of the total time for each work is theirs. The db then calculates how much of the work's time is divided to each contributor for payment purposes. 

           The db so far works fine. What I need to learn is how to make the footer display a list of every contributor (dynamically, via a portal I should think) and to display a percentage representing their TOTAL CONTRIBUTION to all works (records). I need the db to somehow sum all time for each contributor, by finding each of their related records, and calculate a percentage of the overall time.

           In other words, if there are 3 works, 4 contributors and a total time of 9:36, I want the footer to show each person and their total percentage of contribution overall. 

           Here's how I'd like it to look in the footer:

           David: XX.XX%

           Judith: XX.XX%

           Hansel: XX.XX%

           Clancy: XX.XX%

            

           The db is available from this link: https://www.dropbox.com/s/kr61jytaswvlgmr/PercentageTimeWIP.fmp12

            

           Any help is greatly appreciated! Screenshots:

            

            

      Screen_Shot_2013-05-24_at_15.56.58_.png

        • 1. Re: Need help to report related cumulative figures
          DanielPinder

               Here's the relationships window:

                

          • 2. Re: Need help to report related cumulative figures
            philmodjunk

                 Your relationship shown lacks "crows feet". Either WorkList::IDKey uniquely identifies each record in Work List or Contributors::IDKey uniquely identifies each contributor. But both are not possible for the relationship shown as what is shown is a one to one relationship.

                 It looks to me like you need a join table since a given contributor can work on any number of "Works" and any given Work can have multiple contributors.

                 That would suggest relationships similar to this:

                 Works------<Contributor_Work>-------Contributors

                 Works::__pkWorkID = Contributor_Work::_fkWorkID
                 Contributors::__pkContributorID = Contributor_Work::_fkContributorID

                 The hours worked by any given contributor would be recorded (or computed from a fourth related table) in Contributor_Work.

            • 3. Re: Need help to report related cumulative figures
              DanielPinder

                   I did a bit of reading yesterday and made your suggested join table work. My problem now is that, by moving some of the criteria to the join table (percentages, for example) the portal I've created shows fields from Contributor_Work, but some of those fields, such as the contributor name, must come from the Contributors table.

                   This is a problem because when I tell the join table to automatically create records via relationship, (I specify this in the relationship between Works and Contributor_Work), I can create records based on non-essential criteria such as percentages, but I cannot choose a Name from from the Contributors table because that is not the relationship I've enabled to create records in. But that's backwards.

                   There is this paradox: I want to create records, through a portal in my parent table (Works), that live in the join table, but the main criterion I want to use to create these records is in the wrong table. If I tell the Contributors table to automatically create records, I will get endless copies of my contributors, which is wrong—they should have one record associated with them.

                   How do I get around this problem? At the moment, I am forced to create a layout that looks at fields in the join table, create records in there, then watch them show up in the parent table's portal. This is inelegant, to say the least. 

              • 4. Re: Need help to report related cumulative figures
                GuyStevens

                     Hey Daniel,

                     You can select contributors from a dropdown list in your join table.

                     I made you a little example file:

                https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Daniel%20Pinder/JoinTable.fmp12

                     Note that in this file I'm using a trick of two fields on top of each other to select a Contributor. I also created a second layout that uses a merge field to show the name. It's a slightly different technique.

                     It is however so that you cannot create contributors from the Works layout using this method. Because the contributors table is one step to far away. Your Works table is related to your Join table. You can create records there. But from there you can't create new contributors.

                     You can do this however if you use a sneaky trick. Namely, if you relate a second Table Occurrence (I don't know if you have learned about Table Occurrences yet or not) of your contributors table to your Works table.

                     For instance by creating a "Temporary Contributor" field in your works table where you can either select an existing or create a new contributor. And then you use a button that runs a script that puts that temp info in your join table.

                     An example of this can be seen here:

                https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Daniel%20Pinder/JoinTable2.fmp12

                      

                • 5. Re: Need help to report related cumulative figures
                  philmodjunk

                       Here's a demo file on Many to Many Relationships that you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                       It demo several different approaches and tricks for working with a many to many relationship.

                       And here's a file that demonstrates a an approach for selecting records by name that allows you to use auto-complete and yet still link records by ID number:

                       FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                       Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                        

                  • 6. Re: Need help to report related cumulative figures
                    DanielPinder

                         Thanks to you both. I'll respond ASAP.

                    • 7. Re: Need help to report related cumulative figures
                      DanielPinder

                           DaSaint,

                           Thanks for that. I like being able to select the contributors from a list and creating their records in the join table via the main "work" table, which is pretty much what I was trying to acheive. 

                           Also, it's not a problem to have to create contributors in their own table, but you nailed the data entry that I need.

                            

                           Appreciated!

                      • 8. Re: Need help to report related cumulative figures
                        DanielPinder

                             Phil—those are fantastic. Will pour over them this evening.

                        • 9. Re: Need help to report related cumulative figures
                          DanielPinder

                               Okay, I'm back and have implemented everything and it is working brilliantly. Thanks to both of you for your help and handy files. I have one question, as I've run into a reporting problem: I ended up making two of these many-to-many relationships (using a join table, of course) because I need to make a print layout that shows, in my case:

                               1. a subsummary page that shows each work, then each contributor underneath — this is fine, no problems.

                               2. I also need to show a second set of related contributors (this time "Publishers"). It is set up exactly the same way as "contributors"

                               I can display related records in my subsummary if the layout is set to show records from "Contributor_Work". The problem, however, is that I cannot display related records from "Publisher_Work" (my new join table for showing publisher info) at the same time. It seems I can only show related records correctly from one table at a time, and I understand why.

                               Any ideas how to combat this? Perhaps there's a work-around.

                          • 10. Re: Need help to report related cumulative figures
                            philmodjunk

                                 It would take a bit of creativity, but both Contributor_Work and Publisher_Work could be two table occurrences with the same data source table. This would require an additional field used to distinguish between the two and you may want to use filtered portals or more complex relationships for portals to this join table and to automate data entry into this added field, but such could be made to work and then you could have both publishers and Contributors as part of the same summary report.

                                 The alternative is to use a portal to one of the two join tables--which can work, but also has some limitations that can make it hard to get the report to function the way you need it to.

                            • 11. Re: Need help to report related cumulative figures
                              DanielPinder

                                   Good to know it's possible. I could, if need be, put contributors and publishers in the same table and filter the results onto the page, perhaps? It's fine if they share records in the same table, but they need to be displayed in two different positions on the page and if filtering portals helps here, then maybe that's a way to go. I shall investigate. 

                              • 12. Re: Need help to report related cumulative figures
                                philmodjunk

                                     Using sorting, you can group the two types of records and with a sub summary layout part, you can set up sub headings for each such group.

                                • 13. Re: Need help to report related cumulative figures
                                  DanielPinder

                                       You mean that if the field for the Contributor name and Publisher name are on different subsummary parts, all related records for each will display?

                                  • 14. Re: Need help to report related cumulative figures
                                    philmodjunk

                                         Nope.

                                         You would have a field that identifies to which group each record belongs. Say it's a text field with either the text "Contributor" or "Publisher".

                                         If you added a sub summary part "when sorted by" this field, your records would sort into a group of contributor records and a group of publisher records. The sub summary part then serves as a "sub header" where you can put this one field if you wish so that your report looks like this:

                                         Contributor
                                            John Smith
                                            Mary Alexander

                                         Publisher
                                            Random House
                                            Etc....

                                    1 2 Previous Next