1 2 Previous Next 17 Replies Latest reply on May 15, 2015 6:23 AM by matthew_odell

    How to generate reports from portal records

    mingram

      For example, in Meetings Starter Solution, there are Topics and Action Items in portals. How does one create a report that:

      1) lists out all the Subjects/Text from the Topics Portal, preferably ordered by Subject regardless of how many records; and

      2) reference any Action Items and Due Dates for matching subjects entered where these fields are not blank? Does the Portal for Action Items even pull from the Subjects or is this another random list??

        • 1. Re: How to generate reports from portal records
          matthew_odell

          Hi there,

           

          How are you planning on viewing this report? Are you looking to print it out or save it to PDF, or is this strictly for viewing on the screen?

          • 2. Re: How to generate reports from portal records
            mingram

            Both - view on screen and PDF, thanks.

            • 3. Re: How to generate reports from portal records
              matthew_odell

              Alright, there are a few concepts that will probably be helpful in this. The first is about tables and creating new layouts. Any table that is in your system can be the source data of your layout. In this case, you would use layout mode to create a new layout, and instead of having it based on the Meetings table, you would have it pointing to the Topics data (which in that starter solution is the "Data" table). I'd suggest choosing a layout formatted for a Printer, and is a Report. If you need some help walking through these screens, I suggest reading through this part of our build training: People Goal 4 - Intro: Add your interfaces - Reporting screen

               

              Now that this layout is off on it's own, the next thing you'll need to worry about is how to limit the found set on that reporting layout. By default, just going to that layout will show you all records, but you'll probably only want to see records from a specific Meeting. An easy way to do this is to build a script, and use the script step Go To Related Records, and point to the Data | Topics table occurrence, and tell it to go to the layout you created above. Then put a button on your Meeting Detail layout to call that script. It will go to that layout and change the found set to only show Topics from that meeting.

               

              Lastly, you make a mention a reference to any action item with matching subjects. This would involve create another relationship to only see Action Items with the same title for the same meeting. To do this you would need to create a calculation field in the Data table that is always equal to "Action Items". Then create a new table occurrence on your graph, relate it to Data, and have the relationship look like the image below. Then you could place fields from the "Data | related action items for Topic" table occurrence on your layout.


              I know that's probably a lot, so let me know if you have any questions.


              Screen Shot 2015-05-13 at 12.13.35 PM.png

              • 4. Re: How to generate reports from portal records
                mingram

                Thanks so much for your response.

                 

                Currently, the Meeting Starter Solution already has a Meeting Report with all the details for ONE meeting, where it lists all Topics and Action Items entered.

                 

                The report I need is not actually for one specific meeting but for all meetings. I need the ability to report on all topics discussed at all the meetings, and pool similar topics or sort by topic, to see which ones remain constant with multiple companies to help determine importance and what's top-of-mind for the majority of companies. I hope I am explaining this correctly.  Essentially I need a table or report that will contain this information (clearly showing which topic shows up most frequently):

                 

                Screen Shot 2015-05-13 at 3.02.42 PM.png

                 

                Action Items related to each Topic can be addressed afterwards.  I need to know if this Starter Solution can organize the topics that are recorded for each meeting in this way. The person inputting the notes will be the person conducting the meetings so each "topic" may not be worded in the same way, so I need Filemaker to sort them somehow and report for printing, so that similar topics can bubble to the top and the topics can be properly named for uniformity.

                 

                Thanks so much.

                • 5. Re: How to generate reports from portal records
                  matthew_odell

                  Hey,

                   

                  Well there is some good news, and some bad news. Good news is to just remove my information earlier about using Go To Related Records, and instead write a script to find the data that you want.

                   

                  Bad news is that two of the things you're looking for are difficult to get. First off, the type of report you show there is sometimes referred to as a "cross tab" report (or pivot report, because it pivots record data from going vertical to horizontal). FileMaker doesn't do Cross tabs well. You can do it, and there are tutorials in places like our FTS: Advanced that show how to create one, but it's a lot of work, and can be somewhat limiting. Instead you might want to think about creating this like a sub summary report with multiple sub summary parts. First by Company, then by Topic, which would look something like this:

                   

                  Screen Shot 2015-05-14 at 6.49.30 AM.png

                   

                  The second bad news is around this statement:

                   

                  The person inputting the notes will be the person conducting the meetings so each "topic" may not be worded in the same way

                   

                  Any report that groups things together like this needs the values to be exactly the same, and if they aren't, you will see multiple lines for what might be the same thing. For example if one person spells a topic "Alamo" and another person says "The Alamo", FileMaker will consider those two different things, and will show up as two lines on your report. You're best bet to help with this is to use Auto-complete, which I believe is turned on for those fields in Meetings already. That will help some of the time, but to get accurate reports, you'll probably still need some manual clean up.

                  • 6. Re: How to generate reports from portal records
                    mingram

                    After I sent my last message I realized I could mimic the Meeting List Report for this purpose, which uses the SubSummary part.  Good to know that what I am trying to do is difficult.  Would be great if there was a features list for each Quick Start Solution listing what it can do, what it's best used for and what areas (with 2-3 examples) will need customization.  I didn't want to input 50 meetings and then not be able to do what I need it to do.

                     

                    Thank you for your advice.  I will attempt the Topics report today

                     

                    One more question: If I want to link the companies (Contacts) from another database to the Meetings database, do I need to create/assign card numbers to each company? Should I post this under a new "discussion"?

                    • 7. Re: How to generate reports from portal records
                      matthew_odell

                      What do you mean by "Card Numbers"? Also, are you referring to using the Contacts starter solution as the other database?

                      • 8. Re: How to generate reports from portal records
                        mingram

                        Sorry, Card IDs.  We have an existing Contacts database where each person has a Card ID.  I wanted to import our contacts database into the Contacts Starter Solution but am afraid of limitations I might encounter.  Our current database has been patched up and improved over all the versions of filemaker that I'm afraid it doesn't meet the standards of how a contacts database should be set up. I believe a proper contacts database should be in the hierarchy of: Org, Dept, Person.  Our database is by Person only which is why you are getting strange questions...lol.

                        • 9. Re: How to generate reports from portal records
                          mingram

                          I am missing some key component and hope you can help.  I still am unable to collect all the "subjects" under the Topics portal.  I believe the key is the subSummary and this sort key, but I have no experience with it.  Here is what my report looks like:

                          Screen Shot 2015-05-14 at 9.28.06 AM.png

                          But it is only listing the first Subject - Roundtables (there's 3 subjects for each company so far), in the portal.  When I check the fields in the layout, there is a Subsummary part for a Sort Key but nothing happens when I tried to create a sort key for the subject and put into another subsummary part...help? Image below is the calculation for the working subsummary, Title (which is really the Company)

                           

                          Screen Shot 2015-05-14 at 9.25.07 AM.png

                          • 10. Re: How to generate reports from portal records
                            mingram

                            The Starter Solution (Meetings) is flawed.  Field "subject" is shared between Topics Portal and Action Items Portal.  I managed to prepare a report of all topic subjects but since this field also appears in the other portal, these items show up in my report.

                            • 11. Re: How to generate reports from portal records
                              matthew_odell

                              Well the Contacts starter solution is not relational like you would potentially want to setup your solution, but you might not need it to be relational (and still just with people) if you can use subsummaries to group people together. In terms of assigning numbers, you would want to create a Card ID field, then in field options, give it an Auto-enter Serial Number. Look that up in help if you have never used a serial number before.

                              • 12. Re: How to generate reports from portal records
                                matthew_odell

                                This is fixed with a find. Just find records with the Type field equal to "Topics" and you're golden.

                                • 13. Re: How to generate reports from portal records
                                  mingram

                                  Yes, this is how we manage in our own contacts database - with sub summaries.. I was just wondering if an ideal way to start is is that hierarchy?

                                  • 14. Re: How to generate reports from portal records
                                    mingram

                                    Tried that, thank you

                                    1 2 Previous Next